pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase not using indexes
6+ messages / 4 participants
[nested] [flat]
* database not using indexes
@ 2011-11-09 15:22 Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-09 15:48 ` Re: database not using indexes Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-11-09 15:58 ` Re: database not using indexes Ruslan A. Bondar <fsat@list.ru>
0 siblings, 2 replies; 6+ messages in thread
From: Silvio Brandani @ 2011-11-09 15:22 UTC (permalink / raw)
To: pgsql-admin
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.
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: database not using indexes
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2011-11-09 15:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2011-11-09 15:48 UTC (permalink / raw)
To: pgsql-admin; Silvio Brandani <silvio.brandani@tech.sdb.it>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: database not using indexes
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2011-11-09 15:58 ` Ruslan A. Bondar <fsat@list.ru>
2011-11-09 17:06 ` Re: database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-10 02:40 ` Re: database not using indexes Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 2 replies; 6+ messages in thread
From: Ruslan A. Bondar @ 2011-11-09 15:58 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: pgsql-admin
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. --
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: database not using indexes
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-09 15:58 ` Re: database not using indexes Ruslan A. Bondar <fsat@list.ru>
@ 2011-11-09 17:06 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-10 02:43 ` Re: database not using indexes Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 1 reply; 6+ messages in thread
From: Silvio Brandani @ 2011-11-09 17:06 UTC (permalink / raw)
To: Ruslan A. Bondar <fsat@list.ru>; +Cc: pgsql-admin
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 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.
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: database not using indexes
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-09 15:58 ` Re: database not using indexes Ruslan A. Bondar <fsat@list.ru>
2011-11-09 17:06 ` Re: database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
@ 2011-11-10 02:43 ` Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2011-11-10 02:43 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: Ruslan A. Bondar <fsat@list.ru>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: database not using indexes
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-09 15:58 ` Re: database not using indexes Ruslan A. Bondar <fsat@list.ru>
@ 2011-11-10 02:40 ` Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2011-11-10 02:40 UTC (permalink / raw)
To: Ruslan A. Bondar <fsat@list.ru>; +Cc: Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-10 02:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-09 15:22 database not using indexes Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-09 15:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-11-09 15:58 ` Ruslan A. Bondar <fsat@list.ru>
2011-11-09 17:06 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-11-10 02:43 ` Craig Ringer <ringerc@ringerc.id.au>
2011-11-10 02:40 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox