Showing posts with label CTE. Show all posts
Showing posts with label CTE. Show all posts

Thursday, September 18, 2025

MySQL automatically indexes materialized CTEs

Did you know -

When you use a CTE in MySQL, should the optimizer choose to materialize it, it may automatically create an index on the fly, if it thinks your query could benefit from it?

Let's dig a bit into this.

First a little context, in case anyone is not aware, the materialization approach to CTE processing is when MySQL chooses to execute the CTE query, and store its results in an internal temporary table, to be reused in the parent query. This tends to be a useful approach when the CTE is referenced multiple times.

The alternate approach is to merge the CTE into the parent query. I won't discuss the merge approach for now.

For example, say you ran the following query:

WITH data_cte AS (
    /* expensive query */ 
    SELECT group_id, AVG(x) AS avg_x, MAX(x) AS max_x FROM table_100m GROUP BY group_id
)
/* "parent" part of the query; notice multiple references to data_cte
SELECT group_id, avg_x FROM data_cte WHERE max_x = 100000 -- search by max_x
UNION ALL
SELECT group_id, avg_x FROM data_cte WHERE max_x = 99999
UNION ALL
SELECT group_id, avg_x FROM data_cte WHERE max_x = 99998
;


For this query, MySQL's optimizer may be able to materialize the CTE. In addition to that, because the parent query searches by max_x three times, the optimizer may decide, "hey, I'd better create an index on max_x in the materialization."

The execution plan shows that:

1. The CTE called data_cte was indeed materialized

...and...

2. For all three lookups by max_x in the parent query, we can see in the plan an index lookup occurred. e.g. Index lookup on avg_data_cte using <auto_key0> (max_x = 99998). MySQL's optimizer automatically created an index to assist in these searches!

Execution plan:

 | -> Append  (cost=10.5 rows=30) (actual time=16912..16912 rows=2115 loops=1)
    -> Stream results  (cost=3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
        -> Filter: (data_cte.max_x = 100000)  (cost=3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 100000)  (cost=0.35..3.5 rows=10) (actual time=16912..16912 rows=419 loops=1)
                -> Materialize CTE data_cte if needed  (cost=0..0 rows=0) (actual time=16912..16912 rows=10000 loops=1)
                    -> Table scan on <temporary>  (actual time=16909..16909 rows=10000 loops=1)
                        -> Aggregate using temporary table  (actual time=16909..16909 rows=10000 loops=1)
                            -> Table scan on table_100m  (cost=10.2e+6 rows=99.8e+6) (actual time=2.2..8625 rows=100e+6 loops=1)
    -> Stream results  (cost=3.5 rows=10) (actual time=0.0248..0.117 rows=786 loops=1)
        -> Filter: (data_cte.max_x = 99999)  (cost=3.5 rows=10) (actual time=0.014..0.0798 rows=786 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 99999)  (cost=0.35..3.5 rows=10) (actual time=0.0129..0.0546 rows=786 loops=1)
                -> Materialize CTE data_cte if needed (query plan printed elsewhere)  (cost=0..0 rows=0) (never executed)
    -> Stream results  (cost=3.5 rows=10) (actual time=0.00196..0.108 rows=910 loops=1)
        -> Filter: (data_cte.max_x = 99998)  (cost=3.5 rows=10) (actual time=0.00183..0.0774 rows=910 loops=1)
            -> Index lookup on data_cte using <auto_key0> (max_x = 99998)  (cost=0.35..3.5 rows=10) (actual time=0.00158..0.0493 rows=910 loops=1)
                -> Materialize CTE data_cte if needed (query plan printed elsewhere)  (cost=0..0 rows=0) (never executed)

Notice the three instances of Index lookup on data_cte using <auto_key0> - that's the automagic index goodness in action.

By the way, this isn't just something we have to infer via experiment. This behavior is explicitly mentioned in the MySQL documentation:

For cases when a derived table requires materialization, the optimizer may add an index to the materialized table to speed up access to it. 

And it's worth mentioning it talks specifically about derived tables. MySQL optimizes CTEs, views, and derived table (a subquery that comes after the FROM) very similarly. So you can get this kind of auto-indexing with views and derived tables as well.

There's some meta-context here. There's a recurring debate in internet SQL discussions about what is better: CTEs or temp tables. In my view, the two apply to only partially overlapping use cases, but the idea is you could rewrite the above query by splitting it into two statements, the first of which substitutes a temp table for the CTE, like so:

CREATE TEMPORARY TABLE data_tmp AS 
    SELECT group_id, AVG(x) AS avg_x, MAX(x) AS max_x FROM table_100m GROUP BY group_id;

Then query the temp table, like so:

SELECT group_id, avg_x FROM data_tmp WHERE max_x = 100000 -- search by max_x
UNION ALL
SELECT group_id, avg_x FROM data_tmp WHERE max_x = 99999
UNION ALL
SELECT group_id, avg_x FROM data_tmp WHERE max_x = 99998
;


Unfortunately, MySQL presents a blocker to this query: an unfortunate limitation that prevents me from even testing this approach because you can't reference a temporary table multiple times in one query. 

ERROR 1137 (HY000): Can't reopen table: 'data_tmp'

But I could instead try creating a "normal" table that is only used temporarily, to serve this query, then drop it after.

Below is the execution plan from this approach. Observe that it did NOT use an index - instead it ran Table scan on data_tbl three times - because we didn't explicitly create an index:

| -> Append  (cost=3011 rows=2989) (actual time=0.0747..15.2 rows=2115 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.0734..6.31 rows=419 loops=1)
        -> Filter: (data_tbl.max_x = 100000)  (cost=1004 rows=996) (actual time=0.0675..6.21 rows=419 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0488..5.69 rows=10000 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.0383..4.23 rows=786 loops=1)
        -> Filter: (data_tbl.max_x = 99999)  (cost=1004 rows=996) (actual time=0.0304..4.15 rows=786 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0219..3.74 rows=10000 loops=1)
    -> Stream results  (cost=1004 rows=996) (actual time=0.025..4.56 rows=910 loops=1)
        -> Filter: (data_tbl.max_x = 99998)  (cost=1004 rows=996) (actual time=0.021..4.47 rows=910 loops=1)
            -> Table scan on data_tbl  (cost=1004 rows=9963) (actual time=0.0122..3.99 rows=10000 loops=1)


But we could explicitly create an index. After doing so, and rerunning the query, we see a similar execution plan to the search portion of the CTE query's plan:

| -> Append  (cost=277 rows=2115) (actual time=0.0567..3.73 rows=2115 loops=1)
    -> Stream results  (cost=63.7 rows=419) (actual time=0.056..0.928 rows=419 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 100000), with index condition: (data_tbl.max_x = 100000)  (cost=63.7 rows=419) (actual time=0.0504..0.88 rows=419 loops=1)
    -> Stream results  (cost=100 rows=786) (actual time=0.017..1.27 rows=786 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 99999), with index condition: (data_tbl.max_x = 99999)  (cost=100 rows=786) (actual time=0.0151..1.19 rows=786 loops=1)
    -> Stream results  (cost=113 rows=910) (actual time=0.0143..1.42 rows=910 loops=1)
        -> Index lookup on data_tbl using my_idx (max_x = 99998), with index condition: (data_tbl.max_x = 99998)  (cost=113 rows=910) (actual time=0.012..1.32 rows=910 loops=1)


When this debate arises on the r/sql subreddit, I see temp tables getting all the upvotes over CTEs. I'm not always in agreement with the reasoning. Probably the single biggest fallacy I notice is they discuss how the CTEs vs. temp tables are processed under the hood, without specifying a DBMS. But you've heard my rants on that subject about 100 times.

One of the reasons sometimes offered is that you can index a temp table, but (supposedly) can't do that with a CTE. As we've seen just now, this is not true in MySQL. In fact one could argue the CTE is more automagic and therefore preferable. 

Also consider that merging may be the better approach than materialization, which is also part of the optimizer's automagic! If you create a temp table, that's forcing a materialization, whether it's better or not. If the MySQL optimizer is doing its job right - which it usually does - we should let it do its thing and make the right decision. To be fair, it's far from perfect.

I'd also wager that at least 80% of developers wouldn't think to make the explicit index on their temp table.

Automagic ftw, in my opinion. Unless the automagic fails, then take other measures.

Here's one example of a Reddit comment on the debate. This is just a fundamental misunderstanding. (I'd feel better about it if it didn't get piles of upvotes.)

 

A classic r/sql error: sweeping statements about SQL engine behavior that are 1) not correct (at least not always, and far from always), 2) not qualified with a specific DBMS, and 3) somehow upvoted heavily

Contradicting the Reddit commenter, and their 26 upvoters, we've observed already that the CTE materialization MySQL can perform means the CTE query is NOT executed once per reference While it's possible they are correct about *some* DBMS out there (and perhaps in some querying scenarios but not all), every one of the several other DBMSs for which I've tested this subject is also capable of materializing CTEs.

It used to be the case in Postgres that CTEs were always materialized! That isn't the best approach 100% of the time.

Funny thing is, in MySQL, the materialized CTE literally becomes a temp table under the hood! So it almost turns into an argument of, "My temp table is better than your temp table!" You just don't have to manage it yourself using the CTE approach, and again, MySQL might choose the merge approach which may run better. Automagic ftw!

But I may be digressing a bit. I'll cut the discussion here unless anyone has any questions/comments. 


Thursday, October 03, 2019

PostgreSQL 12 released today!

PostgreSQL 12 was released today, and among many cool features, we have the much-awaited removal of the CTE optimization fence! Let's take a quick, hands-on look...

Please review my old post that described how it worked in previous versions: https://mwrynn.blogspot.com/2016/04/common-table-expressions-postgres-vs.html

In short, when you used a CTE, it materialized the results in memory, then ran the subsequent query against that.

You can still do that if you want in Postgres 12 with the "materialized" keyword, but you're not forced to.

So let's repeat the test I did 3 and a half years ago! Postgres 12 only - I won't repeat on Oracle, nor will I make fun of MySQL this time.

Here's what I've got:


mwrynn=# EXPLAIN ANALYZE
WITH peeps_by_state AS NOT MATERIALIZED                               (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state)
SELECT cnt                                                          FROM peeps_by_state                                                 WHERE state=13;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on peeps_by_state  (cost=1000.00..484733.72 rows=50 width=8) (actual time=3687.870..3687.871 rows=1 loops=1)
   ->  Finalize GroupAggregate  (cost=1000.00..484733.22 rows=50 width=12) (actual time=3687.869..3687.869 rows=1 loops=1)
         Group Key: person_location.state
         ->  Gather  (cost=1000.00..484732.22 rows=100 width=12) (actual time=3687.732..3694.247 rows=3 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial GroupAggregate  (cost=0.00..483722.22 rows=50 width=12) (actual time=3682.099..3682.099 rows=1 loops=3)
                     Group Key: person_location.state
                     ->  Parallel Seq Scan on person_location  (cost=0.00..481655.75 rows=413195 width=4) (actual time=0.448..3600.027 rows=333333 loops=3)
                           Filter: (state = 13)
                           Rows Removed by Filter: 16333333
 Planning Time: 0.159 ms
 Execution Time: 3694.318 ms
(13 rows)

Now let's use the "MATERIALIZED" keyword and compare.


mwrynn=# EXPLAIN ANALYZE
WITH peeps_by_state AS MATERIALIZED                                   (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state)
SELECT cnt                                                          FROM peeps_by_state                                                 WHERE state=13;
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on peeps_by_state  (cost=534753.70..534754.83 rows=1 width=8) (actual time=11828.277..11828.392 rows=1 loops=1)
   Filter: (state = 13)
   Rows Removed by Filter: 49
   CTE peeps_by_state
     ->  Finalize GroupAggregate  (cost=534741.03..534753.70 rows=50 width=12) (actual time=11828.191..11828.328 rows=50 loops=1)
           Group Key: person_location.state
           ->  Gather Merge  (cost=534741.03..534752.70 rows=100 width=12) (actual time=11828.181..11835.081 rows=150 loops=1)
                 Workers Planned: 2
                 Workers Launched: 2
                 ->  Sort  (cost=533741.01..533741.14 rows=50 width=12) (actual time=11818.697..11818.703 rows=50 loops=3)
                       Sort Key: person_location.state
                       Sort Method: quicksort  Memory: 27kB
                       Worker 0:  Sort Method: quicksort  Memory: 27kB
                       Worker 1:  Sort Method: quicksort  Memory: 27kB
                       ->  Partial HashAggregate  (cost=533739.10..533739.60 rows=50 width=12) (actual time=11818.610..11818.624 rows=50 loops=3)
                             Group Key: person_location.state
                             ->  Parallel Seq Scan on person_location  (cost=0.00..429572.40 rows=20833340 width=4) (actual time=0.521..3374.912 rows=16666667 loops=3)
 Planning Time: 0.300 ms
 Execution Time: 11835.398 ms
(19 rows)

So there we have it - a significant difference! (Note that the times here cannot be compared to the times in the previous post, as it's a different server, different config, etc.)

I'll end the post here, but I may go over the steps in this execution plan and try to explain what's going on - maybe in a subsequent post or maybe I'll just edit this one.

Sunday, February 17, 2019

"CTEs to no longer be an optimization fence (COMMITED) (git.postgresql.org)"

Saw this linked to on Reddit with the title I quoted in this post's title...

Great news in the world of Postgres: no longer will Common Table Expressions (CTE) be shackled to the evil CTE Optimization Fence! Linky

As I previously blogged about, the optimization fence is a drawback to CTEs in Postgres that in short potentially impacts performance when you use them. So you may use them to make a query neater but you can pay a penalty. If the new commits are all that they promise, this performance hit will be no more! Here's the link to my old post about them - linky - note that they should perform about as well Oracle now!

Saturday, June 04, 2016

Cumulative row count over date ranges

I recently helped a coworker (Mr. M) with his SQL query that gets a cumulative count over a set of ranges of dates. He wrote a query that functionally works just fine, but he didn't like it because there was a lot of repetition happening. It looked something like:

SELECT [date1], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date1] UNION ALL
SELECT [date2], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date2] UNION ALL
SELECT [date3], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date3] UNION ALL
SELECT [date4], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date4] UNION ALL
SELECT [date5], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date5]

Imagine in place of [datex] there is some mathy date expression -- I'm leaving it out for now (because I'm lazy) but the idea was subtract a week from today for date1, subtract 14 days from today for date2, etc.

So Mr. M did not like that his copy/pasted query parts violated DRY - Don't Repeat Yourself. I think that's less bad than all this UNIONing and repeated checking against CREATED_ON. It's running 5 different queries that are kind of redundant as they are looking up by the same column repeatedly. How can we improve this query? My first instinct was to use windowing functions, but alas, this is MySQL which lacks them (remember, MySQL is a hair band). I'm sure we can do better, but here's what I came up with as a first hack:

SELECT SUM(bucket1) AS week1,
 SUM(bucket2) AS week2,
 SUM(bucket3) AS week3,
 SUM(bucket4) AS week4,
 SUM(bucket5) as week5
FROM (
 SELECT CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(0*7) DAY) THEN 1 ELSE 0 END AS bucket1,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(1*7) DAY) THEN 1 ELSE 0 END AS bucket2,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(2*7) DAY) THEN 1 ELSE 0 END AS bucket3,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(3*7) DAY) THEN 1 ELSE 0 END AS bucket4,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(4*7) DAY) THEN 1 ELSE 0 END AS bucket5
FROM 
MYTABLE) sub

Well, I wasn't able to remove copy/pasted repetition, but I do think removing the UNION ALLs is a positive. What does this query do?

Step 1) The subquery. This is the bulk of the query, which passes over the whole table just once, and puts each row in an bucket from 1 through 5. It will perform 1 scan per row and 5 comparisons per row, vs. scanning each row 5 scans per row and 5 comparison per row.

Step 2) Sum them up!

Now in this specific instance the performance gain was pretty negligible, and we didn't really neaten the query all that much. I wouldn't blame Mr. M if he decides to stick with old query. As far as performance goes, the issue is this table was very small with only about 50k rows, therefore the cost of repeating 5 queries was negligible compared to all the added logic of those CASE WHEN expressions that I added.

Is there a way to avoid the repetition? 
The answer is, for MySQL, I don't know! But I wonder if I can figure something out with Postgres...

So first I made a sample table MYTABLE with 100,001 rows of randomly generated data.

To write the query, I decided to define the 5 dates using a common table expression, this way we can reference it as an imaginary table, so to speak:

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)
SELECT * FROM weeks;

          date_point           
-------------------------------
 2016-06-04 13:14:00.560272-04
 2016-05-28 13:14:00.560272-04
 2016-05-21 13:14:00.560272-04
 2016-05-14 13:14:00.560272-04
 2016-05-07 13:14:00.560272-04
(5 rows)

Next, let's join that to our table so that for each date_point, get the rows where created_on is less than or equal to the date_point...

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)
SELECT * FROM weeks JOIN mytable ON mytable.created_on <= weeks.date_point;

          date_point           | created_on 
-------------------------------+------------
 2016-06-04 13:15:40.869402-04 | 2015-12-12
 2016-06-04 13:15:40.869402-04 | 2016-02-19
 2016-06-04 13:15:40.869402-04 | 2016-04-20
 2016-06-04 13:15:40.869402-04 | 2016-02-01
[snipping tons of rows]

Final step: get the counts per date_point:

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)
SELECT COUNT(*), date_point
FROM weeks JOIN
     mytable ON mytable.created_on <= weeks.date_point
GROUP BY date_point
ORDER BY date_point DESC;

 count  |          date_point          
--------+------------------------------
 100001 | 2016-06-04 13:17:29.33812-04
  98232 | 2016-05-28 13:17:29.33812-04
  96290 | 2016-05-21 13:17:29.33812-04
  94313 | 2016-05-14 13:17:29.33812-04
  92350 | 2016-05-07 13:17:29.33812-04
(5 rows)

TA-DA!!! WE DID IT! We got the cumulative counts without copying/pasting several queries or several conditions - we just had to define the weeks we concerned about (weeks 0 through 4) in the call to generate_series(), put that in a CTE, and query that with a join... [Edit: Note, we should even be able to parameterize the number of weeks by doing something like generate_series(0,?) -- this way we could easily use any number of weeks.] My initial instinct of requiring window functions was wrong. But a big THANK YOU TO COMMON TABLE EXPRESSIONS. :) 

Question: Does anyone know how to do this in MySQL??

Day-After Edit: I guess a similar technique could be used in MySQL, only we must replace the CTE with a subquery, and replace generate_series with some way of selecting 0-4.... I guess this would work: (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) - but perhaps this would count as the type of repetition we want to avoid...Another edit: apparently there is a solution, albeit an ugly one: http://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql

Monday, April 04, 2016

Common Table Expressions: Postgres vs. Oracle vs. MySQL


NOTE: I see this post still gets views, but it is very old and out of date. MySQL has now had CTEs since 8.x and they work pretty well. Postgres no longer has a CTE optimization fence.  Not sure if Oracle's CTEs have matured any. I am leaving this post up, but again, it is out of date so don't assume it has any accuracy anymore. (Also note to self: qualify these tests with a DBMS version all the time!)

 

The Gist of CTEs

SQL Common Table Expressions (CTEs) are a neat way to enhance the readability of your SQL queries. They can help you manage complexity by avoiding copied/pasted subqueries, and they remove the (what I feel is often an imagined) need for temporary tables to store temporary results for another query to work with. CTEs were defined in ANSI/ISO Standard SQL-99, so if your SQL database does not support CTEs, that means that like your favorite hair band, they still haven't caught up with the 90s.


Quick Examples of CTEs

The idea is you have this kind of ugliness:

SELECT ...
FROM (SELECT ... WHERE ...) AS subqry
WHERE ...

Maybe it's even multiple subquery levels deep:

SELECT ...
FROM (SELECT ... FROM (SELECT ... WHERE ...) AS inner_subqry WHERE ...) AS outer_subqry
WHERE ...

Hard to write, hard to edit, hard to look at. A CTE is simply the use of the "WITH" clause to separate that subquery from the main body, substituting the subquery with the assigned alias.

WITH subqry AS (SELECT ... WHERE ...)
SELECT ... FROM subqry WHERE ...

Another use case: If you ever find yourself saying "Hmm, if I put THIS resultset into a temporary table, then I can query it later..." Like so:

--in general, DON'T DO THE FOLLOWING if you can help it.
CREATE TABLE helper_table AS SELECT ... FROM ... WHERE ... ;
SELECT some_stuff
FROM ... helper_table 
...;
--maybe you use it multiple times, to avoid recomputing helper_table's query!
SELECT some_other_stuff
FROM ... helper_table 
...;
DROP TABLE helper_table;

^^^The above makes me sad. :( You're using another table when you don't need to. It's messier. It's slower. It's unnecessary DDL (and on most databases, DDL necessitates a commit, don't forget), or even if you avoid the CREATE TABLE by keeping helper_table table alive all the time (maybe you delete/insert with every usage), you're creating unnecessary maintenance; unnecessary transaction logging. It's more complex. And if you have CTEs there's (rarely) any good reason to do it.


The Postgres Optimization Fence

Unfortunately, in Postgres there is an infamous downside to the otherwise lovely CTE: The Optimization Fence. The Postgres planner will, upon seeing your CTE query, decide to resolve that WITH part first, then take the results of that, and work it into the "main" part of the query. So behind the curtain, it's actually ALMOST doing something like the "CREATE TABLE helper_table" example above -- resolve that query first, store the results in memory, use that data when it is asked for.

Here is the mention of this problem from the docs:
“…that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)”
Ok! Let's observe by comparing two examples:

First I made a table called person_location that has 50,000,000 rows -- to represent a million people residing in each of the 50 states in the US.

1) CTE to get the number of people per state
mwrynn=# EXPLAIN ANALYZE
WITH peeps_by_state AS 
  (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state)
SELECT cnt
FROM peeps_by_state
WHERE state=13;
                                                                 QUERY PLAN                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on peeps_by_state  (cost=971241.21..971245.71 rows=1 width=8) (actual time=28868.625..28868.645 rows=1 loops=1
   Filter: (state = 13)
   Rows Removed by Filter: 49
   CTE peeps_by_state
     ->  HashAggregate  (cost=971239.21..971241.21 rows=200 width=4) (actual time=28868.592..28868.603 rows=50 loops=1)
           Group Key: person_location.stat
           ->  Seq Scan on person_location  (cost=0.00..721239.14 rows=50000014 width=4) (actual time=0.016..9207.389 rows=50000000 loops=1)
Planning time: 0.168 ms
Execution time: 28868.783 ms
(9 rows)

2) replace that reference to the CTE with a subquery:

mwrynn=# EXPLAIN ANALYZE
SELECT cnt
FROM (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state) peeps_by_state
WHERE state=13;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on peeps_by_state  (cost=0.00..851089.22 rows=1 width=8) (actual time=9925.626..9925.627 rows=1 loops=1)
   ->  GroupAggregate  (cost=0.00..851089.21 rows=1 width=4) (actual time=9925.624..9925.624 rows=1 loops=1)
         Group Key: person_location.state
         ->  Seq Scan on person_location  (cost=0.00..846239.20 rows=970000 width=4) (actual time=0.029..9750.655 rows=1000000 loops=1)
               Filter: (state = 13)
               Rows Removed by Filter: 49000000

 Planning time: 0.124 ms
 Execution time: 9925.680 ms
(8 rows)

Unfortunately, the uglier version of the query will be planned much better, so, sadly, Postgres gives you a performance incentive to use the ugly one. In this simple example above, it may not seem like much of a big deal to write the ugly one, but in a larger, more complex example, the ugly factor could be much more extreme.

In the above example, though, you could just apply the same filter to the CTE and probably get good results. But this is something extra to think about, something extra to deal with, and shouldn't be necessary in an ideal world. So let's try that out (looks good):

3) add the filter to the CTE:
mwrynn=# EXPLAIN ANALYZE WITH peeps_by_state AS (SELECT COUNT(*) AS cnt, state FROM person_location WHERE state=13 GROUP BY state)
SELECT cnt
FROM peeps_by_state
WHERE state=13;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on peeps_by_state  (cost=851089.21..851089.23 rows=1 width=8) (actual time=9895.863..9895.864 rows=1 loops=1)
   Filter: (state = 13)
   CTE peeps_by_state
     ->  GroupAggregate  (cost=0.00..851089.21 rows=1 width=4) (actual time=9895.851..9895.852 rows=1 loops=1)
           Group Key: person_location.state
           ->  Seq Scan on person_location  (cost=0.00..846239.20 rows=970000 width=4) (actual time=0.028..9722.994 rows=1000000 loops=1)
                 Filter: (state = 13)
                 Rows Removed by Filter: 49000000
 Planning time: 0.125 ms
 Execution time: 9895.923 ms

(10 rows)

Don't Worry, Oracle's Got This

Oracle, on the other hand, is quite a bit smarter about CTEs. It can "push down" the CTE and analyze the query as a whole. In other words, Oracle's query optimizer views the two queries as the same, and therefore they both resolve to the same (better) query plan. Good stuff. Let's observe...

--QRY 1
WITH peeps_by_state AS (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state)
SELECT cnt
FROM peeps_by_state
WHERE state=13;




--QRY 2
SELECT cnt
FROM (SELECT COUNT(*) AS cnt, state FROM person_location GROUP BY state) peeps_by_state
WHERE state=13;


Ta-da - same plan either way. Same time to execute either way. This is what we want.

(Note that the fact that the Oracle queries took longer than Postgres doesn't mean anything. I'm running Postgres on my Macbook Pro, and Oracle is running on the tiniest RDS instance Amazon will let me use. :))

MySQL's CTE Implementation

Finally, let's check out how well MySQL (InnoDB) handles CTEs:


(Still hasn't caught up with the 90s.)