Cross-type index comparison support in contrib/btree_gin

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Cross-type index comparison support in contrib/btree_gin
Date: 2025-02-02 01:44:12
Message-ID: 262624.1738460652@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We've had multiple requests for $SUBJECT over the years
([1][2][3][4][5], and I'm sure my archive search missed some).
I finally decided to look into what it'd take to make that happen.
It's not as bad as I feared, and attached is a draft patch.

The thing that makes this sticky is that GIN itself doesn't support
any such thing as cross-type comparisons: all the Datums that it deals
with directly have to be of the same type as the stored index keys.
However, for the cases that btree_gin deals with, we can make use of
the "partial match" feature because all the entries we need to find
will be consecutive in the index. And it turns out that the
comparePartial() method is only ever applied to compare the original
query value with an index entry, which means that internally to
comparePartial() we can apply the proper cross-type comparison
operator. Our GIN index documentation about comparePartial() doesn't
quite say that in so many words, but btree_gin was already relying on
it --- in a very confusing and ill-explained way, if you ask me, but
it was relying on it. (The 0001 patch below is mainly concerned with
making that reliance simpler and clearer.)

The other thing that has to be dealt with is that cross-type or not,
we need to somehow create a Datum of the index key type to perform
the initial index descent with. But I realized that this isn't
that tough after all. Aside from boring change-of-representation
work, there are these special cases:

* Query value is out of range for the index type. We can simply
clamp it to the index type's range, so that GIN descends to one
end of the index or the other and then searches normally. GIN
might falsely think that the endmost entry(s) of the index equal
the search datum, but it doesn't matter too much what GIN thinks
because comparePartial can filter away the false matches by
applying the correct comparison with the original query value.

* Query value falls between possible values of the index type
(possible in float8->float4 or timestamp->date cases, for example).
We can just use our usual conversion rules, though. The critical
observation here is that it does not matter whether the conversion
rounds to the next lower or next higher possible value. If we are
searching for equality, neither of those values will pass the
cross-type comparison so it doesn't matter. If we are searching for
inequality, for example "indcol <= value", then only index entries
strictly less than the query value can match. Rounding down clearly
doesn't hurt, while rounding up at worst makes the search include
some index entries just larger than the query value, which will be
correctly rejected by the cross-type comparison.

So basically all I had to do was write a bunch of non-error-throwing
conversion routines and set up some boilerplate infrastructure.
Patch series attached --- it's rather long, but a lot of it is
new test cases.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/58782480-ab75-4416-a177-ccf91be288a9%40app.fastmail.com
[2] https://www.postgresql.org/message-id/flat/17079-c5edf57c47debc2c%40postgresql.org
[3] https://www.postgresql.org/message-id/flat/20170207150420.1409.58748%40wrigleys.postgresql.org
[4] https://www.postgresql.org/message-id/flat/20160415185902.22924.77993%40wrigleys.postgresql.org
[5] https://www.postgresql.org/message-id/flat/VisenaEmail.42.91df4628bdf7755c.1537e96e852%40tc7-visena

Attachment Content-Type Size
v1-0001-Preliminary-refactoring.patch text/x-diff 6.5 KB
v1-0002-Add-cross-type-comparisons-for-integer-types.patch text/x-diff 35.4 KB
v1-0003-Add-cross-type-comparisons-for-float-types.patch text/x-diff 11.6 KB
v1-0004-Add-cross-type-comparisons-for-string-types.patch text/x-diff 8.3 KB
v1-0005-Add-cross-type-comparisons-for-datetime-types.patch text/x-diff 35.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2025-02-02 05:15:25 Re: Add a function to get the version of installed extension
Previous Message Lars Kanis 2025-02-01 19:22:32 [PATCH] Fix build on MINGW on ARM64