Date: Sat, 2 May 2026 19:26:43 +0200
From: Alexander Nestorov <alexandernst@gmail.com>
To: pgsql-hackers@postgresql.org
Message-ID: <36b4f67d-5975-452c-a6b8-b6407f0924ee@Spark>
In-Reply-To: <aac10ffa-a0ca-4c49-846b-3655cbc6b37e@Spark>
References: <aac10ffa-a0ca-4c49-846b-3655cbc6b37e@Spark>
Subject: [PATCH] btree_gist: add cross-type integer operator support for
 GiST
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="69f633d8_ded7263_b38c"
Archived-At: 
 <https://www.postgresql.org/message-id/36b4f67d-5975-452c-a6b8-b6407f0924ee%40Spark>
Precedence: bulk

--69f633d8_ded7263_b38c
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello hackers,

I'd like to submit a patch that adds cross-type operator support for the
three integer types (int2, int4, int8) to the btree=5Fgist GiST operator
families. The patch also lays a general foundation for cross-type
dispatch that other type families can adopt.


Current problem:

GiST indexes are currently limited to same-type operators: the planner
can only match a query condition against an index column if the
operator's left and right argument types exactly match the types
registered in the index's operator family (pg=5Famop). This is true for
every data type that backs a GiST opclass.

When a query provides a value whose type is compatible but different
from the column type, the planner cannot use the index for that column.
The operator family lookup fails because no cross-type entry exists.
The planner must then either fall back to a sequential scan,
or in multi-column indexes use only the remaining column(s) and filter
the rest as a post-filter.

As a specific example, consider an int8 (bigint) column indexed with
btree=5Fgist. The query:

=C2=A0=C2=A0 =C2=A0SELECT * =46ROM t WHERE camera=5Fid =3D 1189;

Here 1189 is evaluated as int4 (integer). The planner resolves the
operator to =3D(int8,int4), which exists in pg=5Foperator but is not
registered in the gist=5Fint8=5Fops family. Only =3D(int8,int8) is
registered, so the column cannot be used as an index condition.

The workaround is to write an explicit cast in every query:

=C2=A0=C2=A0 =C2=A0WHERE camera=5Fid =3D 1189::int8

This is fragile as ORMs, application parameter binding, and even hand-
written SQL queries produce values whose types do not exactly match
the column type.

A couple of self-contained reproduction scripts are included at the
end of this email. They create a table with a GiST index, insert
enough rows to make the plan difference visible, and run
EXPLAIN ANALYZE.


Proposed solution:

I'm working on a patch that extends the three btree=5Fgist integer
operator families (gist=5Fint2=5Fops, gist=5Fint4=5Fops, gist=5Fint8=5Fop=
s)
with cross-type comparison and KNN-distance operators covering the
other two integer types.=C2=A0=C2=A0Concretely:

=C2=A0=C2=A0 =C2=A0gist=5Fint2=5Fops=C2=A0 =C2=A0<-=C2=A0=C2=A0operators =
for (int2, int4) and (int2, int8)
=C2=A0=C2=A0 =C2=A0gist=5Fint4=5Fops=C2=A0 =C2=A0<-=C2=A0=C2=A0operators =
for (int4, int2) and (int4, int8)
=C2=A0=C2=A0 =C2=A0gist=5Fint8=5Fops=C2=A0 =C2=A0<-=C2=A0=C2=A0operators =
for (int8, int2) and (int8, int4)

=46or each pair I add all the standard btree=5Fgist strategies for the
comparison operators (<, <=3D, =3D, >=3D, >, <>), plus the strategy for
the KNN distance operator (<->) used by ORDER BY.

I propose not to register separate cross-type support functions in
the operator family. GiST's amvalidate requires every support
function to have a matching left and right type, and registering
18 additional support functions (three families x two subtypes x
three strategies) would be verbose and error-prone. Instead, I
propose to dispatch cross-type queries directly inside the existing
consistent and distance functions and use the existing subtype
OID argument.

I'm thinking of introducing a general-purpose cross-type dispatch
table in btree=5Futils=5Fnum:

=C2=A0=C2=A0 =C2=A0typedef struct gbt=5Fsubtype=5Finfo
=C2=A0=C2=A0 =C2=A0=7B
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0Oid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sub=
type;=C2=A0 =C2=A0 =C2=A0/* right-hand Oid, e.g. INT4OID */
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0gbt=5Fcmp=5Ffn=C2=A0=C2=A0lt, le, eq, ge=
, gt;=C2=A0 =C2=A0/* comparison callbacks */
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0gbt=5Fdist=5Ffn dist;=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0/* KNN distance callback */
=C2=A0=C2=A0 =C2=A0=7D gbt=5Fsubtype=5Finfo;

