From: Guillaume Smet <guillaume_ml@smet.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan after vacuum analyze
Date: Wed, 11 May 2005 21:32:11 +0200
Message-ID: <42825DBB.7000703@smet.org> (raw)
In-Reply-To: <13964.1115837926@sss.pgh.pa.us>
References: <42823F94.8050009@smet.org>
<200505111110.15983.josh@agliodbs.com>
<13964.1115837926@sss.pgh.pa.us>
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
Attachments:stats.txt (text/plain, 1.1K) download
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume_ml@smet.org, tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: Bad plan after vacuum analyze
In-Reply-To: <42825DBB.7000703@smet.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox