Re: [ADMIN] database not using indexes, yet

2011-11-18 Thread Tom Lane
Silvio Brandani 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

Re: [ADMIN] database not using indexes, yet

2011-11-18 Thread Silvio Brandani
Il 18/11/2011 15.51, Tom Lane ha scritto: Silvio Brandani 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 a

Re: [ADMIN] database not using indexes, yet

2011-11-18 Thread Tom Lane
Silvio Brandani 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.

Re: [ADMIN] database not using indexes, yet

2011-11-18 Thread 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 Il 18/11/2011 15.19, Szymon Guz ha scritto: 2011/11/18 Silvio Brandani > On postgres 8.3.11 on linu

Re: [ADMIN] database not using indexes, yet

2011-11-18 Thread Szymon Guz
2011/11/18 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 > ai

Re: [ADMIN] database not using indexes

2011-11-09 Thread 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

Re: [ADMIN] database not using indexes

2011-11-09 Thread Craig Ringer
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 fa

Re: [ADMIN] database not using indexes

2011-11-09 Thread 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 transac

Re: [ADMIN] database not using indexes

2011-11-09 Thread 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 Silvio Brandani wrote: > Our database seems not using index anymore, please help with, is a > production database

Re: [ADMIN] database not using indexes

2011-11-09 Thread Kevin Grittner
Silvio Brandani 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