Re: database not using indexes, yet

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 <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