[PATCH] Add support function for containment operators

From: Kim Johan Andersson <kimjand(at)kimmet(dot)dk>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: [PATCH] Add support function for containment operators
Date: 2023-04-29 15:07:19
Message-ID: 94f64d1f-b8c0-b0c5-98bc-0793a34e0851@kimmet.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I had noticed that performance wasn't great when using the @> or <@
operators when examining if an element is contained in a range.
Based on the discussion in [1] I would like to suggest the following
changes:

This patch attempts to improve the row estimation, as well as opening
the possibility of using a btree index scan when using the containment
operators.

This is done via a new support function handling the following 2 requests:

* SupportRequestIndexCondition
find_index_quals will build an operator clause, given at least one
finite RangeBound.

* SupportRequestSimplify
find_simplified_clause will rewrite the containment operator into a
clause using inequality operators from the btree family (if available
for the element type).

A boolean constant is returned if the range is either empty or has no
bounds.

Performing the rewrite here lets the clausesel machinery provide the
same estimates as for normal scalar inequalities.

In both cases build_bound_expr is used to build the operator clauses
from RangeBounds.

Thanks to Laurenz Albe for giving the patch a look before submission.

[1]
https://www.postgresql.org/message-id/222c75fd-43b8-db3e-74a6-bb4fe22f76db@kimmet.dk

Regards,
Kim Johan Andersson

Attachment Content-Type Size
range-support-v1.patch text/plain 30.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Anton Kirilov 2023-04-29 16:06:03 Re: Add PQsendSyncMessage() to libpq
Previous Message John Naylor 2023-04-29 08:17:35 Re: Overhauling "Routine Vacuuming" docs, particularly its handling of freezing