public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 7/7] Move code to apply one WAL record to a subroutine.
7+ messages / 4 participants
[nested] [flat]
* [PATCH 7/7] Move code to apply one WAL record to a subroutine.
@ 2021-06-21 21:00 Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Heikki Linnakangas @ 2021-06-21 21:00 UTC (permalink / raw)
---
src/backend/access/transam/xlogrecovery.c | 283 +++++++++++-----------
1 file changed, 148 insertions(+), 135 deletions(-)
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 5e23244f6da..c78fc5273bd 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -366,6 +366,7 @@ static char recoveryStopName[MAXFNAMELEN];
static bool recoveryStopAfter;
/* prototypes for local functions */
+static void ApplyWalRecord(XLogReaderState *xlogreader, XLogRecord *record);
static void xlog_block_info(StringInfo buf, XLogReaderState *record);
static void readRecoverySignalFile(void);
@@ -1398,11 +1399,8 @@ PerformWalRecovery(void)
if (record != NULL)
{
- ErrorContextCallback errcallback;
TimestampTz xtime;
PGRUsage ru0;
- XLogRecPtr ReadRecPtr;
- XLogRecPtr EndRecPtr;
pg_rusage_init(&ru0);
@@ -1424,11 +1422,6 @@ PerformWalRecovery(void)
*/
do
{
- bool switchedTLI = false;
-
- ReadRecPtr = xlogreader->ReadRecPtr;
- EndRecPtr = xlogreader->EndRecPtr;
-
#ifdef WAL_DEBUG
if (XLOG_DEBUG ||
(rmid == RM_XACT_ID && trace_recovery_messages <= DEBUG2) ||
@@ -1438,8 +1431,8 @@ PerformWalRecovery(void)
initStringInfo(&buf);
appendStringInfo(&buf, "REDO @ %X/%X; LSN %X/%X: ",
- LSN_FORMAT_ARGS(ReadRecPtr),
- LSN_FORMAT_ARGS(EndRecPtr));
+ LSN_FORMAT_ARGS(xlogreader->ReadRecPtr),
+ LSN_FORMAT_ARGS(xlogreader->EndRecPtr));
xlog_outrec(&buf, xlogreader);
appendStringInfoString(&buf, " - ");
xlog_outdesc(&buf, xlogreader);
@@ -1494,132 +1487,10 @@ PerformWalRecovery(void)
recoveryPausesHere(false);
}
- /* Setup error traceback support for ereport() */
- errcallback.callback = rm_redo_error_callback;
- errcallback.arg = (void *) xlogreader;
- errcallback.previous = error_context_stack;
- error_context_stack = &errcallback;
-
/*
- * ShmemVariableCache->nextXid must be beyond record's xid.
+ * Apply the record
*/
- AdvanceNextFullTransactionIdPastXid(record->xl_xid);
-
- /*
- * Before replaying this record, check if this record causes the
- * current timeline to change. The record is already considered to
- * be part of the new timeline, so we update ThisTimeLineID before
- * replaying it. That's important so that replayEndTLI, which is
- * recorded as the minimum recovery point's TLI if recovery stops
- * after this record, is set correctly.
- */
- if (record->xl_rmid == RM_XLOG_ID)
- {
- TimeLineID newTLI = ThisTimeLineID;
- TimeLineID prevTLI = ThisTimeLineID;
- uint8 info = record->xl_info & ~XLR_INFO_MASK;
-
- if (info == XLOG_CHECKPOINT_SHUTDOWN)
- {
- CheckPoint checkPoint;
-
- memcpy(&checkPoint, XLogRecGetData(xlogreader), sizeof(CheckPoint));
- newTLI = checkPoint.ThisTimeLineID;
- prevTLI = checkPoint.PrevTimeLineID;
- }
- else if (info == XLOG_END_OF_RECOVERY)
- {
- xl_end_of_recovery xlrec;
-
- memcpy(&xlrec, XLogRecGetData(xlogreader), sizeof(xl_end_of_recovery));
- newTLI = xlrec.ThisTimeLineID;
- prevTLI = xlrec.PrevTimeLineID;
- }
-
- if (newTLI != ThisTimeLineID)
- {
- /* Check that it's OK to switch to this TLI */
- checkTimeLineSwitch(EndRecPtr, newTLI, prevTLI);
-
- /* Following WAL records should be run with new TLI */
- ThisTimeLineID = newTLI;
- switchedTLI = true;
- }
- }
-
- /*
- * Update shared replayEndRecPtr before replaying this record, so
- * that XLogFlush will update minRecoveryPoint correctly.
- */
- SpinLockAcquire(&XLogRecCtl->info_lck);
- XLogRecCtl->replayEndRecPtr = EndRecPtr;
- XLogRecCtl->replayEndTLI = ThisTimeLineID;
- SpinLockRelease(&XLogRecCtl->info_lck);
-
- /*
- * If we are attempting to enter Hot Standby mode, process XIDs we
- * see
- */
- if (standbyState >= STANDBY_INITIALIZED &&
- TransactionIdIsValid(record->xl_xid))
- RecordKnownAssignedTransactionIds(record->xl_xid);
-
- /* Now apply the WAL record itself */
- RmgrTable[record->xl_rmid].rm_redo(xlogreader);
-
- /*
- * After redo, check whether the backup pages associated with the
- * WAL record are consistent with the existing pages. This check
- * is done only if consistency check is enabled for this record.
- */
- if ((record->xl_info & XLR_CHECK_CONSISTENCY) != 0)
- checkXLogConsistency(xlogreader);
-
- /* Pop the error context stack */
- error_context_stack = errcallback.previous;
-
- /*
- * Update lastReplayedEndRecPtr after this record has been
- * successfully replayed.
- */
- SpinLockAcquire(&XLogRecCtl->info_lck);
- XLogRecCtl->lastReplayedEndRecPtr = EndRecPtr;
- XLogRecCtl->lastReplayedTLI = ThisTimeLineID;
- SpinLockRelease(&XLogRecCtl->info_lck);
-
- /* Also remember its starting position. */
- LastReplayedReadRecPtr = ReadRecPtr;
-
- /*
- * If rm_redo called XLogRequestWalReceiverReply, then we wake up
- * the receiver so that it notices the updated
- * lastReplayedEndRecPtr and sends a reply to the primary.
- */
- if (doRequestWalReceiverReply)
- {
- doRequestWalReceiverReply = false;
- WalRcvForceReply();
- }
-
- /* Allow read-only connections if we're consistent now */
- CheckRecoveryConsistency();
-
- /* Is this a timeline switch? */
- if (switchedTLI)
- {
- /*
- * Before we continue on the new timeline, clean up any
- * (possibly bogus) future WAL segments on the old timeline.
- */
- RemoveNonParentXlogFiles(EndRecPtr, ThisTimeLineID);
-
- /*
- * Wake up any walsenders to notice that we are on a new
- * timeline.
- */
- if (AllowCascadeReplication())
- WalSndWakeup();
- }
+ ApplyWalRecord(xlogreader, record);
/* Exit loop if we reached inclusive recovery target */
if (recoveryStopsAfter(xlogreader))
@@ -1678,7 +1549,7 @@ PerformWalRecovery(void)
ereport(LOG,
(errmsg("redo done at %X/%X system usage: %s",
- LSN_FORMAT_ARGS(ReadRecPtr),
+ LSN_FORMAT_ARGS(xlogreader->ReadRecPtr),
pg_rusage_show(&ru0))));
xtime = GetLatestXTime();
if (xtime)
@@ -1707,6 +1578,148 @@ PerformWalRecovery(void)
(errmsg("recovery ended before configured recovery target was reached")));
}
+/*
+ * Subroutine of PerformWalRecovery, to apply one WAL record.
+ */
+static void
+ApplyWalRecord(XLogReaderState *xlogreader, XLogRecord *record)
+{
+ XLogRecPtr ReadRecPtr;
+ XLogRecPtr EndRecPtr;
+ ErrorContextCallback errcallback;
+ bool switchedTLI = false;
+
+ ReadRecPtr = xlogreader->ReadRecPtr;
+ EndRecPtr = xlogreader->EndRecPtr;
+
+ /* Setup error traceback support for ereport() */
+ errcallback.callback = rm_redo_error_callback;
+ errcallback.arg = (void *) xlogreader;
+ errcallback.previous = error_context_stack;
+ error_context_stack = &errcallback;
+
+ /*
+ * ShmemVariableCache->nextXid must be beyond record's xid.
+ */
+ AdvanceNextFullTransactionIdPastXid(record->xl_xid);
+
+ /*
+ * Before replaying this record, check if this record causes the
+ * current timeline to change. The record is already considered to
+ * be part of the new timeline, so we update ThisTimeLineID before
+ * replaying it. That's important so that replayEndTLI, which is
+ * recorded as the minimum recovery point's TLI if recovery stops
+ * after this record, is set correctly.
+ */
+ if (record->xl_rmid == RM_XLOG_ID)
+ {
+ TimeLineID newTLI = ThisTimeLineID;
+ TimeLineID prevTLI = ThisTimeLineID;
+ uint8 info = record->xl_info & ~XLR_INFO_MASK;
+
+ if (info == XLOG_CHECKPOINT_SHUTDOWN)
+ {
+ CheckPoint checkPoint;
+
+ memcpy(&checkPoint, XLogRecGetData(xlogreader), sizeof(CheckPoint));
+ newTLI = checkPoint.ThisTimeLineID;
+ prevTLI = checkPoint.PrevTimeLineID;
+ }
+ else if (info == XLOG_END_OF_RECOVERY)
+ {
+ xl_end_of_recovery xlrec;
+
+ memcpy(&xlrec, XLogRecGetData(xlogreader), sizeof(xl_end_of_recovery));
+ newTLI = xlrec.ThisTimeLineID;
+ prevTLI = xlrec.PrevTimeLineID;
+ }
+
+ if (newTLI != ThisTimeLineID)
+ {
+ /* Check that it's OK to switch to this TLI */
+ checkTimeLineSwitch(EndRecPtr, newTLI, prevTLI);
+
+ /* Following WAL records should be run with new TLI */
+ ThisTimeLineID = newTLI;
+ switchedTLI = true;
+ }
+ }
+
+ /*
+ * Update shared replayEndRecPtr before replaying this record, so
+ * that XLogFlush will update minRecoveryPoint correctly.
+ */
+ SpinLockAcquire(&XLogRecCtl->info_lck);
+ XLogRecCtl->replayEndRecPtr = EndRecPtr;
+ XLogRecCtl->replayEndTLI = ThisTimeLineID;
+ SpinLockRelease(&XLogRecCtl->info_lck);
+
+ /*
+ * If we are attempting to enter Hot Standby mode, process XIDs we
+ * see
+ */
+ if (standbyState >= STANDBY_INITIALIZED &&
+ TransactionIdIsValid(record->xl_xid))
+ RecordKnownAssignedTransactionIds(record->xl_xid);
+
+ /* Now apply the WAL record itself */
+ RmgrTable[record->xl_rmid].rm_redo(xlogreader);
+
+ /*
+ * After redo, check whether the backup pages associated with the
+ * WAL record are consistent with the existing pages. This check
+ * is done only if consistency check is enabled for this record.
+ */
+ if ((record->xl_info & XLR_CHECK_CONSISTENCY) != 0)
+ checkXLogConsistency(xlogreader);
+
+ /* Pop the error context stack */
+ error_context_stack = errcallback.previous;
+
+ /*
+ * Update lastReplayedEndRecPtr after this record has been
+ * successfully replayed.
+ */
+ SpinLockAcquire(&XLogRecCtl->info_lck);
+ XLogRecCtl->lastReplayedEndRecPtr = EndRecPtr;
+ XLogRecCtl->lastReplayedTLI = ThisTimeLineID;
+ SpinLockRelease(&XLogRecCtl->info_lck);
+
+ /* Also remember its starting position. */
+ LastReplayedReadRecPtr = ReadRecPtr;
+
+ /*
+ * If rm_redo called XLogRequestWalReceiverReply, then we wake up
+ * the receiver so that it notices the updated
+ * lastReplayedEndRecPtr and sends a reply to the primary.
+ */
+ if (doRequestWalReceiverReply)
+ {
+ doRequestWalReceiverReply = false;
+ WalRcvForceReply();
+ }
+
+ /* Allow read-only connections if we're consistent now */
+ CheckRecoveryConsistency();
+
+ /* Is this a timeline switch? */
+ if (switchedTLI)
+ {
+ /*
+ * Before we continue on the new timeline, clean up any
+ * (possibly bogus) future WAL segments on the old timeline.
+ */
+ RemoveNonParentXlogFiles(EndRecPtr, ThisTimeLineID);
+
+ /*
+ * Wake up any walsenders to notice that we are on a new
+ * timeline.
+ */
+ if (AllowCascadeReplication())
+ WalSndWakeup();
+ }
+}
+
/*
* Error context callback for errors occurring during rm_redo().
*/
--
2.30.2
--------------8C95DA69A2E7D2FC9457CD47--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
@ 2023-07-07 11:20 Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-07-07 11:20 UTC (permalink / raw)
To: Kim Johan Andersson <[email protected]>; [email protected]
I wrote:
> You implement both "SupportRequestIndexCondition" and "SupportRequestSimplify",
> but when I experimented, the former was never called. That does not
> surprise me, since any expression of the shape "expr <@ range constant"
> can be simplified. Is the "SupportRequestIndexCondition" branch dead code?
> If not, do you have an example that triggers it?
I had an idea about this:
So far, you only consider constant ranges. But if we have a STABLE range
expression, you could use an index scan for "expr <@ range", for example
Index Cond (expr >= lower(range) AND expr < upper(range)).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
@ 2023-07-08 06:11 ` Kim Johan Andersson <[email protected]>
2023-08-01 02:07 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Kim Johan Andersson @ 2023-07-08 06:11 UTC (permalink / raw)
To: [email protected]
On 07-07-2023 13:20, Laurenz Albe wrote:
> I wrote:
>> You implement both "SupportRequestIndexCondition" and "SupportRequestSimplify",
>> but when I experimented, the former was never called. That does not
>> surprise me, since any expression of the shape "expr <@ range constant"
>> can be simplified. Is the "SupportRequestIndexCondition" branch dead code?
>> If not, do you have an example that triggers it?
I would think it is dead code, I came to the same conclusion. So we can
drop SupportRequestIndexCondition, since the simplification happens to
take care of everything.
> I had an idea about this:
> So far, you only consider constant ranges. But if we have a STABLE range
> expression, you could use an index scan for "expr <@ range", for example
> Index Cond (expr >= lower(range) AND expr < upper(range)).
>
I will try to look into this. Originally that was what I was hoping for,
but didn't see way of going about it.
Thanks for your comments, I will also look at the locale-related
breakage you spotted.
Regards,
Kimjand
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
@ 2023-08-01 02:07 ` Laurenz Albe <[email protected]>
2023-10-13 06:26 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-08-01 02:07 UTC (permalink / raw)
To: Kim Johan Andersson <[email protected]>; [email protected]
On Sat, 2023-07-08 at 08:11 +0200, Kim Johan Andersson wrote:
> On 07-07-2023 13:20, Laurenz Albe wrote:
> > I wrote:
> > > You implement both "SupportRequestIndexCondition" and "SupportRequestSimplify",
> > > but when I experimented, the former was never called. That does not
> > > surprise me, since any expression of the shape "expr <@ range constant"
> > > can be simplified. Is the "SupportRequestIndexCondition" branch dead code?
> > > If not, do you have an example that triggers it?
>
> I would think it is dead code, I came to the same conclusion. So we can
> drop SupportRequestIndexCondition, since the simplification happens to
> take care of everything.
>
>
> > I had an idea about this:
> > So far, you only consider constant ranges. But if we have a STABLE range
> > expression, you could use an index scan for "expr <@ range", for example
> > Index Cond (expr >= lower(range) AND expr < upper(range)).
> >
>
> I will try to look into this. Originally that was what I was hoping for,
> but didn't see way of going about it.
>
> Thanks for your comments, I will also look at the locale-related
> breakage you spotted.
I have marked the patch as "returned with feedback".
I encourage you to submit an improved version in a future commitfest!
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
2023-08-01 02:07 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
@ 2023-10-13 06:26 ` jian he <[email protected]>
2023-10-19 16:01 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: jian he @ 2023-10-13 06:26 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Kim Johan Andersson <[email protected]>; [email protected]
On Tue, Aug 1, 2023 at 10:07 AM Laurenz Albe <[email protected]> wrote:
> >
> >
> > > I had an idea about this:
> > > So far, you only consider constant ranges. But if we have a STABLE range
> > > expression, you could use an index scan for "expr <@ range", for example
> > > Index Cond (expr >= lower(range) AND expr < upper(range)).
> > >
The above part, not sure how to implement it, not sure it is doable.
Refactor:
drop SupportRequestIndexCondition and related code, since mentioned in
upthread, it's dead code.
refactor the regression test. (since data types with infinity cover
more cases than int4range, so I deleted some tests).
now there are 3 helper functions (build_bound_expr,
find_simplified_clause, match_support_request), 2 entry functions
(elem_contained_by_range_support, range_contains_elem_support)
Collation problem seems solved. Putting the following test on the
src/test/regress/sql/rangetypes.sql will not work. Maybe because of
the order of the regression test, in SQL-ASCII encoding, I cannot
create collation="cs-CZ-x-icu".
drop type if EXISTS textrange1, textrange2;
drop table if EXISTS collate_test1, collate_test2;
CREATE TYPE textrange1 AS RANGE (SUBTYPE = text, collation="C");
create type textrange2 as range(subtype=text, collation="cs-CZ-x-icu");
CREATE TABLE collate_test1 (b text COLLATE "en-x-icu" NOT NULL);
INSERT INTO collate_test1(b) VALUES ('a'), ('c'), ('d'), ('ch');
CREATE TABLE collate_test2 (b text NOT NULL);
INSERT INTO collate_test2(b) VALUES ('a'), ('c'), ('d'), ('ch');
--should include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange2('a', 'd');
--should include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange2('a', 'd');
-----------------
Attachments:
[text/x-patch] v2-0001-Optimize-qual-cases-like-Expr-RangeConst-and-Rang.patch (22.2K, ../../CACJufxGmodA7Uc+Y4wE-A95BHLamJ3YxDtRYbXjNF=b8xExz+w@mail.gmail.com/2-v2-0001-Optimize-qual-cases-like-Expr-RangeConst-and-Rang.patch)
download | inline diff:
From bcae7f8a0640b48b04f243660539e8670de43d41 Mon Sep 17 00:00:00 2001
From: pgaddict <[email protected]>
Date: Fri, 13 Oct 2023 12:43:41 +0800
Subject: [PATCH v2 1/1] Optimize qual cases like Expr <@ RangeConst and
RangeConst @> Expr
Previously these two quals will be processed as is.
With this patch, adding prosupport
function to range_contains_elem, elem_contained_by_range.
So cases like Expr <@ rangeCOnst, rangeConst @> expr
will be rewritten to "expr opr range_lower_bound and expr opr
range_upper_bound".
Expr <@ rangeConst will be logically equivalent to rangeConst @> Expr,
if rangeConst is the same. added some tests to validate the generated
plan.
---
src/backend/utils/adt/rangetypes.c | 199 +++++++++++++++++++-
src/backend/utils/adt/rangetypes_selfuncs.c | 6 +-
src/include/catalog/pg_proc.dat | 12 +-
src/test/regress/expected/rangetypes.out | 194 +++++++++++++++++++
src/test/regress/sql/rangetypes.sql | 101 ++++++++++
5 files changed, 505 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625..647bd5e4 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,13 +31,19 @@
#include "postgres.h"
#include "access/tupmacs.h"
+#include "access/stratnum.h"
#include "common/hashfn.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/supportnodes.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pg_list.h"
#include "nodes/miscnodes.h"
#include "port/pg_bitutils.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
#include "utils/rangetypes.h"
@@ -69,7 +75,11 @@ static Size datum_compute_size(Size data_length, Datum val, bool typbyval,
char typalign, int16 typlen, char typstorage);
static Pointer datum_write(Pointer ptr, Datum datum, bool typbyval,
char typalign, int16 typlen, char typstorage);
-
+static Expr *build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation);
+static Node *find_simplified_clause(Const *rangeConst, Expr *otherExpr);
+static Node *match_support_request(Node *rawreq);
/*
*----------------------------------------------------------
@@ -558,7 +568,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
-
/* range, range -> bool functions */
/* equality (internal version) */
@@ -2173,6 +2182,29 @@ make_empty_range(TypeCacheEntry *typcache)
return make_range(typcache, &lower, &upper, true, NULL);
}
+/*
+ * Planner support function for elem_contained_by_range operator
+ */
+Datum
+elem_contained_by_range_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
+
+/*
+ * Planner support function for range_contains_elem operator
+ */
+Datum
+range_contains_elem_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
/*
*----------------------------------------------------------
@@ -2714,3 +2746,166 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
return ptr;
}
+/*
+ * build (Expr Operator Range_bound) expression. something like: expr >= lower(range)
+ * if operator both sides have collation, operator should use (right) range's collation
+ *
+*/
+static Expr *
+build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation)
+{
+ Oid elemType = typeCache->type_id;
+ int16 elemTypeLen = typeCache->typlen;
+ bool elemByValue = typeCache->typbyval;
+ Oid elemCollation = typeCache->typcollation;
+ int16 strategy;
+ Oid oproid;
+ Expr *constExpr;
+
+ if (isLowerBound)
+ strategy = isInclusive ? BTGreaterEqualStrategyNumber : BTGreaterStrategyNumber;
+ else
+ strategy = isInclusive ? BTLessEqualStrategyNumber : BTLessStrategyNumber;
+
+ oproid = get_opfamily_member(opfamily, elemType, elemType, strategy);
+
+ if (!OidIsValid(oproid))
+ return NULL;
+
+ constExpr = (Expr *) makeConst(elemType,
+ -1,
+ elemCollation,
+ elemTypeLen,
+ val,
+ false,
+ elemByValue);
+
+ return make_opclause(oproid,
+ BOOLOID,
+ false,
+ otherExpr,
+ constExpr,
+ InvalidOid,
+ rng_collation
+ );
+}
+
+/*
+ * Supports both the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM cases.
+ *
+ */
+static Node *
+find_simplified_clause(Const *rangeConst, Expr *otherExpr)
+{
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache = lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+ Oid rng_collation;
+
+ range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+
+ rng_collation = rangetypcache->rng_collation;
+
+ if (empty)
+ {
+ /* If the range is empty, then there can be no matches. */
+ return makeBoolConst(false, false);
+ }
+ else if (lower.infinite && upper.infinite)
+ {
+ /* The range has no bounds, so matches everything. */
+ return makeBoolConst(true, false);
+ }
+ else
+ {
+ /* At least one bound is available, we have something to work with. */
+ TypeCacheEntry *elemTypcache = lookup_type_cache(rangetypcache->rngelemtype->type_id, TYPECACHE_BTREE_OPFAMILY);
+ Expr *lowerExpr = NULL;
+ Expr *upperExpr = NULL;
+
+ /* There might not be an operator family available for this element */
+ if (!OidIsValid(elemTypcache->btree_opf))
+ return NULL;
+
+ if (!lower.infinite)
+ {
+ lowerExpr = build_bound_expr(elemTypcache->btree_opf,
+ elemTypcache,
+ true,
+ lower.inclusive,
+ lower.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (!upper.infinite)
+ {
+ upperExpr = build_bound_expr(elemTypcache->btree_opf,
+ elemTypcache,
+ false,
+ upper.inclusive,
+ upper.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (lowerExpr != NULL && upperExpr != NULL)
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(lowerExpr, upperExpr), -1);
+ else if (lowerExpr != NULL)
+ return (Node *) lowerExpr;
+ else if (upperExpr != NULL)
+ return (Node *) upperExpr;
+ }
+
+ return NULL;
+}
+
+static Node *
+match_support_request(Node *rawreq)
+{
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *fexpr = req->fcall;
+ Node *leftop;
+ Node *rightop;
+ Const *rangeConst;
+ Expr *otherExpr;
+
+ Assert(list_length(fexpr->args) == 2);
+
+ leftop = linitial(fexpr->args);
+ rightop = lsecond(fexpr->args);
+
+ switch (fexpr->funcid)
+ {
+ case F_ELEM_CONTAINED_BY_RANGE:
+ if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) rightop;
+ otherExpr = (Expr *) leftop;
+ break;
+
+ case F_RANGE_CONTAINS_ELEM:
+ if (!IsA(leftop, Const) || ((Const *) leftop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) leftop;
+ otherExpr = (Expr *) rightop;
+ break;
+
+ default:
+ return NULL;
+ }
+
+ return find_simplified_clause(rangeConst, otherExpr);
+ }
+ return NULL;
+}
\ No newline at end of file
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e1..7c4cf0ae 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -196,9 +196,9 @@ rangesel(PG_FUNCTION_ARGS)
else if (operator == OID_RANGE_ELEM_CONTAINED_OP)
{
/*
- * Here, the Var is the elem, not the range. For now we just punt and
- * return the default estimate. In future we could disassemble the
- * range constant and apply scalarineqsel ...
+ * Here, the Var is the elem, not the range.
+ * The support function in rangetypes.c should have simplified this case,
+ * enabling the clausesel.c machinery to handle it.
*/
}
else if (((Const *) other)->consttype == vardata.vartype)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f0b7b9cb..3777ce4a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10468,13 +10468,15 @@
proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
{ oid => '3858',
proname => 'range_contains_elem', prorettype => 'bool',
- proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem' },
+ proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem',
+ prosupport => 'range_contains_elem_support' },
{ oid => '3859',
proname => 'range_contains', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contains' },
{ oid => '3860',
proname => 'elem_contained_by_range', prorettype => 'bool',
- proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range' },
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range',
+ prosupport => 'elem_contained_by_range_support' },
{ oid => '3861',
proname => 'range_contained_by', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contained_by' },
@@ -10496,6 +10498,12 @@
{ oid => '3867',
proname => 'range_union', prorettype => 'anyrange',
proargtypes => 'anyrange anyrange', prosrc => 'range_union' },
+{ oid => '9998', descr => 'Planner support function for range_contains_elem operator',
+ proname => 'range_contains_elem_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'range_contains_elem_support' },
+{ oid => '9999', descr => 'Planner support function for elem_contained_by_range operator',
+ proname => 'elem_contained_by_range_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'elem_contained_by_range_support' },
{ oid => '4057',
descr => 'the smallest range which includes both of the given ranges',
proname => 'range_merge', prorettype => 'anyrange',
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff01..02f3f29e 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,197 @@ create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
ERROR: cannot determine result data type
DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange.
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date >= '01-01-2000'::date)
+(2 rows)
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date < '01-02-2000'::date)
+(2 rows)
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+ QUERY PLAN
+-------------------------------
+ Seq Scan on date_support_test
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date > '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ count
+-------
+ 1000
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ count
+-------
+ 1001
+(1 row)
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-02-2000'::date))
+(3 rows)
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ count
+-------
+ 1002
+(1 row)
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+ count
+-------
+ 1001
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '01-01-2000'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ count
+-------
+ 1002
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using date_support_test_some_date_idx on date_support_test
+ Index Cond: ((some_date >= 'infinity'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ count
+-------
+ 1
+(1 row)
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ QUERY PLAN
+--------------------------------
+ Aggregate
+ -> Result
+ One-Time Filter: false
+(3 rows)
+
+DROP TABLE date_support_test;
+drop function check2plan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928..7d6d1799 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,104 @@ create function inoutparam_fail(inout i anyelement, out r anyrange)
--should fail
create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
+
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+DROP TABLE date_support_test;
+drop function check2plan;
\ No newline at end of file
--
2.34.1
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
2023-08-01 02:07 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-10-13 06:26 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
@ 2023-10-19 16:01 ` Laurenz Albe <[email protected]>
2023-10-20 08:24 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-10-19 16:01 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Kim Johan Andersson <[email protected]>; [email protected]
On Fri, 2023-10-13 at 14:26 +0800, jian he wrote:
> Collation problem seems solved.
I didn't review your patch in detail, there is still a problem
with my example:
CREATE TYPE textrange AS RANGE (
SUBTYPE = text,
SUBTYPE_OPCLASS = text_pattern_ops
);
CREATE TABLE tx (t text COLLATE "cs-CZ-x-icu");
INSERT INTO tx VALUES ('a'), ('c'), ('d'), ('ch');
SELECT * FROM tx WHERE t <@ textrange('a', 'd');
t
════
a
c
ch
(3 rows)
That was correct.
EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');
QUERY PLAN
════════════════════════════════════════════════════
Seq Scan on tx (cost=0.00..30.40 rows=7 width=32)
Filter: ((t >= 'a'::text) AND (t < 'd'::text))
(2 rows)
But that was weird. The operators seem wrong. Look at that
query:
SELECT * FROM tx WHERE t >= 'a' AND t < 'd';
t
═══
a
c
(2 rows)
But the execution plan is identical...
I am not sure what is the problem here, but in my opinion the
operators shown in the execution plan should be like this:
SELECT * FROM tx WHERE t ~>=~ 'a' AND t ~<~ 'd';
t
════
a
c
ch
(3 rows)
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add support function for containment operators
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
2023-08-01 02:07 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-10-13 06:26 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
2023-10-19 16:01 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
@ 2023-10-20 08:24 ` jian he <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: jian he @ 2023-10-20 08:24 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Kim Johan Andersson <[email protected]>; [email protected]
On Fri, Oct 20, 2023 at 12:01 AM Laurenz Albe <[email protected]> wrote:
>
> On Fri, 2023-10-13 at 14:26 +0800, jian he wrote:
> > Collation problem seems solved.
>
> I didn't review your patch in detail, there is still a problem
> with my example:
>
> CREATE TYPE textrange AS RANGE (
> SUBTYPE = text,
> SUBTYPE_OPCLASS = text_pattern_ops
> );
>
> CREATE TABLE tx (t text COLLATE "cs-CZ-x-icu");
>
> INSERT INTO tx VALUES ('a'), ('c'), ('d'), ('ch');
>
> SELECT * FROM tx WHERE t <@ textrange('a', 'd');
>
> t
> ════
> a
> c
> ch
> (3 rows)
>
> That was correct.
>
> EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');
>
> QUERY PLAN
> ════════════════════════════════════════════════════
> Seq Scan on tx (cost=0.00..30.40 rows=7 width=32)
> Filter: ((t >= 'a'::text) AND (t < 'd'::text))
> (2 rows)
>
> But that was weird. The operators seem wrong. Look at that
Thanks for pointing this out!
The problem is that TypeCacheEntry->rngelemtype typcaheentry don't
have the range's SUBTYPE_OPCLASS info.
So in find_simplified_clause, we need to get the range's
SUBTYPE_OPCLASS from the pg_catalog table.
Also in pg_range, column rngsubopc is not null. so this should be fine.
Attachments:
[text/x-patch] v3-0001-Optimize-quals-Expr-RangeConst-and-RangeConst-Exp.patch (23.1K, ../../CACJufxHHyaTMxRJB3ZepvtcnGJUqT-TAieGt6p=Zi70G6t5bqw@mail.gmail.com/2-v3-0001-Optimize-quals-Expr-RangeConst-and-RangeConst-Exp.patch)
download | inline diff:
From 810208a42e99109bcaf56cddae6968efbcf969c5 Mon Sep 17 00:00:00 2001
From: pgaddict <[email protected]>
Date: Fri, 20 Oct 2023 16:20:38 +0800
Subject: [PATCH v3 1/1] Optimize quals (Expr <@ RangeConst) and (RangeConst
@> Expr)
Previously these two quals will be processed as is.
This patch will rewritten the expression to expose more info to optimizer by
adding prosupport function to range_contains_elem, elem_contained_by_range.
Expr <@ rangeConst will be rewritten to "expr opr range_lower_bound and expr opr
range_upper_bound". (range bound inclusiveness will be handled properly).
Added some tests to validate the generated plan.
---
src/backend/utils/adt/rangetypes.c | 229 +++++++++++++++++++-
src/backend/utils/adt/rangetypes_selfuncs.c | 6 +-
src/include/catalog/pg_proc.dat | 12 +-
src/test/regress/expected/rangetypes.out | 194 +++++++++++++++++
src/test/regress/sql/rangetypes.sql | 101 +++++++++
5 files changed, 535 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625..d100e55e 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,16 +31,24 @@
#include "postgres.h"
#include "access/tupmacs.h"
+#include "access/stratnum.h"
+#include "catalog/pg_range.h"
#include "common/hashfn.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/miscnodes.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pg_list.h"
+#include "nodes/supportnodes.h"
#include "port/pg_bitutils.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
#include "utils/rangetypes.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "varatt.h"
@@ -69,7 +77,11 @@ static Size datum_compute_size(Size data_length, Datum val, bool typbyval,
char typalign, int16 typlen, char typstorage);
static Pointer datum_write(Pointer ptr, Datum datum, bool typbyval,
char typalign, int16 typlen, char typstorage);
-
+static Expr *build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation);
+static Node *find_simplified_clause(Const *rangeConst, Expr *otherExpr);
+static Node *match_support_request(Node *rawreq);
/*
*----------------------------------------------------------
@@ -558,7 +570,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
-
/* range, range -> bool functions */
/* equality (internal version) */
@@ -2173,6 +2184,29 @@ make_empty_range(TypeCacheEntry *typcache)
return make_range(typcache, &lower, &upper, true, NULL);
}
+/*
+ * Planner support function for elem_contained_by_range operator
+ */
+Datum
+elem_contained_by_range_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
+
+/*
+ * Planner support function for range_contains_elem operator
+ */
+Datum
+range_contains_elem_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
/*
*----------------------------------------------------------
@@ -2714,3 +2748,194 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
return ptr;
}
+/*
+ * build (Expr Operator Range_bound) expression. something like: expr >= lower(range)
+ * if operator both sides have collation, operator should use (right) range's collation
+ *
+*/
+static Expr *
+build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation)
+{
+ Oid elemType = typeCache->type_id;
+ int16 elemTypeLen = typeCache->typlen;
+ bool elemByValue = typeCache->typbyval;
+ Oid elemCollation = typeCache->typcollation;
+ int16 strategy;
+ Oid oproid;
+ Expr *constExpr;
+
+ if (isLowerBound)
+ strategy = isInclusive ? BTGreaterEqualStrategyNumber : BTGreaterStrategyNumber;
+ else
+ strategy = isInclusive ? BTLessEqualStrategyNumber : BTLessStrategyNumber;
+
+ oproid = get_opfamily_member(opfamily, elemType, elemType, strategy);
+
+ if (!OidIsValid(oproid))
+ return NULL;
+
+ constExpr = (Expr *) makeConst(elemType,
+ -1,
+ elemCollation,
+ elemTypeLen,
+ val,
+ false,
+ elemByValue);
+
+ return make_opclause(oproid,
+ BOOLOID,
+ false,
+ otherExpr,
+ constExpr,
+ InvalidOid,
+ rng_collation
+ );
+}
+
+/*
+ * Supports both the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM cases.
+ *
+ */
+static Node *
+find_simplified_clause(Const *rangeConst, Expr *otherExpr)
+{
+ Form_pg_range pg_range;
+ HeapTuple tup;
+ Oid opclassOid;
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+ Oid rng_collation;
+ TypeCacheEntry *elemTypcache;
+ Oid opfamily = InvalidOid;
+
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache = lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ {
+ /* this part is get the range's SUBTYPE_OPCLASS from pg_range catalog.
+ * Refer load_rangetype_info function last line.
+ * TypeCacheEntry->rngelemtype typcaheenetry either don't have opclass entry or with default opclass.
+ * Range's subtype opclass only in catalog table.
+ */
+ tup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(RangeTypeGetOid(range)));
+
+ /* should not fail, since we already checked typtype ... */
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for range type %u", RangeTypeGetOid(range));
+
+ pg_range = (Form_pg_range) GETSTRUCT(tup);
+
+ opclassOid = pg_range->rngsubopc;
+
+ ReleaseSysCache(tup);
+
+ /* get opclass properties and look up the comparison function */
+ opfamily = get_opclass_family(opclassOid);
+ }
+
+ range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+ rng_collation = rangetypcache->rng_collation;
+
+ if (empty)
+ {
+ /* If the range is empty, then there can be no matches. */
+ return makeBoolConst(false, false);
+ }
+ else if (lower.infinite && upper.infinite)
+ {
+ /* The range has no bounds, so matches everything. */
+ return makeBoolConst(true, false);
+ }
+ else
+ {
+ /* At least one bound is available, we have something to work with. */
+ Expr *lowerExpr = NULL;
+ Expr *upperExpr = NULL;
+
+ /* There might not be an operator family available for this element */
+ if (!OidIsValid(opfamily))
+ return NULL;
+
+ /* flags set to 0 should be fine. Since already get the opfamily */
+ elemTypcache = lookup_type_cache(rangetypcache->rngelemtype->type_id, 0);
+
+ if (!lower.infinite)
+ {
+ lowerExpr = build_bound_expr(opfamily,
+ elemTypcache,
+ true,
+ lower.inclusive,
+ lower.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (!upper.infinite)
+ {
+ upperExpr = build_bound_expr(opfamily,
+ elemTypcache,
+ false,
+ upper.inclusive,
+ upper.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (lowerExpr != NULL && upperExpr != NULL)
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(lowerExpr, upperExpr), -1);
+ else if (lowerExpr != NULL)
+ return (Node *) lowerExpr;
+ else if (upperExpr != NULL)
+ return (Node *) upperExpr;
+ }
+
+ return NULL;
+}
+
+static Node *
+match_support_request(Node *rawreq)
+{
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *fexpr = req->fcall;
+ Node *leftop;
+ Node *rightop;
+ Const *rangeConst;
+ Expr *otherExpr;
+
+ Assert(list_length(fexpr->args) == 2);
+
+ leftop = linitial(fexpr->args);
+ rightop = lsecond(fexpr->args);
+
+ switch (fexpr->funcid)
+ {
+ case F_ELEM_CONTAINED_BY_RANGE:
+ if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) rightop;
+ otherExpr = (Expr *) leftop;
+ break;
+
+ case F_RANGE_CONTAINS_ELEM:
+ if (!IsA(leftop, Const) || ((Const *) leftop)->constisnull)
+ return NULL;
+
+ rangeConst = (Const *) leftop;
+ otherExpr = (Expr *) rightop;
+ break;
+
+ default:
+ return NULL;
+ }
+
+ return find_simplified_clause(rangeConst, otherExpr);
+ }
+ return NULL;
+}
\ No newline at end of file
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e1..7c4cf0ae 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -196,9 +196,9 @@ rangesel(PG_FUNCTION_ARGS)
else if (operator == OID_RANGE_ELEM_CONTAINED_OP)
{
/*
- * Here, the Var is the elem, not the range. For now we just punt and
- * return the default estimate. In future we could disassemble the
- * range constant and apply scalarineqsel ...
+ * Here, the Var is the elem, not the range.
+ * The support function in rangetypes.c should have simplified this case,
+ * enabling the clausesel.c machinery to handle it.
*/
}
else if (((Const *) other)->consttype == vardata.vartype)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c92d0631..6475bec6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10477,13 +10477,15 @@
proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
{ oid => '3858',
proname => 'range_contains_elem', prorettype => 'bool',
- proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem' },
+ proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem',
+ prosupport => 'range_contains_elem_support' },
{ oid => '3859',
proname => 'range_contains', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contains' },
{ oid => '3860',
proname => 'elem_contained_by_range', prorettype => 'bool',
- proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range' },
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range',
+ prosupport => 'elem_contained_by_range_support' },
{ oid => '3861',
proname => 'range_contained_by', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contained_by' },
@@ -10505,6 +10507,12 @@
{ oid => '3867',
proname => 'range_union', prorettype => 'anyrange',
proargtypes => 'anyrange anyrange', prosrc => 'range_union' },
+{ oid => '9998', descr => 'Planner support function for range_contains_elem operator',
+ proname => 'range_contains_elem_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'range_contains_elem_support' },
+{ oid => '9999', descr => 'Planner support function for elem_contained_by_range operator',
+ proname => 'elem_contained_by_range_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'elem_contained_by_range_support' },
{ oid => '4057',
descr => 'the smallest range which includes both of the given ranges',
proname => 'range_merge', prorettype => 'anyrange',
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff01..02f3f29e 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,197 @@ create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
ERROR: cannot determine result data type
DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange.
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date >= '01-01-2000'::date)
+(2 rows)
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date < '01-02-2000'::date)
+(2 rows)
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+ QUERY PLAN
+-------------------------------
+ Seq Scan on date_support_test
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date > '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ count
+-------
+ 1000
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ count
+-------
+ 1001
+(1 row)
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-02-2000'::date))
+(3 rows)
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ count
+-------
+ 1002
+(1 row)
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+ count
+-------
+ 1001
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '01-01-2000'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ count
+-------
+ 1002
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using date_support_test_some_date_idx on date_support_test
+ Index Cond: ((some_date >= 'infinity'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ count
+-------
+ 1
+(1 row)
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ QUERY PLAN
+--------------------------------
+ Aggregate
+ -> Result
+ One-Time Filter: false
+(3 rows)
+
+DROP TABLE date_support_test;
+drop function check2plan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928..7d6d1799 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,104 @@ create function inoutparam_fail(inout i anyelement, out r anyrange)
--should fail
create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
+
+--
+-- Test support function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+
+-- Only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[]');
+
+-- Only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL,'2000-01-01', '[]');
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+DROP TABLE date_support_test;
+drop function check2plan;
\ No newline at end of file
--
2.34.1
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-10-20 08:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-21 21:00 [PATCH 7/7] Move code to apply one WAL record to a subroutine. Heikki Linnakangas <[email protected]>
2023-07-07 11:20 Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-07-08 06:11 ` Re: [PATCH] Add support function for containment operators Kim Johan Andersson <[email protected]>
2023-08-01 02:07 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-10-13 06:26 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
2023-10-19 16:01 ` Re: [PATCH] Add support function for containment operators Laurenz Albe <[email protected]>
2023-10-20 08:24 ` Re: [PATCH] Add support function for containment operators jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox