Feedback-ID: i7a6842ed:Fastmail
MIME-Version: 1.0
Date: Mon, 11 Aug 2025 12:11:05 +0200
From: "Quentin de Metz" <quentin@de.me.tz>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: pgsql-novice@postgresql.org
Message-Id: <7680b9a0-5e90-402e-ba56-e3ffca80c44c@app.fastmail.com>
In-Reply-To: <4037726.1738356169@sss.pgh.pa.us>
References: <58782480-ab75-4416-a177-ccf91be288a9@app.fastmail.com>
 <4037726.1738356169@sss.pgh.pa.us>
Subject: Re: btree_gin, bigint and number literals
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7680b9a0-5e90-402e-ba56-e3ffca80c44c%40app.fastmail.com>
Precedence: bulk

Hello Tom,

I see that you have commited a change (e2b64fcef35f70f96fa92db56fbfa9ac2da136c7) which addresses this issue. Thank you!

I looked into this issue recently and still don't understand why this would not work for other hardware variants. Will it yield the wrong plan, or will the plan's execution yield wrong results?

I'm surprised because the SQL changes I proposed seemed relatively aligned with the existing extension source code which references support functions defined in code related to btree indexes (e.g. btint2cmp). These functions are already hardware-independent. Aren't these functions the ones called by the engine when executing a query and going through the index - as explained here (https://www.postgresql.org/docs/18/xindex.html#XINDEX-OPFAMILY)?

Also there a specific reason the integer-related operator classes defined in btree_gin (int2_ops, int4_ops, int8_ops) don't belong to the same operator family? That seems to be the direction suggested by the documentation I linked to above.

Finally, what is your approach to testing on 32-bit or big-endian hardware?

Thank you for your guidance,

Quentin


On Fri, Jan 31, 2025, at 21:42, Tom Lane wrote:
> "Quentin de Metz" <quentin@de.me.tz> writes:
>> On a multi-column GIN index over a bigint column and a text column, the query planner does not filter the index on the bigint column when a condition on this column is specified with a number literal.
>
> Yeah, because "owner_id = 12" will use int84eq, which as you observe
> is not supported by btree_gin's opclass.
>
>> Would you be open to considering a patch to include the ALTER OPERATOR snippet in the btree_gin install script, so that this works out of the box?
>
> I'd be quite surprised if that "just works" without any corresponding
> changes in the C code, because btree_gin.c only knows about applying
> same-type-on-both-sides comparison functions.  (int8 vs int4 might
> appear to work as long as you don't try very hard, but for example
> it'd fail on 32-bit or big-endian hardware.)  If you feel like writing
> a patch that actually takes care of the matter fully, step right up.
>
> 			regards, tom lane