Each integer opclass defines a static array of these entries:

=C2=A0=C2=A0 =C2=A0static const gbt=5Fsubtype=5Finfo gbt=5Fint2=5Fsubtype=
=5Fops=5B=5D =3D =7B
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0=7BINT4OID,=C2=A0=C2=A0... 6 comparison =
fns ... ,=C2=A0=C2=A0distance fn=7D,
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0=7BINT8OID,=C2=A0=C2=A0... 6 comparison =
fns ... ,=C2=A0=C2=A0distance fn=7D,
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0=7BInvalidOid=7D
=C2=A0=C2=A0 =C2=A0=7D;

I'll replace the existing gbt=5Fnum=5Fconsistent() function with
gbt=5Fnum=5Fconsistent=5Fx(), which takes a Datum query value and a
subtype Oid. If the subtype is InvalidOid or matches the indexed
type, the same-type path is used (backward compatible). Otherwise,
the function walks the dispatch table, finds the matching subtype
entry, and invokes the corresponding cross-type comparison or
distance callback.

Other btree=5Fgist opclasses (float4/float8, date, timestamp, ...) and
even range-type GiST opfamilies in core can adopt the same pattern by
defining their own subtype dispatch tables and registering cross-type
operators via ALTER OPERATOR =46AMILY.

I don't plan adding cross-type support for every data type as that
would result in a very bulky patch difficult to review, but I think
my proposal establishes the infrastructure so that follow-up work
for additional types is straightforward.

Is there interest in this patch=3F Should I proceed with polishing my
patch and sending it here for a review=3F I appreciate any feedback=21

Thank you


Simple repro example:


DROP TABLE I=46 EXISTS camera=5Ffeeds=5Fsimple CASCADE;

CREATE TABLE camera=5Ffeeds=5Fsimple (
=C2=A0=C2=A0 =C2=A0id=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SERIAL PRIMA=
RY KEY,
=C2=A0=C2=A0 =C2=A0camera=5Fid=C2=A0 =C2=A0int8 NOT NULL
);

CREATE EXTENSION I=46 NOT EXISTS btree=5Fgist;

CREATE INDEX idx=5Fcamera=5Ffeeds=5Fgist
=C2=A0=C2=A0 =C2=A0ON camera=5Ffeeds=5Fsimple
=C2=A0=C2=A0 =C2=A0USING GIST (camera=5Fid);

SET enable=5Fseqscan =3D O=46=46;
SET enable=5Fbitmapscan =3D ON;

INSERT INTO camera=5Ffeeds=5Fsimple (camera=5Fid) VALUES
=C2=A0=C2=A0 =C2=A0(1), (1), (1), (1), (2), (2), (2), (2), (3), (3), (3),=

=C2=A0=C2=A0 =C2=A0(4), (4), (4), (4), (4), (4), (4), (4), (5), (5), (6),=
 (6);

ANALYZE camera=5Ffeeds=5Fsimple;

=5Cecho 'Triggering current behaviour that fails to use the index'

EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)
SELECT *
=46ROM camera=5Ffeeds=5Fsimple
WHERE camera=5Fid =3D 4;

=5Cecho 'Triggering query with manual cast workaround'

EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)
SELECT *
=46ROM camera=5Ffeeds=5Fsimple
WHERE camera=5Fid =3D 4::int8;

RESET enable=5Fseqscan;
RESET enable=5Fbitmapscan;


Repro example with two columns:


DROP TABLE I=46 EXISTS camera=5Ffeeds=5Fmulti CASCADE;

CREATE TABLE camera=5Ffeeds=5Fmulti (
=C2=A0=C2=A0 =C2=A0id=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SERIAL PRIMA=
RY KEY,
=C2=A0=C2=A0 =C2=A0camera=5Fid=C2=A0 =C2=A0int8 NOT NULL,
=C2=A0=C2=A0 =C2=A0timerange=C2=A0 =C2=A0TSTZRANGE NOT NULL
);

CREATE EXTENSION I=46 NOT EXISTS btree=5Fgist;

