[ADMIN] database not using indexes

[Silvio Brandani]

Our database seems not using index anymore, please help with, is a
production database.

is there a script to check missing index on foreign key ??

thanks a lot

---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

[Kevin Grittner]

Post by Silvio Brandani
Our database seems not using index anymore, please help with,
is a production database.
is there a script to check missing index on foreign key ??

You haven't provided enough information to allow anyone to help.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-Kevin

[Ruslan A. Bondar]

Why have you decided it isn't using indexes?
If index exists - postgres will use it.
To write a script for this I need at least database version.

On Wed, 09 Nov 2011 16:22:20 +0100

Post by Silvio Brandani
Our database seems not using index anymore, please help with, is a
production database.
is there a script to check missing index on foreign key ??
thanks a lot
---
Utilizziamo i dati personali che la riguardano esclusivamente per
nostre finalità amministrative e contabili, anche quando li
comunichiamo a terzi. Informazioni dettagliate, anche in ordine al
Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla
pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto
questo messaggio per errore Vi preghiamo di ritornarlo al mittente
eliminandolo assieme agli eventuali allegati, ai sensi art. 616
codice penale
http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda
non si assume alcuna responsabilità giuridica qualora pervengano da
questo indirizzo messaggi estranei all'attività lavorativa o contrari
a norme. --

[Silvio Brandani]

Ok,

the problem was on a big table on query like this:

select outmessage0_.out_msg_id as out1_0_
from edi.out_messages outmessage0_, edi.transaction_set_partners
transactio1_
where outmessage0_.transaction_set_partner=transactio1_.trn_set_prtn_id
and outmessage0_.status_id='TOSND'
and transactio1_.legacy_sender_id='ALL'
and transactio1_.legacy_receiver_id='00004542'
and outmessage0_.transaction_set_id='INTERNAL_USE'
order by outmessage0_.out_msg_id


the existing indexes on status_id
CREATE INDEX out_msg_status_idex
ON edi.out_messages
USING btree
(status_id);

and transaction_set_partners

CREATE INDEX edi_out_messages_trn_set_prtn_id_fk_idx
ON edi.out_messages
USING btree
(transaction_set_partner);

where not used anyore.

I created the following one:

CREATE INDEX out_msg_status_trn_set_prtn_idx
ON edi.out_messages
USING btree
(status_id,transaction_set_partner);

and still the explain show a seq scan

then I inverted the fields and now it works:

CREATE INDEX out_msg_status_trn_set_prtn_idx2
ON edi.out_messages
USING btree
(transaction_set_partner,status_id);



I wonder why not use anymore the existing indexes.

regards

Post by Ruslan A. Bondar
Why have you decided it isn't using indexes?
If index exists - postgres will use it.
To write a script for this I need at least database version.
On Wed, 09 Nov 2011 16:22:20 +0100

Post by Silvio Brandani
Our database seems not using index anymore, please help with, is a
production database.
is there a script to check missing index on foreign key ??
thanks a lot
---

---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

[Craig Ringer]

Post by Silvio Brandani
Ok,
select outmessage0_.out_msg_id as out1_0_
from edi.out_messages outmessage0_, edi.transaction_set_partners
transactio1_
where outmessage0_.transaction_set_partner=transactio1_.trn_set_prtn_id
and outmessage0_.status_id='TOSND'
and transactio1_.legacy_sender_id='ALL'
and transactio1_.legacy_receiver_id='00004542'
and outmessage0_.transaction_set_id='INTERNAL_USE'
order by outmessage0_.out_msg_id

Please read:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

and as linked to there, read:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

then try again including all the information you are asked for. In
particular, you have not included EXPLAIN ANALYZE output or any
information about your PostgreSQL version. Failure to provide that
information makes it difficult or impossible to answer your question,
wasting your time and everyone else's.

We don't link to that document just for fun. It's information that is
important to answer questions properly.

--
Craig Ringer

[Craig Ringer]

Post by Ruslan A. Bondar
Why have you decided it isn't using indexes?
If index exists - postgres will use it.

Actually that's not necessarily the case.

PostgreSQL will only use an index if (a) the index can be used for that
kind of query and (b) using the index will be faster than doing a
sequential scan.

If a query requires all the data in a table, PostgreSQL is quite likely
to do a sequential scan of the table, because it'll need to read every
block anyway. Reading just the table (without reading the index) in
order is much faster than reading the index then doing semi-random reads
of the table.

