Hello, We are using PostgreSQL for our business application. Recently, during testing of our application with large volumes of data, we faced a weird problem. Our query performance dropped *dramatically* after "VACUUM FULL ANALYZE" command. We have encountered a similar problem listed on mailing list archives, but the submitter solved his problem by rewriting his query, which is unfortunatelly very hard for us.
I am attaching two EXPLAIN ANALYZE outputs, first one is just before the VACUUM FULL ANALYZE command and the other is the one after. Also attached is the SQL query, which is simplified to clearify the problem. In the example query time increases from 1.8 second to > 4.0 secons. The difference for the complete query is much bigger, query time increases from 7.8 seconds to > 110 seconds. Any help is appreciated, we were unable to identify what causes the query planner to choose a different/poor performing plan. Notes: Our production platform is Ubuntu Linux Hoary on i386, PostgreSQL 8.0.3, compiled from sources. Same tests were carried on Windows XP Professional and PostgreSQL 8.0.1 with similar results. The queries use little IO, high CPU. The largest table involved in the sample query has about 10000 rows. Indexes are used intensively, some tables use > 4 indexes. Best regards, Umit Oztosun
SELECT * FROM ( SELECT COALESCE ( (SELECT COALESCE (sum(irskal.anamiktar), 0) * (SELECT birim.fiyat2 * (SELECT kur1 FROM sis_doviz_kuru kur WHERE birim._key_sis_doviz2 = kur._key_sis_doviz ORDER BY tarih desc LIMIT 1) FROM scf_stokkart_birimleri birim WHERE _key_scf_stokkart = stok._key AND anabirim = '1' ) FROM scf_irsaliye irs, scf_irsaliye_kalemi irskal WHERE irskal._key_kalemturu = stok._key AND irskal._key_scf_irsaliye = irs._key AND irs.karsifirma = 'KENDI' AND (irs.turu='MAI' OR irs.turu='KGI' OR irs.turu='PS' OR irs.turu='TS' OR irs.turu='KC' OR irs.turu='KCO') AND ( irs._key_sis_depo_dest = '$$$$0000003l$1$$' OR irs._key_sis_depo_dest = '$$$$00000048$1$$' OR irs._key_sis_depo_dest = '$$$$0000004b$1$$' OR irs._key_sis_depo_dest = '$$$$0000004d$1$$' ) AND ((irskal._key LIKE '0000%' OR irskal._key LIKE '0101%' OR irskal._key LIKE '$$%')) AND irs.tarih <= '2005-08-26' ), 0 ) as arti_fiili_irs_karsifirma, stok.* FROM scf_stokkart stok ) AS _SWT WHERE (_key LIKE '00%' OR _key LIKE '01%' OR _key LIKE '$$%') ORDER BY _key desc
Before VACUUM FULL ANALYZE - Short Query --------------------------------------- Sort (cost=9094.31..9094.40 rows=37 width=817) (actual time=1852.799..1877.738 rows=10000 loops=1) Sort Key: stok._key -> Seq Scan on scf_stokkart stok (cost=0.00..9093.34 rows=37 width=817) (actual time=8.670..1575.586 rows=10000 loops=1) Filter: (((_key)::text ~~ '00%'::text) OR ((_key)::text ~~ '01%'::text) OR ((_key)::text ~~ '$$%'::text)) SubPlan -> Aggregate (cost=237.29..237.29 rows=1 width=16) (actual time=0.136..0.138 rows=1 loops=10000) InitPlan -> Index Scan using scf_stokkart_birimleri_key_scf_stokkart_idx on scf_stokkart_birimleri birim (cost=0.00..209.59 rows=1 width=58) (actual time=0.088..0.093 rows=1 loops=10000) Index Cond: ((_key_scf_stokkart)::text = ($1)::text) Filter: (anabirim = '1'::bpchar) SubPlan -> Limit (cost=9.31..9.31 rows=1 width=17) (actual time=0.046..0.048 rows=1 loops=10000) -> Sort (cost=9.31..9.31 rows=2 width=17) (actual time=0.041..0.041 rows=1 loops=10000) Sort Key: tarih -> Index Scan using sis_doviz_kuru_key_sis_doviz_idx on sis_doviz_kuru kur (cost=0.00..9.30 rows=2 width=17) (actual time=0.018..0.029 rows=2 loops=10000) Index Cond: (($0)::text = (_key_sis_doviz)::text) -> Nested Loop (cost=0.00..27.69 rows=1 width=16) (actual time=0.033..0.033 rows=0 loops=10000) -> Index Scan using scf_irsaliye_kalemi_key_kalemturu_idx on scf_irsaliye_kalemi irskal (cost=0.00..21.75 rows=1 width=58) (actual time=0.017..0.020 rows=0 loops=10000) Index Cond: ((_key_kalemturu)::text = ($1)::text) Filter: (((_key)::text ~~ '0000%'::text) OR ((_key)::text ~~ '0101%'::text) OR ((_key)::text ~~ '$$%'::text)) -> Index Scan using scf_irsaliye_pkey on scf_irsaliye irs (cost=0.00..5.94 rows=1 width=42) (actual time=0.021..0.021 rows=0 loops=3000) Index Cond: (("outer"._key_scf_irsaliye)::text = (irs._key)::text) Filter: (((karsifirma)::text = 'KENDI'::text) AND (((turu)::text = 'MAI'::text) OR ((turu)::text = 'KGI'::text) OR ((turu)::text = 'PS'::text) OR ((turu)::text = 'TS'::text) OR ((turu)::text = 'KC'::text) OR ((turu)::text = 'KCO'::text)) AND (((_key_sis_depo_dest)::text = '$$$$0000003l$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$00000048$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$0000004b$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$0000004d$1$$'::text)) AND (tarih <= '2005-08-26'::date)) Total runtime: 1899.533 ms
After VACUUM FULL ANALYZE - Short Query --------------------------------------- Index Scan Backward using scf_stokkart_pkey on scf_stokkart stok (cost=0.00..392045.63 rows=9998 width=166) (actual time=0.661..4431.568 rows=10000 loops=1) Filter: (((_key)::text ~~ '00%'::text) OR ((_key)::text ~~ '01%'::text) OR ((_key)::text ~~ '$$%'::text)) SubPlan -> Aggregate (cost=39.16..39.16 rows=1 width=10) (actual time=0.416..0.418 rows=1 loops=10000) InitPlan -> Index Scan using scf_stokkart_birimleri_key_scf_stokkart_idx on scf_stokkart_birimleri birim (cost=0.00..5.25 rows=2 width=28) (actual time=0.101..0.105 rows=1 loops=10000) Index Cond: ((_key_scf_stokkart)::text = ($1)::text) Filter: (anabirim = '1'::bpchar) SubPlan -> Limit (cost=1.08..1.09 rows=1 width=15) (actual time=0.048..0.050 rows=1 loops=10000) -> Sort (cost=1.08..1.09 rows=2 width=15) (actual time=0.043..0.043 rows=1 loops=10000) Sort Key: tarih -> Seq Scan on sis_doviz_kuru kur (cost=0.00..1.07 rows=2 width=15) (actual time=0.009..0.026 rows=2 loops=10000) Filter: (($0)::text = (_key_sis_doviz)::text) -> Nested Loop (cost=0.00..33.90 rows=1 width=10) (actual time=0.295..0.295 rows=0 loops=10000) -> Seq Scan on scf_irsaliye irs (cost=0.00..30.00 rows=1 width=20) (actual time=0.290..0.290 rows=0 loops=10000) Filter: (((karsifirma)::text = 'KENDI'::text) AND (((turu)::text = 'MAI'::text) OR ((turu)::text = 'KGI'::text) OR ((turu)::text = 'PS'::text) OR ((turu)::text = 'TS'::text) OR ((turu)::text = 'KC'::text) OR ((turu)::text = 'KCO'::text)) AND (((_key_sis_depo_dest)::text = '$$$$0000003l$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$00000048$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$0000004b$1$$'::text) OR ((_key_sis_depo_dest)::text = '$$$$0000004d$1$$'::text)) AND (tarih <= '2005-08-26'::date)) -> Index Scan using scf_irsaliye_kalemi_key_scf_irsaliye_idx on scf_irsaliye_kalemi irskal (cost=0.00..3.89 rows=1 width=30) (never executed) Index Cond: ((irskal._key_scf_irsaliye)::text = ("outer"._key)::text) Filter: (((_key_kalemturu)::text = ($1)::text) AND (((_key)::text ~~ '0000%'::text) OR ((_key)::text ~~ '0101%'::text) OR ((_key)::text ~~ '$$%'::text))) Total runtime: 4456.895 ms
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly