Обсуждение: database not using indexes
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 quandoli 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 eventualiallegati, 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 <silvio.brandani@tech.sdb.it> wrote: > 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
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 Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > 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. -- >
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 Il 09/11/2011 16.58, Ruslan A. Bondar ha scritto: > 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 > Silvio Brandani<silvio.brandani@tech.sdb.it> wrote: > >> 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 quandoli 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 eventualiallegati, 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. --
On 09/11/11 23:58, Ruslan A. Bondar wrote: > > 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
On 10/11/11 01:06, Silvio Brandani wrote: > 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 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
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 quandoli 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 eventualiallegati, 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. --
2011/11/18 Silvio Brandani <silvio.brandani@tech.sdb.it>
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
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/
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
Il 18/11/2011 15.19, Szymon Guz ha scritto:
I create a copy of the table with create tabase as select ... and then indexed, the new table use the indexes ...
thanks
Il 18/11/2011 15.19, Szymon Guz ha scritto:
2011/11/18 Silvio Brandani <silvio.brandani@tech.sdb.it>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 BrandaniHi Silvio,how many rows do you have in the table?Usually PostgreSQL doesn't want to use index when the table is small.regardsSzymon--
http://simononsoftware.com/
-- Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119
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.
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 <silvio.brandani@tech.sdb.it> writes: > 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 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
Il 18/11/2011 15.51, Tom Lane ha scritto: > Silvio Brandani<silvio.brandani@tech.sdb.it> writes: >> 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 > 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 quandoli 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 eventualiallegati, 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 <silvio.brandani@tech.sdb.it> writes: > Il 18/11/2011 15.51, Tom Lane ha scritto: >> 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