pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan i make this sql query more efficiant?
8+ messages / 5 participants
[nested] [flat]
* can i make this sql query more efficiant?
@ 2003-04-03 21:02 Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 3 replies; 8+ messages in thread
From: Robert Treat @ 2003-04-03 21:02 UTC (permalink / raw)
To: pgsql-sql
create table baz (event text, level int);
insert into baz values ('x',1);
insert into baz values ('x',2);
insert into baz values ('x',3);
insert into baz values ('y',2);
insert into baz values ('y',3);
insert into baz values ('y',3);
select * from baz;
event | level
-------+-------
x | 1
x | 2
x | 3
y | 2
y | 3
y | 3
(6 rows)
I want to know how many ones, twos, and threes there are for each 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;
which gives me:
event | ones | twos | threes
-------+------+------+--------
x | 1 | 1 | 1
y | 0 | 1 | 2
(2 rows)
which is fine, but I am wondering if there is a better way to do this?
I'd mainly like to reduce the number of subqueries involved. Another
improvement would be to not have to explicitly query for each level,
though this isn't as big since I know the range of levels in advance
(famous last words for a dba :-)
Thanks in advance,
Robert Treat
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: can i make this sql query more efficiant?
@ 2003-04-03 22:15 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Robert Treat <xzilla@users.sourceforge.net>
2 siblings, 0 replies; 8+ messages in thread
From: Franco Bruno Borghesi @ 2003-04-03 22:15 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql
if you're allowed to change the resultset structure, you could do:
SELECT
event,
level,
count(*)
FROM
baz
GROUP BY
event,
level;
event | level | count
-------+-------+-------
x | 1 | 1
x | 2 | 1
x | 3 | 1
y | 2 | 1
y | 3 | 2
(5 rows)
of course it doesn't show you the rows where the count is zero.
if you need the zeros, do this
SELECT
EL.event,
EL.level,
count(baz.*)
FROM
(
SELECT DISTINCT
B1.event, B2.level
FROM
baz B1
CROSS JOIN baz B2
) EL
LEFT JOIN baz ON (baz.event=EL.event AND baz.level=EL.level)
GROUP BY
EL.event,
EL.level;
event | level | count
-------+-------+-------
x | 1 | 1
x | 2 | 1
x | 3 | 1
y | 1 | 0
y | 2 | 1
y | 3 | 2
(6 rows)
hope it helps.
On Thursday 03 April 2003 18:02, Robert Treat wrote:
> create table baz (event text, level int);
>
> insert into baz values ('x',1);
> insert into baz values ('x',2);
> insert into baz values ('x',3);
> insert into baz values ('y',2);
> insert into baz values ('y',3);
> insert into baz values ('y',3);
>
> select * from baz;
>
> event | level
> -------+-------
> x | 1
> x | 2
> x | 3
> y | 2
> y | 3
> y | 3
> (6 rows)
>
>
> I want to know how many ones, twos, and threes there are for each 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;
>
> which gives me:
>
> event | ones | twos | threes
> -------+------+------+--------
> x | 1 | 1 | 1
> y | 0 | 1 | 2
> (2 rows)
>
>
> which is fine, but I am wondering if there is a better way to do this?
> I'd mainly like to reduce the number of subqueries involved. Another
> improvement would be to not have to explicitly query for each level,
> though this isn't as big since I know the range of levels in advance
> (famous last words for a dba :-)
>
> Thanks in advance,
>
> Robert Treat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: can i make this sql query more efficiant?
@ 2003-04-03 23:02 Tomasz Myrta <jasiek@klaster.net>
parent: Robert Treat <xzilla@users.sourceforge.net>
2 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-04-03 23:02 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql
<cut>
> 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;
>
> which gives me:
>
> event | ones | twos | threes
> -------+------+------+--------
> x | 1 | 1 | 1
> y | 0 | 1 | 2
> (2 rows)
<cut>
What about this:
select
event,
sum(case when level=1 then 1 else 0 end) as ones,
sum(case when level=2 then 1 else 0 end) as twos,
sum(case when level=3 then 1 else 0 end) as threes
from baz
group by event;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: can i make this sql query more efficiant?
@ 2003-04-03 23:13 Manfred Koizar <mkoi-pg@aon.at>
parent: Robert Treat <xzilla@users.sourceforge.net>
2 siblings, 0 replies; 8+ messages in thread
From: Manfred Koizar @ 2003-04-03 23:13 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
On 03 Apr 2003 16:02:04 -0500, Robert Treat
<xzilla@users.sourceforge.net> wrote:
>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;
>which is fine, but I am wondering if there is a better way to do this?
>I'd mainly like to reduce the number of subqueries involved.
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;
> Another
>improvement would be to not have to explicitly query for each level,
This might be a case for a clever set returning function, but that's
not my realm. Wait for Joe to jump in ;-)
Servus
Manfred
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: can i make this sql query more efficiant?
@ 2003-04-04 16:16 Josh Berkus <josh@agliodbs.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-04-04 16:16 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql
Tomasz,
> What about this:
> select
> event,
> sum(case when level=1 then 1 else 0 end) as ones,
> sum(case when level=2 then 1 else 0 end) as twos,
> sum(case when level=3 then 1 else 0 end) as threes
> from baz
> group by event;
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.
YMMV.
BTW, while it won't be faster, Joe Conway's crosstab function in /tablefunc
does this kind of transformation.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] can i make this sql query more efficiant?
@ 2003-04-04 19:03 Manfred Koizar <mkoi-pg@aon.at>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Manfred Koizar @ 2003-04-04 19:03 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql; pgsql-performance@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] can i make this sql query more efficiant?
@ 2003-04-04 19:26 Josh Berkus <josh@agliodbs.com>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-04-04 19:26 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Tomasz Myrta <jasiek@klaster.net>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql; pgsql-performance@postgresql.org
Manfred,
> I'd be honestly interested in the circumstances where you made that
> observation.
Hmmmm ... one of my database involves a "crosstab" converstion where there
were 13 possible values, and the converted table is heavily indexed. For
that case, I found using CASE statements to be slower.
For your example, how do the statistics change if you increase the number of
levels to 15 and put an index on them?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] can i make this sql query more efficiant?
@ 2003-04-05 02:08 Manfred Koizar <mkoi-pg@aon.at>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Manfred Koizar @ 2003-04-05 02:08 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance@postgresql.org
On Fri, 4 Apr 2003 11:26:14 -0800, Josh Berkus <josh@agliodbs.com>
wrote:
>For your example, how do the statistics change if you increase the number of
>levels to 15 and put an index on them?
CREATE TABLE baz (event int, level int);
INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5);
INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5) FROM baz;
...
INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5) FROM baz;
ANALYSE baz;
CREATE INDEX baz_e ON baz(event);
CREATE INDEX baz_l ON baz(level);
CREATE INDEX baz_el ON baz(event, level);
CREATE INDEX baz_le ON baz(level, event);
tup cluster case subsel
8K - 1219.90 msec 70605.93 msec (seq scan)
8K - 3087.30 msec (seq scan off)
16K - 3861.87 msec 161902.36 msec (seq scan)
16K - 31498.76 msec (seq scan off)
16K event 2407.72 msec 5773.12 msec
16K level 2298.08 msec 32752.43 msec
16K l, e 2318.60 msec 3184.84 msec
32K - 6571.57 msec 7381.22 msec
32K e, l 4584.97 msec 3429.94 msec
32K l, e 4552.00 msec 64782.59 msec
32K l, e 4552.98 msec 3544.32 msec (baz_l dropped)
64K - 17275.73 msec 26525.24 msec
64K - 17150.16 msec 26195.87 msec (baz_le dropped)
64K - 17286.29 msec 656046.24 msec (baz_el dropped)
64K e, l 9137.88 msec 21809.52 msec
64K e, l 9183.25 msec 6412.97 msec (baz_e dropped)
64K e, l 11690.28 msec 10022.44 msec (baz_el dropped)
64K e, l 11740.54 msec 643046.39 msec (baz_le dropped)
64K l, e 9437.65 msec 133368.20 msec
64K l, e 9119.48 msec 6722.00 msec (baz_l dropped)
64K l, e 9294.68 msec 6663.15 msec (baz_le dropped)
64K l, e 9259.35 msec 639754.27 msec (baz_el dropped)
256K - 59809.69 msec 120755.78 msec
256K - 59809.69 msec 114133.34 msec (baz_le dropped)
256K e, l 38506.41 msec 88531.54 msec
256K e, l 49427.43 msec 43544.03 msec (baz_e dropped)
256K l, e 56821.23 msec 575850.14 msec
256K l, e 57462.78 msec 67911.41 msec (baz_l dropped)
So yes, there are cases where subselect is faster than case, but case
is much more robust regarding correlation and indices.
Servus
Manfred
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-04-05 02:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-03 21:02 can i make this sql query more efficiant? Robert Treat <xzilla@users.sourceforge.net>
2003-04-03 22:15 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-04-03 23:02 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-04 16:16 ` Josh Berkus <josh@agliodbs.com>
2003-04-04 19:03 ` Manfred Koizar <mkoi-pg@aon.at>
2003-04-04 19:26 ` Josh Berkus <josh@agliodbs.com>
2003-04-05 02:08 ` Manfred Koizar <mkoi-pg@aon.at>
2003-04-03 23:13 ` Manfred Koizar <mkoi-pg@aon.at>
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