Re: Bad plan after vacuum analyze

От: Guillaume Smet
Тема: Re: Bad plan after vacuum analyze
Дата: ,
Msg-id: 42825DBB.7000703@smet.org
(см: обсуждение, исходный текст)
Ответ на: Re: Bad plan after vacuum analyze  (Tom Lane)
Ответы: Re: Bad plan after vacuum analyze  (Tom Lane)
Список: pgsql-performance

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

Bad plan after vacuum analyze  (Guillaume Smet, )
 Re: Bad plan after vacuum analyze  (Josh Berkus, )
  Re: Bad plan after vacuum analyze  (Tom Lane, )
   Re: Bad plan after vacuum analyze  (Guillaume Smet, )
    Re: Bad plan after vacuum analyze  (Tom Lane, )
     Re: Bad plan after vacuum analyze  (Guillaume Smet, )
      Re: Bad plan after vacuum analyze  (Tom Lane, )
       Re: Bad plan after vacuum analyze  (Guillaume Smet, )
        Re: Bad plan after vacuum analyze  (Markus Bertheau, )
 Re: Bad plan after vacuum analyze  (Mischa Sandberg, )

Tom,

> So, the usual questions: have these two tables been ANALYZEd lately?

Yes, of course.
As I wrote in my previous mail, here is how I reproduce the problem:
- we load the dump in a new database (to be sure, there is no problem on
an index or something like that)
- query: it's fast (< 1ms)
- *VACUUM FULL ANALYZE;*
- query: it's really slow (130ms) and it's another plan
- set enable_seqscan=off;
- query: it's fast (< 1ms) : it uses the best plan

I reproduced it on two different servers exactly like that (7.4.5 and
7.4.7).

I first met the problem on a production database with a VACUUM ANALYZE
run every night (and we don't have too many inserts a day on this database).

> If so, can we see the pg_stats rows for the object_id and
> parent_application_id columns?

See attached file.

If you're interested in a dump of these tables, just tell me. There
aren't any confidential information in them.

Regards

--
Guillaume
 schemaname |  tablename  |  attname  | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs |
                        histogram_bounds                               | correlation  

------------+-------------+-----------+-----------+-----------+------------+------------------+-------------------+-----------------------------------------------------------------------------+-------------
 public     | acs_objects | object_id |         0 |         4 |         -1 |                  |                   |
{1032,34143,112295,120811,285004,420038,449980,453451,457684,609292,710005}|    0.488069 
(1 ligne)

 schemaname |  tablename   |        attname        | null_frac | avg_width | n_distinct | most_common_vals |
most_common_freqs| histogram_bounds | correlation  

------------+--------------+-----------------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
 public     | applications | parent_application_id |  0.928571 |         4 |          1 | {1031}           |
{0.0714286}      |                  |           1 
(1 ligne)



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

От: Enrico Weigelt
Дата:
Сообщение: Re: BLOB's bypassing the OS Filesystem for better Image loading speed?
От: Bruce Momjian
Дата:
Сообщение: Re: Intel SRCS16 SATA raid?