CREATE INDEX idx=5Fcamera=5Ffeeds=5Fgist
=C2=A0=C2=A0 =C2=A0ON camera=5Ffeeds=5Fmulti
=C2=A0=C2=A0 =C2=A0USING GIST (camera=5Fid, timerange);

SET enable=5Fseqscan =3D O=46=46;
SET enable=5Fbitmapscan =3D ON;

INSERT INTO camera=5Ffeeds=5Fmulti (camera=5Fid, timerange) VALUES
=C2=A0=C2=A0 =C2=A0(1, '=5B2026-04-01 06:00:00+00, 2026-04-01 12:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(1, '=5B2026-04-01 12:00:00+00, 2026-04-01 18:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(1, '=5B2026-04-01 18:00:00+00, 2026-04-02 06:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(1, '=5B2026-04-02 06:00:00+00, 2026-04-02 12:00:00+00=
)'),

=C2=A0=C2=A0 =C2=A0(2, '=5B2026-04-01 07:00:00+00, 2026-04-01 09:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(2, '=5B2026-04-01 08:30:00+00, 2026-04-01 11:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(2, '=5B2026-04-01 10:00:00+00, 2026-04-01 14:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(2, '=5B2026-04-01 13:00:00+00, 2026-04-01 17:00:00+00=
)'),

=C2=A0=C2=A0 =C2=A0(3, '=5B2026-03-15 00:00:00+00, 2026-03-15 23:59:59+00=
)'),
=C2=A0=C2=A0 =C2=A0(3, '=5B2026-03-20 08:00:00+00, 2026-03-20 20:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(3, '=5B2026-04-01 00:00:00+00, 2026-04-01 23:59:59+00=
)'),

=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 00:00:00+00, 2026-04-01 06:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 06:00:00+00, 2026-04-01 08:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 08:00:00+00, 2026-04-01 12:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 12:00:00+00, 2026-04-01 14:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 14:00:00+00, 2026-04-01 18:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 18:00:00+00, 2026-04-01 22:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 22:00:00+00, 2026-04-02 00:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(4, '=5B2026-04-01 09:00:00+00, 2026-04-01 15:00:00+00=
)'),

=C2=A0=C2=A0 =C2=A0(5, '=5B2026-03-30 00:00:00+00, 2026-04-02 00:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(5, '=5B2026-04-01 10:00:00+00, 2026-04-01 10:30:00+00=
)'),

=C2=A0=C2=A0 =C2=A0(6, '=5B2026-04-01 12:00:00+00, 2026-04-01 12:00:00+00=
)'),
=C2=A0=C2=A0 =C2=A0(6, '=5B2026-04-01 12:00:00+00, 2026-04-01 13:00:00+00=
)');

ANALYZE camera=5Ffeeds=5Fmulti;

=5Cecho 'Triggering current behaviour that fails to use the index'

EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)
SELECT *
=46ROM camera=5Ffeeds=5Fmulti
WHERE camera=5Fid =3D 4
=C2=A0=C2=A0AND timerange && '=5B2026-04-01 10:00:00+00, 2026-04-01 16:00=
:00+00)'::tstzrange
ORDER BY timerange;

=5Cecho 'Triggering query with manual cast workaround'

EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)
SELECT *
=46ROM camera=5Ffeeds=5Fmulti
WHERE camera=5Fid =3D 4::int8
=C2=A0=C2=A0AND timerange && '=5B2026-04-01 10:00:00+00, 2026-04-01 16:00=
:00+00)'::tstzrange
ORDER BY timerange;

RESET enable=5Fseqscan;
RESET enable=5Fbitmapscan;

--69f633d8_ded7263_b38c
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hello hackers,<br />
<br />
I'd like to submit a patch that adds cross-type operator support for the<=
br />
three integer types (int2, int4, int8) to the btree=5Fgist GiST operator<=
br />
families. The patch also lays a general foundation for cross-type<br />
dispatch that other type families can adopt.<br />
<br />
<br />
Current problem:<br />
<br />
GiST indexes are currently limited to same-type operators: the planner<br=
 />
can only match a query condition against an index column if the<br />
operator's left and right argument types exactly match the types<br />
registered in the index's operator family (pg=5Famop). This is true for<b=
r />
every data type that backs a GiST opclass.<br />
<br />
When a query provides a value whose type is compatible but different<br /=
>
from the column type, the planner cannot use the index for that column.<b=
r />
The operator family lookup fails because no cross-type entry exists.<br /=
>
The planner must then either fall back to a sequential scan,<br />
or in multi-column indexes use only the remaining column(s) and filter<br=
 />
