Message-ID: <42825DBB.7000703@smet.org>
Date: Wed, 11 May 2005 21:32:11 +0200
From: Guillaume Smet <guillaume_ml@smet.org>
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com, pgsql-performance@postgresql.org
Subject: Re: Bad plan after vacuum analyze
References: <42823F94.8050009@smet.org> <200505111110.15983.josh@agliodbs.com>
	<13964.1115837926@sss.pgh.pa.us>
In-Reply-To: <13964.1115837926@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------010907060909070006020000"

This is a multi-part message in MIME format.
--------------010907060909070006020000
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------010907060909070006020000
Content-Type: text/plain;
 name="stats.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="stats.txt"

 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)


--------------010907060909070006020000--