Re: Bad selectivity estimate when using a sub query to determine WHERE condition

On Mon, Feb 10, 2020 at 4:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Well, it's not that hard to get rid of that scalar sub-select: since
> you're already relying on current_rollup to produce exactly one row,
> you could write a plain join instead, something like
>

Using a join instead of the sub-select did already help.

EXPLAIN (ANALYZE, BUFFERS ) WITH current_rollup AS (
SELECT COALESCE(MAX(window_end), '-infinity') AS cutoff
FROM exchange.ledger_zerosum_rollup
)
SELECT *
FROM exchange.ledger, current_rollup
WHERE created > current_rollup.cutoff;

https://explain.depesz.com/s/Zurb

I'm a bit confused, because the row estimate on the index scan for the
ledger table seems to be way off still,
but nonetheless the planner now chooses the index scan.
Maybe it has more insight into the result of the CTE this way?
Or picks the index scan because it fits well with the nested loop?

> The form of cheating that comes to mind is to wrap the sub-select
> in a function that's marked STABLE:
> create function current_rollup_cutoff() returns timestamp -- or whatever
> stable language sql as $$
> SELECT COALESCE(MAX(window_end), '-infinity') AS cutoff
> FROM exchange.ledger_zerosum_rollup
> $$;
> SELECT *
> FROM exchange.ledger
> WHERE created > current_rollup_cutoff();
> I have not actually tried this, but I think that since the function is
> marked stable, the planner would test-run it to get an estimated value,
> and then produce a plan similar to what you'd get with a literal constant.

The version with a function is even better, the query planner now uses
good estimates and produces a trivial execution plan.
I'll go with that one as it seems to be the most future proof approach.

https://explain.depesz.com/s/34m8

Thanks for your insight!

Best Regards
Chris