cte-index.txt
text/plain
archiveopteryx=# SET enable_mergejoin = false;
SET
archiveopteryx=# EXPLAIN ANALYZE WITH RECURSIVE t (mailbox, uid, date, subject, sender, has_attachments, parent_uid, idx, depth) AS (
SELECT mailbox, uid, date, subject, sender, has_attachments, parent_uid, uid::text, 1
FROM arc_messages
WHERE parent_uid IS NULL AND mailbox = 17 AND date >= '2007-11-01' AND date < '2007-12-01'
UNION ALL
SELECT a.mailbox, a.uid, a.date, a.subject, a.sender, a.has_attachments, a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
FROM t JOIN arc_messages a USING (mailbox)
WHERE t.uid = a.parent_uid
) SELECT * FROM t ORDER BY idx ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=104762.75..104770.98 rows=1647 width=121) (actual time=34.315..36.331 rows=1428 loops=1)
Sort Key: t.idx
Sort Method: quicksort Memory: 366kB
CTE t
-> Recursive Union (cost=0.00..104580.17 rows=1647 width=130) (actual time=0.040..24.851 rows=1428 loops=1)
-> Index Scan using arc_messages_mailbox_parent_id_date_key on arc_messages (cost=0.00..486.42 rows=567 width=94) (actual time=0.034..1.467 rows=482 loops=1)
Index Cond: ((mailbox = 17) AND (parent_uid IS NULL) AND (date >= '2007-11-01 00:00:00+01'::timestamp with time zone) AND (date < '2007-12-01 00:00:00+01'::timestamp with time zone))
-> Nested Loop (cost=0.00..10408.72 rows=108 width=130) (actual time=0.018..1.235 rows=63 loops=15)
-> WorkTable Scan on t (cost=0.00..22.68 rows=5670 width=44) (actual time=0.001..0.138 rows=95 loops=15)
-> Index Scan using arc_messages_mailbox_parent_id_key on arc_messages a (cost=0.00..1.82 rows=1 width=94) (actual time=0.005..0.006 rows=1 loops=1428)
Index Cond: ((a.mailbox = t.mailbox) AND (a.parent_uid = t.uid))
-> CTE Scan on t (cost=0.00..6.59 rows=1647 width=121) (actual time=0.045..30.543 rows=1428 loops=1)
Total runtime: 38.559 ms
(13 rows)