From: Manfred Koizar <mkoi-pg@aon.at>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tomasz Myrta <jasiek@klaster.net>
Cc: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] can i make this sql query more efficiant?
Date: Fri, 04 Apr 2003 21:03:08 +0200
Message-ID: <d0ir8vgn2sp6uh2h61nkm4tdasqqn4o18l@4ax.com> (raw)
In-Reply-To: <200304040816.01369.josh@agliodbs.com>
References: <1049403724.13799.5473.camel@camel>
<20030404080209.M3259@klaster.net>
<200304040816.01369.josh@agliodbs.com>
On Fri, 4 Apr 2003 08:16:01 -0800, Josh Berkus <josh@agliodbs.com>
wrote:
>That version is only more efficient for small data sets. I've generally
>found that case statements are slower than subselects for large data sets.
I'd be honestly interested in the circumstances where you made that
observation.
>YMMV.
Yes, it does :-) Out of curiosity I did a few tests with PG 7.2 on my
old notebook:
CREATE TABLE baz (event int, level int);
INSERT INTO baz SELECT (100*random()+0.5), (3*random()+0.5);
INSERT INTO baz SELECT (100*random()+0.5), (3*random()+0.5) FROM baz;
...
INSERT INTO baz SELECT (100*random()+0.5), (3*random()+0.5) FROM baz;
CREATE INDEX baz_event ON baz(event);
ANALYSE baz;
SELECT event,
SUM (CASE level WHEN 1 THEN 1 ELSE 0 END) AS ones,
SUM (CASE level WHEN 2 THEN 1 ELSE 0 END) AS twos,
SUM (CASE level WHEN 3 THEN 1 ELSE 0 END) AS threes
FROM baz GROUP BY event;
SELECT event,
(SELECT count(*) FROM baz a
WHERE level = 1 AND a.event=baz.event) AS ones,
(SELECT count(*) FROM baz a
WHERE level = 2 and a.event=baz.event) AS twos,
(SELECT count(*) FROM baz a
WHERE level = 3 and a.event=baz.event) AS threes
FROM baz GROUP BY event;
tuples case subselect
8K 718.48 msec 16199.88 msec
32K 6168.18 msec 74742.85 msec
128K 25072.34 msec 304585.61 msec
CLUSTER baz_event ON baz; ANALYSE baz;
This changes the subselect plan from seq scan to index scan.
128K 12116.07 msec 17530.85 msec
Add 128K more tuples, so that only the first half of the relation is
clustered.
256K 45663.35 msec 117748.23 msec
CLUSTER baz_event ON baz; ANALYSE baz;
256K 23691.81 msec 35138.26 msec
Maybe it is just the data distribution (100 events, 3 levels,
thousands of tuples) that makes CASE look faster than subselects ...
Servus
Manfred
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-sql@postgresql.org
Cc: mkoi-pg@aon.at, josh@agliodbs.com, jasiek@klaster.net, xzilla@users.sourceforge.net, pgsql-performance@postgresql.org
Subject: Re: [SQL] can i make this sql query more efficiant?
In-Reply-To: <d0ir8vgn2sp6uh2h61nkm4tdasqqn4o18l@4ax.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