X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3B8A29DC802 for ; Wed, 11 Jan 2006 05:42:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31851-07 for ; Wed, 11 Jan 2006 05:42:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.barettadeit.com (unknown [213.255.109.130]) by postgresql.org (Postfix) with ESMTP id 430BA9DC813 for ; Wed, 11 Jan 2006 05:42:33 -0400 (AST) Received: from [10.1.0.20] (unknown [10.1.0.20]) by mail.barettadeit.com (Postfix) with ESMTP id 40F957D258E; Wed, 11 Jan 2006 10:46:30 +0100 (CET) Message-ID: <43C4D315.8010505@barettadeit.com> Date: Wed, 11 Jan 2006 10:42:45 +0100 From: Alessandro Baretta User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: 500x speed-down: Wrong statistics! 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> In-Reply-To: <29702.1136906579@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/107 X-Sequence-Number: 16585 Tom Lane wrote: > Alessandro Baretta 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