Re: Apply extended statistics to join clause during parameterized path costing

On 8/17/26 13:17, Ilia Evdokimov wrote:
> Hi hackers,
>
> ...
>                                                            QUERY PLAN   
>                                                         
> ---------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop  (cost=5.86..4474.19 rows=98 width=18) (actual
> time=0.772..14.091 rows=10000.00 loops=1)
>    Buffers: shared hit=9844 read=265
>    ->  Seq Scan on small t2  (cost=0.00..1.50 rows=50 width=8) (actual
> time=0.297..0.301 rows=50.00 loops=1)
>          Buffers: shared read=1
>    ->  Bitmap Heap Scan on big t1  (cost=5.86..87.45 *rows=200*
> width=10) (actual time=0.040..0.253 rows=200.00 loops=50)
>          Recheck Cond: ((a = t2.a) AND (b = t2.b))
>          Heap Blocks: exact=10000
>          Buffers: shared hit=9844 read=264
>          ->  Bitmap Index Scan on big_ab_idx  (cost=0.00..5.81 rows=200
> width=0) (actual time=0.014..0.014 rows=200.00 loops=50)
>                Index Cond: ((a = t2.a) AND (b = t2.b))
>                Index Searches: 50
>                Buffers: shared hit=98 read=10
>  Planning:
>    Buffers: shared hit=35 read=5
>  Planning Time: 0.648 ms
>  Execution Time: 14.428 ms
> (16 rows)
> ```
>
> The row estimate (200) now matches actual exactly.
>
> Implementation
> -----------------------
>
> A clause is now also considered compatible when it has exactly two
> varnos, one of which is the 'relid' being estimated and the other
> belongs to some single other relation. That other side is treated like a
> pseudoconstant for the purposes of this check - its actual value doesn't
> matter, only that it is fixed for the duration of one parameterized
> probe. The degree logic itself is unchanged.
>
> When clause list doesn't reference a single relation but is being
> estimated for a specific varRelid - i.e. we are costing a parameterized
> path - look up that relation's dependency extended statistics and apply
> them the same way,via dependencies_clauselist_selectivity.
>
> Note this only helps the `dependencies` kind of ext stats. There is
> already ongoing work in this direction [0].
>

Why couldn't it leverage ndistict and/or MCV list too? I was imagining
we'd use mostly the same logic as for regular univariate stats.

> Any feedback are welcome.
>

I agree using existing per-table extended statistics for estimating
joins is a good idea, and something I suggested in the past. But doing
it this way also introduces an annoying inconsistency, because it
changes estimate for the scan, not for the join.

This is actually visible in your example, where you have

> Nested Loop (cost=5.86..4474.19 rows=98 width=18)
> -> Seq Scan on small t2 (cost=0.00..1.50 rows=50 width=8)
> -> Bitmap Heap Scan on big t1 (cost=5.86..87.45 rows=200 ...

but 50 * 200 != 98.

I do think we'd need to make sure the join cardinality estimate also
considers the per-table statistics. Say, eqjoinsel() would need to look
not just for regular per-attribute ndistinct / MCV / histograms, but
also the extended stats.

It's not entirely trivial, though. It's likely that only one side of the
join has extended stats (e.g. fact table has MCV, but the dimension side
is uniform/unique, and so has just basic per-attribute stats). So we'd
need to consider extended-extended as well as extended-plain and
plain-plain cases.

regards

--
Tomas Vondra