On Sun, Aug 21, 2022 at 2:45 PM Jeff Janes <jeff.ja...@gmail.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
>
