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)