This table won't use INDEX until I DUMP/RESTORE it ?
От | Chris Miles |
---|---|
Тема | This table won't use INDEX until I DUMP/RESTORE it ? |
Дата | |
Msg-id | 3F425458.2020802@psychofx.com обсуждение исходный текст |
Ответы |
Re: This table won't use INDEX until I DUMP/RESTORE it ?
|
Список | pgsql-admin |
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 pcparis 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 pcparis 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/
В списке pgsql-admin по дате отправления: