Why is Postgres CTE slower than subquery? I have a somewhat involved query that splits strings and outputs each word as a record.

I did a quick test, one with a CTE and one with a subquery and was somewhat surprised to see that the CTE takes twice as long to execute.

Here is the gist of what the query does:

-- 1. translate matches characters from comment to given list (of symbols) and replaces them with commas.
-- 2. string_to_array splits string by comma and puts in an array
-- 3. unnest unpacks the array into rows


Inline subquery

SELECT
    sub_query.word,
    sub_query._created_at
FROM 
(   SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.&lt;&gt;?/;:@#~[{]}=+-_)("*&amp;^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
        nps_reports.comment,
        nps_reports._id,
        nps_reports._created_at
    FROM nps_reports
    WHERE nps_reports.comment::text &lt;&gt; 'undefined'::text
) sub_query 
WHERE sub_query.word IS NOT NULL AND NOT (sub_query.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY sub_query._created_at DESC;


CTE

WITH split AS
(
SELECT unnest(string_to_array(translate(nps_reports.comment::text, ' ,.&lt;&gt;?/;:@#~[{]}=+-_)("*&amp;^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
    nps_reports.comment,
    nps_reports._id,
    nps_reports._created_at
FROM nps_reports
WHERE nps_reports.comment::text &lt;&gt; 'undefined'::text
)

SELECT
    split.word,
    split._created_at
FROM split
WHERE split.word IS NOT NULL AND NOT (split.word IN ( SELECT stop_words.stop_word FROM stop_words))
ORDER BY split._created_at DESC;


And here are the EXPLAINs for each:

Subquery Explain

Sort  (cost=15921589.76..16082302.91 rows=64285258 width=40) (actual time=16299.150..17697.914 rows=4394788 loops=1)
  Sort Key: sub_query._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=22915 read=7627, temp read=34281 written=34281
  -&gt;  Subquery Scan on sub_query  (cost=2.49..2311035.10 rows=64285258 width=40) (actual time=0.177..13274.895 rows=4394788 loops=1)
        Filter: ((sub_query.word IS NOT NULL) AND (NOT (hashed SubPlan 1)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=22915 read=7627
        -&gt;  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=88) (actual time=0.073..9781.244 rows=8071091 loops=1)
              Filter: ((comment)::text &lt;&gt; 'undefined'::text)
              Rows Removed by Filter: 844360
              Buffers: shared hit=22914 read=7627
        SubPlan 1
          -&gt;  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.016..0.034 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.115 ms
Execution time: 18451.245 ms


CTE Explain

Sort  (cost=17213755.76..17374468.91 rows=64285258 width=40) (actual time=44008.467..45508.786 rows=4394788 loops=1)
  Sort Key: split._created_at DESC
  Sort Method: external merge  Disk: 116112kB
  Buffers: shared hit=23031 read=7531, temp read=34281 written=353942
  CTE split
    -&gt;  Seq Scan on nps_reports  (cost=0.00..695825.11 rows=129216600 width=135) (actual time=0.057..10451.951 rows=8071091 loops=1)
          Filter: ((comment)::text &lt;&gt; 'undefined'::text)
          Rows Removed by Filter: 844360
          Buffers: shared hit=23027 read=7531
  -&gt;  CTE Scan on split  (cost=2.49..2907375.99 rows=64285258 width=40) (actual time=0.162..37888.364 rows=4394788 loops=1)
        Filter: ((word IS NOT NULL) AND (NOT (hashed SubPlan 2)))
        Rows Removed by Filter: 3676303
        Buffers: shared hit=23028 read=7531, temp written=319661
        SubPlan 2
          -&gt;  Seq Scan on stop_words  (cost=0.00..2.19 rows=119 width=4) (actual time=0.009..0.030 rows=119 loops=1)
                Buffers: shared hit=1
Planning time: 0.649 ms
Execution time: 46297.825 ms
CTE's in PostgreSQL are an optimization fence. That means the query planner doesn't push optimizations across a CTE boundary.


Blog entry about this


I think a lot of this is silly though you can just write it like this.. Here we use CROSS JOIN LATERAL rather than the complex wrapping and NOT EXISTS rather than NOT IN

SELECT word,
  _created_at
FROM nps_reports
CROSS JOIN LATERAL unnest(regexp_split_to_array(
  nps_reports.comment,
  '[^a-zA-Z0-9]+'
)) AS word
WHERE nps_reports.comment &lt;&gt; 'undefined'
  AND nps_reports.comment IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM stop_words
    WHERE stop_words.stop_word = word
  )
ORDER BY _created_at DESC;


All of that said, whatever you're doing seems to be reinventing FTS. So that's also a bad idea.