On Mon, 30 Aug 2010, Tom Lane wrote:
Gerhard Wiesinger <lists@xxxxxxxxxxxxx> 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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