Additionally, not all indexes can be used for all operations. For
example, a LIKE query with a prefix wildcard eg "%FRED" cannot use a
btree index, so any btree index on the searched field will be ignored.

--
Craig Ringer

[Silvio Brandani]

On postgres 8.3.11 on linux centos 5 we have a table not too big with
primary key index on

Indexes:
"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)

the query not use index:

aidadb=# explain analyze select aida_reference_id from
aida.aida_references where aida_reference_id = '3145';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4)
(actual time=0.173..1457.643 rows=1 loops=1)
Filter: (aida_reference_id = 3145)
Total runtime: 1457.696 ms


already executed the vacuum ,reindex.

Please help
--
Silvio Brandani


---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

[Szymon Guz]

Post by Silvio Brandani
On postgres 8.3.11 on linux centos 5 we have a table not too big with
primary key index on
"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)
aidadb=# explain analyze select aida_reference_id from
aida.aida_references where aida_reference_id = '3145';
QUERY PLAN
------------------------------**------------------------------**
------------------------------**----------------------
Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4) (actual
time=0.173..1457.643 rows=1 loops=1)
Filter: (aida_reference_id = 3145)
Total runtime: 1457.696 ms
already executed the vacuum ,reindex.
Please help
--
Silvio Brandani

Hi Silvio,
how many rows do you have in the table?
Usually PostgreSQL doesn't want to use index when the table is small.


regards
Szymon

[Silvio Brandani]

table is 959818 records,

I create a copy of the table with create tabase as select ... and
then indexed, the new table use the indexes ...

thanks

Post by Silvio Brandani
On postgres 8.3.11 on linux centos 5 we have a table not too big
with primary key index on
"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)
aidadb=# explain analyze select aida_reference_id from
aida.aida_references where aida_reference_id = '3145';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4)
(actual time=0.173..1457.643 rows=1 loops=1)
Filter: (aida_reference_id = 3145)
Total runtime: 1457.696 ms
already executed the vacuum ,reindex.
Please help
--
Silvio Brandani
Hi Silvio,
how many rows do you have in the table?
Usually PostgreSQL doesn't want to use index when the table is small.
regards
Szymon
--
*http://simononsoftware.com/*

--
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: [phone]
Fax: [phone]


---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

[Tom Lane]

Post by Silvio Brandani
On postgres 8.3.11 on linux centos 5 we have a table not too big with
primary key index on
"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)
aidadb=# explain analyze select aida_reference_id from
aida.aida_references where aida_reference_id = '3145';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4)
(actual time=0.173..1457.643 rows=1 loops=1)
Filter: (aida_reference_id = 3145)
Total runtime: 1457.696 ms

There's nothing here to suggest that this query shouldn't use an index,
so the problem is in something you didn't show us. Maybe you have
enable_indexscan turned off, or maybe that index isn't really on that
table, or something else.

regards, tom lane

[Silvio Brandani]

Post by Tom Lane

Post by Silvio Brandani
On postgres 8.3.11 on linux centos 5 we have a table not too big with
primary key index on
"aida_references_pkey" PRIMARY KEY, btree (aida_reference_id)
aidadb=# explain analyze select aida_reference_id from
aida.aida_references where aida_reference_id = '3145';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on aida_references (cost=0.00..51489.15 rows=1 width=4)
(actual time=0.173..1457.643 rows=1 loops=1)
Filter: (aida_reference_id = 3145)
Total runtime: 1457.696 ms

There's nothing here to suggest that this query shouldn't use an index,
so the problem is in something you didn't show us. Maybe you have
enable_indexscan turned off, or maybe that index isn't really on that
table, or something else.
regards, tom lane

this is not the case, to be sure I have recreated the index and still
not work. moreover the copy of the table with the same ttpe of index is
using it.

I have vacuum full the table and still not work, how can I rebuild this
table ? this is a production database..

thanks
--
Silvio Brandani


---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

[Tom Lane]

Post by Silvio Brandani

Post by Tom Lane
There's nothing here to suggest that this query shouldn't use an index,
so the problem is in something you didn't show us. Maybe you have
enable_indexscan turned off, or maybe that index isn't really on that
table, or something else.

this is not the case, to be sure I have recreated the index and still
not work. moreover the copy of the table with the same ttpe of index is
using it.

Well, the rest of us can't see what you're doing, but I think this is
highly likely to be user error. Have you got more than one table named
aida_references?

regards, tom lane