the rest as a post-filter.<br />
<br />
As a specific example, consider an int8 (bigint) column indexed with<br /=
>
btree=5Fgist. The query:<br />
<br />
&=23160;&=23160; &=23160;SELECT * =46ROM t WHERE camera=5Fid =3D 1189;<br=
 />
<br />
Here 1189 is evaluated as int4 (integer). The planner resolves the<br />
operator to =3D(int8,int4), which exists in pg=5Foperator but is not<br /=
>
registered in the gist=5Fint8=5Fops family. Only =3D(int8,int8) is<br />
registered, so the column cannot be used as an index condition.<br />
<br />
The workaround is to write an explicit cast in every query:<br />
<br />
&=23160;&=23160; &=23160;WHERE camera=5Fid =3D 1189::int8<br />
<br />
This is fragile as ORMs, application parameter binding, and even hand-<br=
 />
written SQL queries produce values whose types do not exactly match<br />=

the column type.<br />
<br />
A couple of self-contained reproduction scripts are included at the<br />=

end of this email. They create a table with a GiST index, insert<br />
enough rows to make the plan difference visible, and run<br />
EXPLAIN ANALYZE.<br />
<br />
<br />
Proposed solution:<br />
<br />
I'm working on a patch that extends the three btree=5Fgist integer<br />
operator families (gist=5Fint2=5Fops, gist=5Fint4=5Fops, gist=5Fint8=5Fop=
s)<br />
with cross-type comparison and KNN-distance operators covering the<br />
other two integer types.&=23160;&=23160;Concretely:<br />
<br />
&=23160;&=23160; &=23160;gist=5Fint2=5Fops&=23160; &=23160;&lt;-&=23160;&=
=23160;operators for (int2, int4) and (int2, int8)<br />
&=23160;&=23160; &=23160;gist=5Fint4=5Fops&=23160; &=23160;&lt;-&=23160;&=
=23160;operators for (int4, int2) and (int4, int8)<br />
&=23160;&=23160; &=23160;gist=5Fint8=5Fops&=23160; &=23160;&lt;-&=23160;&=
=23160;operators for (int8, int2) and (int8, int4)<br />
<br />
=46or each pair I add all the standard btree=5Fgist strategies for the<br=
 />
comparison operators (&lt;, &lt;=3D, =3D, &gt;=3D, &gt;, &lt;&gt;), plus =
the strategy for<br />
the KNN distance operator (&lt;-&gt;) used by ORDER BY.<br />
<br />
I propose not to register separate cross-type support functions in<br />
the operator family. GiST's amvalidate requires every support<br />
function to have a matching left and right type, and registering<br />
18 additional support functions (three families x two subtypes x<br />
three strategies) would be verbose and error-prone. Instead, I<br />
propose to dispatch cross-type queries directly inside the existing<br />=

consistent and distance functions and use the existing subtype<br />
OID argument.<br />
<br />
I'm thinking of introducing a general-purpose cross-type dispatch<br />
table in btree=5Futils=5Fnum:<br />
<br />
&=23160;&=23160; &=23160;typedef struct gbt=5Fsubtype=5Finfo<br />
&=23160;&=23160; &=23160;=7B<br />
&=23160;&=23160; &=23160; &=23160; &=23160;Oid&=23160; &=23160; &=23160; =
&=23160; &=23160;subtype;&=23160; &=23160; &=23160;/* right-hand Oid, e.g=
. INT4OID */<br />
&=23160;&=23160; &=23160; &=23160; &=23160;gbt=5Fcmp=5Ffn&=23160;&=23160;=
lt, le, eq, ge, gt;&=23160; &=23160;/* comparison callbacks */<br />
&=23160;&=23160; &=23160; &=23160; &=23160;gbt=5Fdist=5Ffn dist;&=23160; =
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;/*=
 KNN distance callback */<br />
&=23160;&=23160; &=23160;=7D gbt=5Fsubtype=5Finfo;<br />
<br />
Each integer opclass defines a static array of these entries:<br />
<br />
&=23160;&=23160; &=23160;static const gbt=5Fsubtype=5Finfo gbt=5Fint2=5Fs=
ubtype=5Fops=5B=5D =3D =7B<br />
&=23160;&=23160; &=23160; &=23160; &=23160;=7BINT4OID,&=23160;&=23160;...=
 6 comparison fns ... ,&=23160;&=23160;distance fn=7D,<br />
