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