Re: POC, WIP: OR-clause support for indexes - Mailing list pgsql-hackers

From Peter Geoghegan
Subject Re: POC, WIP: OR-clause support for indexes
Date
Msg-id CAH2-Wzm0_6VB-bgxqkE+pLtRmF358mJd6X0ExgCok4Z26_XvrQ@mail.gmail.com
Whole thread Raw
In response to Re: POC, WIP: OR-clause support for indexes  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: POC, WIP: OR-clause support for indexes
List pgsql-hackers
On Fri, Oct 4, 2024 at 8:31 AM Robert Haas <robertmhaas@gmail.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@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

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: On disable_cost
Next
From: Robert Haas
Date:
Subject: Re: POC, WIP: OR-clause support for indexes