&=23160;&=23160; &=23160; &=23160; &=23160;=7BINT8OID,&=23160;&=23160;...=
 6 comparison fns ... ,&=23160;&=23160;distance fn=7D,<br />
&=23160;&=23160; &=23160; &=23160; &=23160;=7BInvalidOid=7D<br />
&=23160;&=23160; &=23160;=7D;<br />
<br />
I'll replace the existing gbt=5Fnum=5Fconsistent() function with<br />
gbt=5Fnum=5Fconsistent=5Fx(), which takes a Datum query value and a<br />=

subtype Oid. If the subtype is InvalidOid or matches the indexed<br />
type, the same-type path is used (backward compatible). Otherwise,<br />
the function walks the dispatch table, finds the matching subtype<br />
entry, and invokes the corresponding cross-type comparison or<br />
distance callback.<br />
<br />
Other btree=5Fgist opclasses (float4/float8, date, timestamp, ...) and<br=
 />
even range-type GiST opfamilies in core can adopt the same pattern by<br =
/>
defining their own subtype dispatch tables and registering cross-type<br =
/>
operators via ALTER OPERATOR =46AMILY.<br />
<br />
I don't plan adding cross-type support for every data type as that<br />
would result in a very bulky patch difficult to review, but I think<br />=

my proposal establishes the infrastructure so that follow-up work<br />
for additional types is straightforward.<br />
<br />
Is there interest in this patch=3F Should I proceed with polishing my<br =
/>
patch and sending it here for a review=3F I appreciate any feedback=21<br=
 />
