Re: CTE optimization fence on the todo list?

On Fri, May 1, 2015 at 2:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> * Multiply-referenced WITH item (unless the outer query applies
> identical constraints to each reference, which seems silly and not
> worth the cycles to check for).
>
Not sure if I understand this correctly. Look at this query, CTE q is
referenced twice and it is obviously expand it helps:

postgres=# explain with q as (select * from a) select * from q q1 join
q q2 on q1.i=q2.i where q1.i <= 10 and q2.i >=2;
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=1443.59..1526.35 rows=9 width=16)
CTE q
-> Seq Scan on a a_2 (cost=0.00..1443.00 rows=100000 width=8)
-> Index Scan using ai on a (cost=0.29..8.45 rows=9 width=8)
Index Cond: (i <= 10)
-> Index Scan using ai on a a_1 (cost=0.29..8.31 rows=1 width=8)
Index Cond: ((i = a.i) AND (i >= 2))
(7 rows)

Another question is that CTEs might be used as an optimization fence.
Think about a query like this:

WITH q1 as /* 5 table joins */, q2 as /* 5 tables join */, q3 ...,
q4 SELECT ...

If we expand all CTEs, we may end up optimize join with many tables
(could be bad). Or it is possible that users intentionally arrange
join in that way (more or less like hints) to override the optimizer.

We could look at geqo_threshold and decide how shall we expand, but
this may not be better than a GUC variable.

Regards,
Qingqing