Обсуждение: 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/

-- 
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201119

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