A case for GIST supporting ORDER BY

From: Michał Kłeczek <michal(at)kleczek(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: A case for GIST supporting ORDER BY
Date: 2023-10-24 11:22:53
Message-ID: EB2AF704-70FC-4D73-A97A-A7884A0381B5@kleczek.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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:
https://www.postgresql.org/message-id/3FA1E0A9-8393-41F6-88BD-62EEEA1EC21F%40kleczek.org
GIST index and ORDER BY
postgresql.org

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zhijie Hou (Fujitsu) 2023-10-24 11:31:29 RE: logical decoding and replication of sequences, take 2
Previous Message Dean Rasheed 2023-10-24 10:59:05 Re: Bug: RLS policy FOR SELECT is used to check new rows