2012/10/9 Serge Fonville serge.fonvi...@gmail.com:
This indeed is a very interesting question.
At http://wiki.postgresql.org/wiki/CTEReadme it seems to suggest that a CTE
is just rewritten and the resulting query is executed.
As was mentioned a couple of times in this list, CTE do have
optimization fence feature (per SQL Standard).
I asked on the #postgresql channel and was pointed, that typically you
get benefits of this feature
when you have to join grouping subquery to itself.
I went and did some tests. Table attempt contains e-mail delivery
attempts for the postfix:
# select
relname,relpages,reltuples::numeric,pg_size_pretty(pg_relation_size(oid))
from pg_class where relname='attempt';
relname | relpages | reltuples | pg_size_pretty
-+--+---+
attempt | 145117 | 4252530 | 1134 MB
My default work_mem is 1MB on this instance.
First, plain query with 2 subqueries:
# explain (analyze, buffers)
select a.eid, b.eid from
(select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg,
max(tstamp) as maxmsg from attempt group by recipient_email_id) a,
(select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg,
max(tstamp) as maxmsg from attempt group by recipient_email_id) b
where a.minmsg = b.maxmsg;
QUERY PLAN
---
Merge Join (cost=1861911.11..1953183.16 rows=6067386 width=16)
(actual time=65758.378..66115.400 rows=59845 loops=1)
Merge Cond: (a.minmsg = b.maxmsg)
Buffers: shared hit=1590 read=288644, temp read=103129 written=103134
- Sort (cost=930955.56..931042.64 rows=34835 width=16) (actual
time=30242.503..30370.379 rows=212434 loops=1)
Sort Key: a.minmsg
Sort Method: external merge Disk: 5400kB
Buffers: shared hit=779 read=144338, temp read=51481 written=51481
- Subquery Scan on a (cost=873875.76..927729.06 rows=34835
width=16) (actual time=26744.434..30008.996 rows=212434 loops=1)
Buffers: shared hit=779 read=144338, temp read=50561
written=50561
- GroupAggregate (cost=873875.76..927380.71
rows=34835 width=16) (actual time=26744.433..29951.390 rows=212434
loops=1)
Buffers: shared hit=779 read=144338, temp
read=50561 written=50561
- Sort (cost=873875.76..884507.08 rows=4252528
width=16) (actual time=26744.273..28296.850 rows=4255749 loops=1)
Sort Key: public.attempt.recipient_email_id
Sort Method: external merge Disk: 108168kB
Buffers: shared hit=779 read=144338, temp
read=50561 written=50561
- Seq Scan on attempt
(cost=0.00..187642.28 rows=4252528 width=16) (actual
time=0.010..13618.612 rows=4255749 loops=1)
Buffers: shared hit=779 read=144338
- Materialize (cost=930955.56..931129.73 rows=34835 width=16)
(actual time=35515.860..35640.974 rows=214271 loops=1)
Buffers: shared hit=811 read=144306, temp read=51648 written=51653
- Sort (cost=930955.56..931042.64 rows=34835 width=16)
(actual time=35515.853..35586.598 rows=210800 loops=1)
Sort Key: b.maxmsg
Sort Method: external merge Disk: 5384kB
Buffers: shared hit=811 read=144306, temp read=51648
written=51653
- Subquery Scan on b (cost=873875.76..927729.06
rows=34835 width=16) (actual time=31879.743..35251.218 rows=212434
loops=1)
Buffers: shared hit=811 read=144306, temp
read=50561 written=50561
- GroupAggregate (cost=873875.76..927380.71
rows=34835 width=16) (actual time=31879.741..35184.965 rows=212434
loops=1)
Buffers: shared hit=811 read=144306, temp
read=50561 written=50561
- Sort (cost=873875.76..884507.08
rows=4252528 width=16) (actual time=31879.577..33460.975 rows=4255749
loops=1)
Sort Key: public.attempt.recipient_email_id
Sort Method: external merge Disk: 108168kB
Buffers: shared hit=811 read=144306,
temp read=50561 written=50561
- Seq Scan on attempt
(cost=0.00..187642.28 rows=4252528 width=16) (actual
time=0.012..17637.516 rows=4255749 loops=1)
Buffers: shared hit=811 read=144306
Total runtime: 67611.657 ms
(34 rows)
The source relation is scanned twice. Now, using CTE and it's
materialization feature:
# explain (analyze, buffers)
with msgs as (select recipient_email_id eid, count(*) cnt, min(tstamp)
as minmsg, max(tstamp) as maxmsg from attempt group by
recipient_email_id)
select a.eid, b.eid from msgs a, msgs b where a.minmsg=b.maxmsg;
