try to analyze the table. That may help to use the index
>From: Chris Miles
>To: pgsql-admin@postgresql.org
>CC: Chris Miles
>Subject: [ADMIN] This table won't use INDEX until I DUMP/RESTORE it ?
>Date: Tue, 19 Aug 2003 17:46:16 +0100
>
>I have a DB that appears to perform badly. A test of one table
>with one of the typical queries gives me a query plan indicating
>a Seq Scan;
>
>DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where
>pcbsref='something' and (pccaref is null or pccaref='') and pcpar
>is null order by pcseqnbr ;
>NOTICE: QUERY PLAN:
>
>Sort (cost=38266.65..38266.65 rows=4 width=58)
> -> Seq Scan on catrecrel (cost=0.00..38266.61 rows=4 width=58)
>
>Ok, that's no good, but it _should_ be using an index instead, and
>if I dump this table, restore it onto a different (non-live) DB, and
>try again, I see that it does indeed plan to use the index:
>
>DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where
>pcbsref='something' and (pccaref is null or pccaref='') and pcpar
>is null order by pcseqnbr ;
>NOTICE: QUERY PLAN:
>
>Sort (cost=469.92..469.92 rows=1 width=58)
> -> Index Scan using ind_pcbsref on catrecrel (cost=0.00..469.91
>rows=1 width=58)
>
>Now, why is this? The first (live) DB is VACUUM ANALYSEd nightly,
>and
>was done so again just before this test. Data, schema and indexes
>should
>be the same in both (well it was dumped/restored directly from one
>to the
>other).
>
>The only fix I can think of is to dump and restore the whole DB,
>based on
>the fact that a newly restored DB appears to work much better, but
>surely
>I shouldn't have to do this? What else can i do to fix it?
>
>Cheers,
>CM
>
>--
>Chris Miles
>http://chrismiles.info/
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html