<br />
Thank you<br />
<br />
<br />
<br />
<br />
<br />
Simple repro example:<br />
<br />
<br />
DROP TABLE I=46 EXISTS camera=5Ffeeds=5Fsimple CASCADE;<br />
<br />
CREATE TABLE camera=5Ffeeds=5Fsimple (<br />
&=23160;&=23160; &=23160;id&=23160;&=23160; &=23160; &=23160; &=23160; &=23=
160;SERIAL PRIMARY KEY,<br />
&=23160;&=23160; &=23160;camera=5Fid&=23160; &=23160;int8 NOT NULL<br />
);<br />
<br />
CREATE EXTENSION I=46 NOT EXISTS btree=5Fgist;<br />
<br />
CREATE INDEX idx=5Fcamera=5Ffeeds=5Fgist<br />
&=23160;&=23160; &=23160;ON camera=5Ffeeds=5Fsimple<br />
&=23160;&=23160; &=23160;USING GIST (camera=5Fid);<br />
<br />
SET enable=5Fseqscan =3D O=46=46;<br />
SET enable=5Fbitmapscan =3D ON;<br />
<br />
INSERT INTO camera=5Ffeeds=5Fsimple (camera=5Fid) VALUES<br />
&=23160;&=23160; &=23160;(1), (1), (1), (1), (2), (2), (2), (2), (3), (3)=
, (3),<br />
&=23160;&=23160; &=23160;(4), (4), (4), (4), (4), (4), (4), (4), (5), (5)=
, (6), (6);<br />
<br />
ANALYZE camera=5Ffeeds=5Fsimple;<br />
<br />
=5Cecho 'Triggering current behaviour that fails to use the index'<br />
<br />
EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)<br />
SELECT *<br />
=46ROM camera=5Ffeeds=5Fsimple<br />
WHERE camera=5Fid =3D 4;<br />
<br />
=5Cecho 'Triggering query with manual cast workaround'<br />
<br />
EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)<br />
SELECT *<br />
=46ROM camera=5Ffeeds=5Fsimple<br />
WHERE camera=5Fid =3D 4::int8;<br />
<br />
RESET enable=5Fseqscan;<br />
RESET enable=5Fbitmapscan;<br />
<br />
<br />
<br />
<br />
<br />
<br />
Repro example with two columns:<br />
<br />
<br />
DROP TABLE I=46 EXISTS camera=5Ffeeds=5Fmulti CASCADE;<br />
<br />
CREATE TABLE camera=5Ffeeds=5Fmulti (<br />
&=23160;&=23160; &=23160;id&=23160;&=23160; &=23160; &=23160; &=23160; &=23=
160;SERIAL PRIMARY KEY,<br />
&=23160;&=23160; &=23160;camera=5Fid&=23160; &=23160;int8 NOT NULL,<br />=

&=23160;&=23160; &=23160;timerange&=23160; &=23160;TSTZRANGE NOT NULL<br =
/>
);<br />
<br />
CREATE EXTENSION I=46 NOT EXISTS btree=5Fgist;<br />
<br />
CREATE INDEX idx=5Fcamera=5Ffeeds=5Fgist<br />
&=23160;&=23160; &=23160;ON camera=5Ffeeds=5Fmulti<br />
&=23160;&=23160; &=23160;USING GIST (camera=5Fid, timerange);<br />
<br />
SET enable=5Fseqscan =3D O=46=46;<br />
SET enable=5Fbitmapscan =3D ON;<br />
<br />
INSERT INTO camera=5Ffeeds=5Fmulti (camera=5Fid, timerange) VALUES<br />
&=23160;&=23160; &=23160;(1, '=5B2026-04-01 06:00:00+00, 2026-04-01 12:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(1, '=5B2026-04-01 12:00:00+00, 2026-04-01 18:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(1, '=5B2026-04-01 18:00:00+00, 2026-04-02 06:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(1, '=5B2026-04-02 06:00:00+00, 2026-04-02 12:00=
:00+00)'),<br />
<br />
&=23160;&=23160; &=23160;(2, '=5B2026-04-01 07:00:00+00, 2026-04-01 09:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(2, '=5B2026-04-01 08:30:00+00, 2026-04-01 11:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(2, '=5B2026-04-01 10:00:00+00, 2026-04-01 14:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(2, '=5B2026-04-01 13:00:00+00, 2026-04-01 17:00=
:00+00)'),<br />
<br />
&=23160;&=23160; &=23160;(3, '=5B2026-03-15 00:00:00+00, 2026-03-15 23:59=
:59+00)'),<br />
&=23160;&=23160; &=23160;(3, '=5B2026-03-20 08:00:00+00, 2026-03-20 20:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(3, '=5B2026-04-01 00:00:00+00, 2026-04-01 23:59=
:59+00)'),<br />
<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 00:00:00+00, 2026-04-01 06:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 06:00:00+00, 2026-04-01 08:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 08:00:00+00, 2026-04-01 12:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 12:00:00+00, 2026-04-01 14:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 14:00:00+00, 2026-04-01 18:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 18:00:00+00, 2026-04-01 22:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 22:00:00+00, 2026-04-02 00:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(4, '=5B2026-04-01 09:00:00+00, 2026-04-01 15:00=
:00+00)'),<br />
<br />
&=23160;&=23160; &=23160;(5, '=5B2026-03-30 00:00:00+00, 2026-04-02 00:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(5, '=5B2026-04-01 10:00:00+00, 2026-04-01 10:30=
:00+00)'),<br />
<br />
&=23160;&=23160; &=23160;(6, '=5B2026-04-01 12:00:00+00, 2026-04-01 12:00=
:00+00)'),<br />
&=23160;&=23160; &=23160;(6, '=5B2026-04-01 12:00:00+00, 2026-04-01 13:00=
:00+00)');<br />
<br />
ANALYZE camera=5Ffeeds=5Fmulti;<br />
<br />
=5Cecho 'Triggering current behaviour that fails to use the index'<br />
<br />
EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)<br />
SELECT *<br />
=46ROM camera=5Ffeeds=5Fmulti<br />
WHERE camera=5Fid =3D 4<br />
&=23160;&=23160;AND timerange &amp;&amp; '=5B2026-04-01 10:00:00+00, 2026=
-04-01 16:00:00+00)'::tstzrange<br />
ORDER BY timerange;<br />
<br />
=5Cecho 'Triggering query with manual cast workaround'<br />
<br />
EXPLAIN (ANALYZE, COSTS, BU=46=46ERS, TIMING, SUMMARY)<br />
SELECT *<br />
=46ROM camera=5Ffeeds=5Fmulti<br />
WHERE camera=5Fid =3D 4::int8<br />
&=23160;&=23160;AND timerange &amp;&amp; '=5B2026-04-01 10:00:00+00, 2026=
-04-01 16:00:00+00)'::tstzrange<br />
ORDER BY timerange;<br />
<br />
RESET enable=5Fseqscan;<br />
RESET enable=5Fbitmapscan;</div>
</div>
</body>
</html>

--69f633d8_ded7263_b38c--