<div style="background-color:"><div><p>try to analyze the table. That may help to use the index<br /><br
/></div><div></div>>From:Chris Miles <div></div>>To: pgsql-admin@postgresql.org <div></div>>CC: Chris Miles
<div></div>>Subject:[ADMIN] This table won't use INDEX until I DUMP/RESTORE it ? <div></div>>Date: Tue, 19 Aug
200317:46:16 +0100 <div></div>> <div></div>>I have a DB that appears to perform badly. A test of one table
<div></div>>withone 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,
restoreit 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>>wasdone so again just before this test. Data, schema and indexes <div></div>>should <div></div>>be
thesame 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>>thefact that a newly restored DB appears to work much better, but <div></div>>surely <div></div>>I
shouldn'thave 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>>TIP5: 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=">Get6 months for $9.95/month.</a>
