BUG #17721: A completely unused CTE negatively affect Query Plan - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17721
Logged by:          Nathaniel Hazelton
Email address:      nh.dev@sturdy.ai
PostgreSQL version: 15.1
Operating system:   Docker
Description:

I've boiled down an issue we have in production to a simple query that can
demonstrate it.  I've run this on 13,14 and 15 locally in a docker container
with the same results.  If a CTE that is completely unexecuted exists in a
subquery (or view in our production case) it affects the query plan VERY
negatively.  The first explain shows a sequential scan, where the second
explain shows an index scan, just by the existence of the (obviously)
unexecuted CTE.

In this example, the plans might not differ much in performance.  However,
in our production system, the parallel to this is VERY costly.  In fresh
local docker Postgres, the random page cost must be set to 1 to reflect this
issue.  With our production data, the page cost is the default 4, and
reflects the issue.



drop table if exists conv, conv_acc;

select setseed(0);

create temp table conv as (select id, 'meta' as meta from generate_series
(1, 1000) id);

create temp table conv_acc as (
    select conv.id, acc_id
    from conv
    left join lateral (
        select conv.id, floor(random()*1000) as acc_id
        from generate_series (1, 4) limit floor(random()*4+1)::int
    ) acc on true
);


create index conv_acc_id_index
        on conv_acc(id);

analyze conv; analyze conv_acc;

set random_page_cost to 1;

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select * from
    (select id from conv limit 10) limitconv
    left join (
    with someccte as materialized (select 1/0)
        select id
        from    conv_acc ca
    ) vca
    on vca.id = limitconv.id;

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select * from
    (select id from conv limit 10) limitconv
    left join (
    --with someccte as materialized (select 1/0)
        select id
        from    conv_acc ca
    ) vca
    on vca.id = limitconv.id;