Hi All,
Attached is a first attempt to implement GIST index (only) scans for ORDER BY column clauses.
The idea is that it order by column for some datatypes is a special case of ordering by distance:
ORDER BY a == ORDER BY a <-> MIN_VALUE
and
ORDER BY a DESC == ORDER BY a <-> MAX_VALUE
This allows implementing GIST ordered scans for btree_gist datatypes.
This in turn makes using GIST with partitioning feasible (I have described issues with such usage in my previous e-mails - see below).
The solution is not ideal as it requires registering “<“ and “>” operators as ordering operators in opfamily
(which in turn makes it possible to issue somewhat meaningless “ORDER BY a < ‘constant’)
The problem is though that right now handling of ORDER BY column clauses is tightly coupled to BTree.
It would be good to refactor the code so that semantics of ORDER BY column could be more flexible.
It would be great if someone could take a look at it.
Thanks,
Michal
On 24 Oct 2023, at 13:22, Michał Kłeczek <michal@kleczek.org> wrote:
Hi,
Some time ago I’ve provided some details with the issues we face when trying to use GIST and partitioning at the same time in the postgresql-general mailing list:
We decided to go with the solution to partition our table by:
RANGE (‘2100-01-01' <-> operation_date).
While it (somewhat) solves partition pruning issues described above there is another problem:
It is impossible to create a unique constraint on the partitioned table.
So now we cannot use INSERT … ON CONFLICT (…) DO UPDATE
My question to hackers:
Would it be feasible to implement ORDER BY column GIST index (only) scan for types with total order and sensible greatest and least values?
Thanks,
Michal