Re: Weird performance drop after VACUUM

Hi,
I have the same issue. After doing "VACCUME ANALYZE"
performance of the query dropped.

Here is the query
explain select * from conversion_table c where
c.conversion_date BETWEEN '2005-06-07' and
'2005-08-17'

Before "VACCUME ANALYZE"

"Index Scan using conversion_table_pk on
keyword_conversion_table c (cost=0.00..18599.25
rows=4986 width=95)"
" Index Cond: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"

After "VACCUME ANALYZE"

"Seq Scan on conversion_table c (cost=0.00..29990.83
rows=1094820 width=66)"
" Filter: ((conversion_date >= '2005-06-07'::date)
AND (conversion_date <= '2005-08-17'::date))"

I dont know why system is doing "Seq scan" now.

Thanks

asif ali

--- �mit �ztosun <umit(at)likyabilisim(dot)com> wrote:

> 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
> majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list
> cleanly
>

__________________________________
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail