Re: num_sa_scans in genericcostestimate

On Sun, Aug 21, 2022 at 2:45 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> ...
>

> The context for this is that I was looking at cases where btree indexes
> were not using all the columns they could, but rather shoving some of the
> conditions down into a Filter unnecessarily/unhelpfully. This change
> doesn't fix that, but it does seem to be moving in the right direction.
>

Added to commitfest.

> This does cause a regression test failure due to an (apparently?)
> uninteresting plan change.
>

Looking more at the regression test plan change, it points up an
interesting question which is only tangentially related to this patch.

With patch applied:

[local] 417536 regression=# explain analyze SELECT thousand, tenthous FROM
tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=4.55..4.56 rows=1 width=8) (actual time=0.100..0.101 rows=2
loops=1)
Sort Key: thousand
Sort Method: quicksort Memory: 25kB
-> Index Only Scan using tenk1_thous_tenthous on tenk1
(cost=0.29..4.50 rows=1 width=8) (actual time=0.044..0.048 rows=2 loops=1)
Index Cond: ((thousand < 2) AND (tenthous = ANY
('{1001,3000}'::integer[])))
Heap Fetches: 0
Planning Time: 1.040 ms
Execution Time: 0.149 ms
(8 rows)

[local] 417536 regression=# set enable_sort TO off ;

[local] 417536 regression=# explain analyze SELECT thousand, tenthous FROM
tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
ORDER BY thousand;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1 (cost=0.29..4.71
rows=1 width=8) (actual time=0.021..0.024 rows=2 loops=1)
Index Cond: (thousand < 2)
Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
Rows Removed by Filter: 18
Heap Fetches: 0
Planning Time: 0.156 ms
Execution Time: 0.039 ms
(7 rows)

Why does having the =ANY in the "Index Cond:" rather than the "Filter:"
inhibit it from understanding that the rows will still be delivered in
order by "thousand"?

Cheers,

Jeff

>