Cross-type index comparison support in contrib/btree_gin
Tom Lane <tgl@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-02-02T01:44:12Z
Lists: pgsql-hackers
Attachments
- v1-0001-Preliminary-refactoring.patch (text/x-diff) patch v1-0001
- v1-0002-Add-cross-type-comparisons-for-integer-types.patch (text/x-diff) patch v1-0002
- v1-0003-Add-cross-type-comparisons-for-float-types.patch (text/x-diff) patch v1-0003
- v1-0004-Add-cross-type-comparisons-for-string-types.patch (text/x-diff) patch v1-0004
- v1-0005-Add-cross-type-comparisons-for-datetime-types.patch (text/x-diff) patch v1-0005
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
Commits
-
Add more cross-type comparisons to contrib/btree_gin.
- fc896821c444 19 (unreleased) landed
-
Add cross-type comparisons to contrib/btree_gin.
- e2b64fcef35f 19 (unreleased) landed
-
Break out xxx2yyy_opt_overflow APIs for more datetime conversions.
- 0059bbe1ecaa 19 (unreleased) landed