Re: Major performance problem after upgrade from 8.3 to 8.4

On Mon, 30 Aug 2010, Tom Lane wrote:

> Gerhard Wiesinger <lists(at)wiesinger(dot)com> writes:
>> I know the drawbacks of an EAV design but I don't want to discuss that. I
>> want to discuss the major performance decrease of PostgreSQL 8.3
>> (performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
>
>> Any further ideas how I can track this down?
>> Can someone explain the difference in query plan from an optimizer point
>> of view?
>
> Since you haven't shown us the 8.3 plan, it's kind of hard to speculate ;-)
>
> One thing that jumped out at me was that 8.4 appears to be expecting
> multiple matches in each of the left-joined tables, which is why the
> total rowcount estimate balloons so fast. I rather imagine that you are
> expecting at most one match in reality, else the query isn't going to
> behave nicely. Is this correct? Are you *sure* you analyzed all these
> tables? And if that is how the data looks, where is the actual
> performance problem? A bad rowcount estimate isn't in itself going
> to kill you.
>
> FWIW, in a similar albeit toy example, I don't see any difference
> between the 8.3 and 8.4 plans or cost estimates.

Yes, I'm expecting only one match in reality and I thing PostgreSQL should
also know that from table definition and constraints. Long answer below.

Query doesn't "end" in PostgreSQL.

From the definition:
CREATE TABLE value_types (
valuetypeid bigint PRIMARY KEY,
description varchar(256) NOT NULL -- e.g. 'float', 'integer', 'boolean'
);

CREATE TABLE key_description (
keyid bigint PRIMARY KEY,
description varchar(256) NOT NULL UNIQUE,
fk_valuetypeid bigint NOT NULL,
unit varchar(256) NOT NULL, -- e.g. '°C'
FOREIGN KEY(fk_valuetypeid) REFERENCES value_types(valuetypeid) ON DELETE RESTRICT
);
-- ALTER TABLE key_description DROP CONSTRAINT c_key_description_description;
-- ALTER TABLE key_description ADD CONSTRAINT c_key_description_description UNIQUE(description);

CREATE TABLE log (
id bigserial PRIMARY KEY,
datetime timestamp with time zone NOT NULL,
tdate date NOT NULL,
ttime time with time zone NOT NULL
);

CREATE TABLE log_details (
fk_id bigint NOT NULL,
fk_keyid bigint NOT NULL,
value double precision NOT NULL,
FOREIGN KEY (fk_id) REFERENCES log(id) ON DELETE CASCADE,
FOREIGN KEY (fk_keyid) REFERENCES key_description(keyid) ON DELETE RESTRICT,
CONSTRAINT unique_key_and_id UNIQUE(fk_id, fk_keyid)
);

Therefore keyid is unique and eg d1.fk_keyid is unique.
With constraint from log_details and d1.fk_keyid is unique fk_id is
unique for a given d1.fk_keyid.

BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
with old version on different TCP port possible to compare query plans?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/
From pgsql-performance-owner(at)postgresql(dot)org Mon Aug 30 13:49:49 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
by mail.postgresql.org (Postfix) with ESMTP id 7D968632449
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Mon, 30 Aug 2010 13:49:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024)
with ESMTP id 07780-01
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Mon, 30 Aug 2010 16:49:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by mail.postgresql.org (Postfix) with ESMTP id 5AD2A63226D
for <pgsql-performance(at)postgresql(dot)org>; Mon, 30 Aug 2010 13:49:42 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o7UGnfAd012838;
Mon, 30 Aug 2010 12:49:41 -0400 (EDT)
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>,
Andreas Kretschmer <akretschmer(at)spamfence(dot)net>,
pgsql-performance(at)postgresql(dot)org
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
In-reply-to: <alpine(dot)LFD(dot)2(dot)01(dot)1008301838480(dot)4133(at)bbs(dot)intern>
References: <alpine(dot)LFD(dot)2(dot)01(dot)1008300802300(dot)4121(at)bbs(dot)intern> <20100830072230(dot)GA28373(at)tux> <AANLkTi=Te9ToTG-bqVy+fHQ0wM1YWv0nhBqqYiTk2ePg(at)mail(dot)gmail(dot)com> <alpine(dot)LFD(dot)2(dot)01(dot)1008300956320(dot)19598(at)bbs(dot)intern> <AANLkTik14X_MYoZA5CHNGC8M7Rc0zXrRhoGw6FrfUPXm(at)mail(dot)gmail(dot)com> <alpine(dot)LFD(dot)2(dot)01(dot)1008301808330(dot)31277(at)bbs(dot)intern> <11111(dot)1283185373(at)sss(dot)pgh(dot)pa(dot)us> <alpine(dot)LFD(dot)2(dot)01(dot)1008301838480(dot)4133(at)bbs(dot)intern>
Comments: In-reply-to Gerhard Wiesinger <lists(at)wiesinger(dot)com>
message dated "Mon, 30 Aug 2010 18:45:26 +0200"
Date: Mon, 30 Aug 2010 12:49:41 -0400
Message-ID: <12837(dot)1283186981(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
T_RP_MATCHES_RCVD=-0.01
X-Spam-Level:
X-Archive-Number: 201008/333
X-Sequence-Number: 40176

Gerhard Wiesinger <lists(at)wiesinger(dot)com> writes:
> BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
> with old version on different TCP port possible to compare query plans?

You'll need to reinstall the old executables. If you put the new
executables in the same directories, it's not going to be easy to
run both in parallel. If you didn't, then you just need to start
the old postmaster using a different port number.