Re: Weird performance drop after VACUUM - Mailing list pgsql-performance

Thanks Michael For your reply.

Here is performance on the database on which i did
VACUUM ANALYZE

explain analyze
select   keyword_id
    ,sum(daily_impressions) as daily_impressions
    ,sum(daily_actions)as daily_actions
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17'
    group by keyword_Id

"GroupAggregate  (cost=195623.66..206672.52 rows=20132
width=16) (actual time=8205.283..10139.369 rows=55291
loops=1)"
"  ->  Sort  (cost=195623.66..198360.71 rows=1094820
width=16) (actual time=8205.114..9029.501 rows=863883
loops=1)"
"        Sort Key: keyword_id"
"        ->  Seq Scan on keyword_conversion_table c
(cost=0.00..29990.83 rows=1094820 width=16) (actual
time=0.057..1422.319 rows=863883 loops=1)"
"              Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 14683.617 ms"


Now see if am changing the query and commenting one
column.

explain analyze
select   keyword_id
    ,sum(daily_impressions) as daily_impressions
--    ,sum(daily_actions)as daily_actions
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17'
    group by keyword_Id


"HashAggregate  (cost=27373.51..27373.52 rows=2
width=16) (actual time=3030.386..3127.073 rows=55717
loops=1)"
"  ->  Seq Scan on conversion_table c
(cost=0.00..27336.12 rows=4986 width=16) (actual
time=0.050..1357.164 rows=885493 loops=1)"
"        Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 3159.162 ms"


I noticed "GroupAggregate" changes to "HashAggregate"
and performance from 14 sec to 3 sec.


On the other hand I have another database which I did
not do "VACUUM ANALYZE"  working fine.


explain analyze
select   keyword_id
    ,sum(daily_impressions) as daily_impressions
    ,sum(daily_actions)as daily_actions
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17'
    group by keyword_Id


"HashAggregate  (cost=27373.51..27373.52 rows=2
width=16) (actual time=3024.289..3120.324 rows=55717
loops=1)"
"  ->  Seq Scan on conversion_table c
(cost=0.00..27336.12 rows=4986 width=16) (actual
time=0.047..1352.212 rows=885493 loops=1)"
"        Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 3152.437 ms"


I am new to postgres. Thanks in advance.


asif ali






--- Michael Fuhr <mike@fuhr.org> wrote:

> On Fri, Aug 26, 2005 at 03:52:24PM -0700, asif ali
> wrote:
> > I have the same issue. After doing "VACCUME
> ANALYZE"
> > performance of the query dropped.
>
> Your EXPLAIN output doesn't show the actual query
> times -- could
> you post the EXPLAIN ANALYZE output?  That'll also
> show how accurate
> the planner's row count estimates are.
>
> > 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.
>
> Notice the row count estimates: 4986 in the "before"
> query and
> 1094820 in the "after" query.  In the latter, the
> planner thinks
> it has to fetch so much of the table that a
> sequential scan would
> be faster than an index scan.  You can see whether
> that guess is
> correct by disabling enable_seqscan to force an
> index scan.  It
> might be useful to see the output of the following:
>
> SET enable_seqscan TO on;
> SET enable_indexscan TO off;
> EXPLAIN ANALYZE SELECT ...;
>
> SET enable_seqscan TO off;
> SET enable_indexscan TO on;
> EXPLAIN ANALYZE SELECT ...;
>
> You might also experiment with planner variables
> like effective_cache_size
> and random_page_cost to see how changing them
> affects the query
> plan.  However, be careful of tuning the system
> based on one query:
> make sure adjustments result in reasonable plans for
> many different
> queries.
>
> --
> Michael Fuhr
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>




____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs