Re: Weird performance drop after VACUUM

От: Michael Fuhr
Тема: Re: Weird performance drop after VACUUM
Дата: ,
Msg-id: 20050826232641.GA19583@winnie.fuhr.org
(см: обсуждение, исходный текст)
Ответ на: Re: Weird performance drop after VACUUM  (asif ali)
Ответы: Re: Weird performance drop after VACUUM  (asif ali)
Список: pgsql-performance

Скрыть дерево обсуждения

Weird performance drop after VACUUM  (Ümit Öztosun, )
 Re: Weird performance drop after VACUUM  (asif ali, )
  Re: Weird performance drop after VACUUM  (Philip Hallstrom, )
  Re: Weird performance drop after VACUUM  (Michael Fuhr, )
   Re: Weird performance drop after VACUUM  (asif ali, )
    Re: Weird performance drop after VACUUM  (Michael Fuhr, )
     Re: Weird performance drop after VACUUM  (asif ali, )
      Re: Weird performance drop after VACUUM  (Michael Fuhr, )
       Re: Weird performance drop after VACUUM  (asif ali, )
 Re: Weird performance drop after VACUUM  (Tom Lane, )
  Re: Weird performance drop after VACUUM  (Umit Oztosun, )
  Re: Weird performance drop after VACUUM  ("Steinar H. Gunderson", )
   Re: Weird performance drop after VACUUM  (Tom Lane, )
 Re: Weird performance drop after VACUUM  ("Steinar H. Gunderson", )
  Re: Weird performance drop after VACUUM  (Tom Lane, )

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


В списке pgsql-performance по дате сообщения:

От: "tobbe"
Дата:
Сообщение: Re: Performance for relative large DB
От: Bruno Wolff III
Дата:
Сообщение: Re: Need indexes on empty tables for good performance ?