X-Original-To: pgsql-performance@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 629E3475F53
	for <pgsql-performance@postgresql.org>;
	Fri,  4 Apr 2003 14:03:14 -0500 (EST)
Received: from email02.aon.at (WARSL401PIP7.highway.telekom.at [195.3.96.94])
	by postgresql.org (Postfix) with SMTP id 443BF475C3D
	for <pgsql-performance@postgresql.org>;
	Fri,  4 Apr 2003 14:03:13 -0500 (EST)
Received: (qmail 384036 invoked from network); 4 Apr 2003 19:03:11 -0000
Received: from m154p001.dipool.highway.telekom.at (HELO cantor) ([62.46.9.33])
	(envelope-sender <mkoi-pg@aon.at>)
	by qmail2rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <josh@agliodbs.com>; 4 Apr 2003 19:03:11 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: Josh Berkus <josh@agliodbs.com>
Cc: "Tomasz Myrta" <jasiek@klaster.net>,
	Robert Treat <xzilla@users.sourceforge.net>,
	pgsql-sql@postgresql.org, 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>
References: <1049403724.13799.5473.camel@camel>
	<20030404080209.M3259@klaster.net>
	<200304040816.01369.josh@agliodbs.com>
In-Reply-To: <200304040816.01369.josh@agliodbs.com>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Spam-Status: No, hits=-27.9 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,RCVD_IN_DSBL,
	REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_FORTE version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/62
X-Sequence-Number: 1568

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