Why is Postgres CTE slower than subquery?

Question

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, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
        nps_reports.comment,
        nps_reports._id,
        nps_reports._created_at
    FROM nps_reports
    WHERE nps_reports.comment::text <> '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, ' ,.<>?/;:@#~[{]}=+-_)("*&^%$£!`\|}'::text, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'::text), ','::text, ''::text)) AS word,
    nps_reports.comment,
    nps_reports._id,
    nps_reports._created_at
FROM nps_reports
WHERE nps_reports.comment::text <> '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
  ->  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
        ->  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 <> 'undefined'::text)
              Rows Removed by Filter: 844360
              Buffers: shared hit=22914 read=7627
        SubPlan 1
          ->  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
    ->  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 <> 'undefined'::text)
          Rows Removed by Filter: 844360
          Buffers: shared hit=23027 read=7531
  ->  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
          ->  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

Answer 1

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 <> '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.

Answer 2

@Evan Carroll explained why the CTE takes longer but here is an improved query, that is faster than all solutions listed above.

See this question for more background.

-- create custom dict (you don't necessarily need to do this)
CREATE TEXT SEARCH DICTIONARY simple_with_stop_words (TEMPLATE = pg_catalog.simple, STOPWORDS = english);
CREATE TEXT SEARCH CONFIGURATION public.simple_with_stop_words (COPY = pg_catalog.simple);
ALTER TEXT SEARCH CONFIGURATION public.simple_with_stop_words ALTER MAPPING FOR asciiword WITH simple_with_stop_words;

-- the actual query

SELECT 
    token.word, 
    nps._created_at
FROM nps_reports nps CROSS JOIN LATERAL UNNEST(to_tsvector('simple_with_stop_words', nps.comment)) token(word)
WHERE nps.comment IS NOT NULL AND
      nps.comment <> 'undefined' AND
      nps.language = 'en-US';

This utilises PostgreSQL's to_tsvector function which does several things depending on the configuration given to it. If used with the simple dictionary, instead of the custom one I made, it will simply split any string into words.

I am also utilising a feature from Postgres 9.3+, the LATERAL keyword, which enables me to pass an argument from the left side of the join to the right side of the join, i.e.: I can pass the comment into UNNEST.

This takes about 10 seconds to execute over the whole database. Compare to the previous fastest method (subquery), which took 18 seconds.