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