Re: POC, WIP: OR-clause support for indexes

On Fri, Oct 4, 2024 at 8:31 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Personally, I don't think this particular limitation is a problem. I
> don't think it will be terribly frequent in practice, and it doesn't
> seem any weirder than any of the other things that happen as a result
> of small and large integer constants being differently typed.

While it's not enough of a problem to hold up the patch, the behavior
demonstrated by my test case does seem worse than what happens as a
result of mixing integer constants in other, comparable contexts. That
was the basis of my concern, really.

The existing IN() syntax somehow manages to produce a useful bigint[]
SAOP when I use the same mix of integer types/constants that were used
for my original test case from yesterday:

pg(at)regression:5432 =# explain (analyze,buffers) select * from tenk1
where four in (1, 2_147_483_648) order by four, ten limit 5;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY
PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.29..1.73 rows=5 width=244) (actual time=0.009..0.010
rows=5 loops=1) │
│ Buffers: shared hit=4
│
│ -> Index Scan using tenk1_four_ten_idx on tenk1
(cost=0.29..721.25 rows=2500 width=244) (actual time=0.008..0.009
rows=5 loops=1) │
│ Index Cond: (four = ANY ('{1,2147483648}'::bigint[]))
│
│ Index Searches: 1
│
│ Buffers: shared hit=4
│
│ Planning Time: 0.046 ms
│
│ Execution Time: 0.017 ms
│
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)

--
Peter Geoghegan