From: Alessandro Baretta <a.baretta@barettadeit.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: 500x speed-down: Wrong statistics!
Date: Wed, 11 Jan 2006 10:42:45 +0100
Message-ID: <43C4D315.8010505@barettadeit.com> (raw)
In-Reply-To: <29702.1136906579@sss.pgh.pa.us>
References: <43C26A5F.7040702@barettadeit.com>
<43C27AFA.7060604@beccati.com>
<43C28DEA.60801@barettadeit.com>
<6719.1136832065@sss.pgh.pa.us>
<43C2B06B.4050506@barettadeit.com>
<6914.1136833010@sss.pgh.pa.us>
<43C3665F.5020106@barettadeit.com>
<29702.1136906579@sss.pgh.pa.us>
Tom Lane wrote:
> Alessandro Baretta <a.baretta@barettadeit.com> writes:
>
>>I have no clue as to how or why the statistics were wrong
>>yesterday--as I vacuum-analyzed continuously out of lack of any better
>>idea--and I was stupid enough to re-timestamp everything before
>>selecting from pg_stats.
>
>
> Too bad. I would be interested to find out how, if the stats were
> up-to-date, the thing was still getting the row estimate so wrong.
> If you manage to get the database back into its prior state please
> do send along the pg_stats info.
I have some more information on this issue, which clears PostgreSQL's planner of
all suspects. I am observing severe corruption of the bookkeeping fields managed
by the xdbs rule/trigger "complex". I am unable to pinpoint the cause, right
now, but the effect is that after running a few hours' test on the end-user
application (which never interacts directly with xdbs_* fields, and thus cannot
possibly mangle them) most tuples (the older ones, apparently) get thei
timestamps set to NULL. Before vacuum-analyzing the table, yesterday's
statistics were in effect, and the planner used the appropriate indexes. Now,
after vacuum-analyzing the table, the pg_stats row for the xdbs_modified field
no longer exists (!), and the planner has reverted to the Nested Loop Seq Scan
join strategy. Hence, all the vacuum-analyzing I was doing when complaining
against the planner was actually collecting completely screwed statistics, and
this is why the ALTER TABLE ... SET STATISTICS 1000 did not help at all!
Ok. I plead guilty and ask for the clemency of the court. I'll pay my debt with
society with a long term of pl/pgsql code debugging...
Alex
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: a.baretta@barettadeit.com, tgl@sss.pgh.pa.us
Subject: Re: 500x speed-down: Wrong statistics!
In-Reply-To: <43C4D315.8010505@barettadeit.com>
* 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