Hi Nik,
On Tue, Nov 3, 2026 at 10:37 PM Nikolay Samokhvalov <[email protected]>
wrote:
> True for scalars, but accepting arrays of them here is new in 19.
Right, I missed it: my line about floats covered scalars only, and I
never ran arrays with extra_float_digits or bytea_output changed.
Your repro on REL_19_STABLE a8e54f3dad3 with v5 returns 0 rows through
the index and 1 with a seq scan; 18.6 and master 73a28d2733a with v5
reject the CREATE INDEX. float4[], bytea[], point[], box[] and
circle[] behave the same, also as an array of a domain, as a 2-D
array and as a domain over the array type: 24 cases, all stale on 19
with v5, all rejected on 18.6, on master with v5 and on 19 with your
patch (attached: measurements.txt and the script, repro-arrays.sql.txt).
> It is for REL_19_STABLE only, on top of v5: arrays of float4,
> float8, bytea and the geometric types, or of domains over them, are
> mutable.
I found no type missing from your list. The built-in types whose
output function is IMMUTABLE on REL_19_STABLE and STABLE on master are
your ten plus pg_mcv_list and pg_brin_minmax_multi_summary, which have
no array type. In the regression database I also hashed the JSON of
every built-in column under extra_float_digits, bytea_output,
DateStyle, IntervalStyle, TimeZone, lc_monetary and lc_numeric; the
only types whose output changed with an IMMUTABLE output function on
19 are the same ten (gucprobe.sql.txt, attached too).
The patch applies with git am on top of v5, builds with assertions
and no warnings, and passes make check and check-world; without the
jsonfuncs.c hunk the new test fails. Of the 340 types we try on 19 it
rejects eleven more than v5 (arrays of your ten and of a domain over
float8), none that 18.6 accepts, and no scalars.
One nit: in a REL_19-only patch, "On master the output functions of
these types are STABLE" might read better as "e75fcc9867f made them
STABLE in 20 but was not backpatched".
An option for Andrew: making every array mutable on 19, as in 18,
also closes this, but rejects 47 more types that v5 and your patch
accept, among them int4[] and text[], whose output no setting changed.
regards,
Manu
Measurements for the reply to Nikolay, re-run 2026-11-04. Builds, all
--enable-cassert --enable-debug, -O2:
r18 18.6 (724edf9bde9)
r19v5 REL_19_STABLE a8e54f3dad3 + v5-0001 + v5-0002
r19nik REL_19_STABLE a8e54f3dad3 + v5 + Nikolay's nocfbot patch
mv5 master 73a28d2733a + v5-0001 + v5-0002
r19B REL_19_STABLE a8e54f3dad3 + v5 + every array mutable (as in 18);
a measurement build only, not a proposal
Upstream heads as fetched from git.postgresql.org on 2026-11-04.
1. repro-arrays.sql.txt: CREATE INDEX on json_array(v RETURNING jsonb),
then extra_float_digits = 0 (bytea: bytea_output = escape). stale N/M:
index scan finds N rows, seq scan M; ok N/M: they agree.
'domain-array' is d[] with d a domain over the type, '2d-array' a
two-dimensional array, 'domain-over-array' a domain over type[].
case r18 r19v5 r19nik mv5 r19B
float8/array rejected stale 0/1 rejected rejected rejected
float8/domain-array rejected stale 0/1 rejected rejected rejected
float8/2d-array rejected stale 0/1 rejected rejected rejected
float8/domain-over-array rejected stale 0/1 rejected rejected rejected
float8/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
float4/array rejected stale 0/1 rejected rejected rejected
float4/domain-array rejected stale 0/1 rejected rejected rejected
float4/2d-array rejected stale 0/1 rejected rejected rejected
float4/domain-over-array rejected stale 0/1 rejected rejected rejected
float4/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
bytea/array rejected stale 0/1 rejected rejected rejected
bytea/domain-array rejected stale 0/1 rejected rejected rejected
bytea/2d-array rejected stale 0/1 rejected rejected rejected
bytea/domain-over-array rejected stale 0/1 rejected rejected rejected
bytea/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
point/array rejected stale 0/1 rejected rejected rejected
point/domain-array rejected stale 0/1 rejected rejected rejected
point/2d-array rejected stale 0/1 rejected rejected rejected
point/domain-over-array rejected stale 0/1 rejected rejected rejected
point/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
box/array rejected stale 0/1 rejected rejected rejected
box/domain-array rejected stale 0/1 rejected rejected rejected
box/2d-array rejected stale 0/1 rejected rejected rejected
box/domain-over-array rejected stale 0/1 rejected rejected rejected
box/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
circle/array rejected stale 0/1 rejected rejected rejected
circle/domain-array rejected stale 0/1 rejected rejected rejected
circle/2d-array rejected stale 0/1 rejected rejected rejected
circle/domain-over-array rejected stale 0/1 rejected rejected rejected
circle/scalar stale 0/1 stale 0/1 stale 0/1 rejected stale 0/1
array cases (of 24):
r18 0 stale, 24 rejected, 0 ok
r19v5 24 stale, 0 rejected, 0 ok
r19nik 0 stale, 24 rejected, 0 ok
mv5 0 stale, 24 rejected, 0 ok
r19B 0 stale, 24 rejected, 0 ok
2. The repro from the report, verbatim (nik-repro.sql):
r18 CREATE INDEX rejected
r19v5 index scan 0 (plan uses t_i: True), seq scan 1
r19nik CREATE INDEX rejected
mv5 CREATE INDEX rejected
3. catalog.sql: built-in base types whose output function is IMMUTABLE on
REL_19_STABLE and STABLE on master (12 of 70):
box, bytea, circle, float4, float8, line, lseg, path,
pg_brin_minmax_multi_summary (no array type), pg_mcv_list (no array type),
point, polygon
4. gucprobe.sql.txt in the regression database (after make installcheck):
the JSON text of every column of a built-in type, hashed with the
defaults and under each setting. Types whose JSON changed:
REL_19_STABLE + v5 (55 types sampled):
box box_out IMMUTABLE extra_float_digits
bytea byteaout IMMUTABLE bytea_output
circle circle_out IMMUTABLE extra_float_digits
double precision float8out IMMUTABLE extra_float_digits
interval interval_out STABLE IntervalStyle
line line_out IMMUTABLE extra_float_digits
lseg lseg_out IMMUTABLE extra_float_digits
money cash_out STABLE lc_monetary
path path_out IMMUTABLE extra_float_digits
point point_out IMMUTABLE extra_float_digits
polygon poly_out IMMUTABLE extra_float_digits
real float4out IMMUTABLE extra_float_digits
timestamp with time zone timestamptz_out STABLE TimeZone
(2702 columns, hashed with the defaults and under 14 settings;
none missing)
master + v5 (55 types sampled):
box box_out STABLE extra_float_digits
bytea byteaout STABLE bytea_output
circle circle_out STABLE extra_float_digits
double precision float8out STABLE extra_float_digits
interval interval_out STABLE IntervalStyle
line line_out STABLE extra_float_digits
lseg lseg_out STABLE extra_float_digits
money cash_out STABLE lc_monetary
path path_out STABLE extra_float_digits
point point_out STABLE extra_float_digits
polygon poly_out STABLE extra_float_digits
real float4out STABLE extra_float_digits
timestamp with time zone timestamptz_out STABLE TimeZone
(2704 columns, hashed with the defaults and under 14 settings;
none missing)
5. blast.sql, CREATE INDEX on json_array(v RETURNING jsonb) for every
built-in base, range and multirange type, their arrays, and a set of
user-defined ones. Accepted / types tried:
r18 58/330, r19v5 121/340, r19nik 110/340, mv5 97/342, r19B 63/340
accepted by r19v5, rejected by r19nik (11):
box[], bytea[], circle[], double precision[], line[], lseg[], path[],
point[], polygon[], real[], ub_dfloat8[]
accepted by r19nik, rejected by r19v5 (0):
(none)
accepted by r18, rejected by r19nik (the same two as r19v5) (2):
ub_enumc, ub_rangec
accepted by r19nik, rejected by mv5 (13):
box, bytea, circle, double precision, line, lseg, path,
pg_brin_minmax_multi_summary, pg_mcv_list, point, polygon, real,
ub_dfloat8
accepted by r19nik, rejected by r19B (47):
"char"[], bigint[], bit varying[], bit[], boolean[], character varying[],
character[], cid[], cidr[], gtsvector[], inet[], int2vector,
int2vector[], int4multirange[], int4range[], int8multirange[],
int8range[], integer[], json[], jsonb[], jsonpath[], macaddr8[],
macaddr[], name[], numeric[], nummultirange[], numrange[], oid8[], oid[],
oidvector, oidvector[], pg_lsn[], pg_snapshot[], refcursor[], smallint[],
text[], tid[], time with time zone[], time without time zone[],
tsquery[], tsvector[], txid_snapshot[], ub_dint[], uuid[], xid8[], xid[],
xml[]
accepted by r19B, rejected by r18 (types that exist in both) (6):
int4multirange, int4range, int8multirange, int8range, nummultirange,
numrange
-- Arrays of float4, float8, bytea, point, box and circle in a JSON_ARRAY()
-- expression index, then the setting that changes their output. Run with
-- psql -X -d <db> -f repro-arrays.sql.txt
-- One line per case:
-- ORACLE <type>/<shape> | index_exists index_used | index=N seq=M | verdict
-- no-index: CREATE INDEX rejected; DIVERGE: index scan and seq scan
-- disagree (stale index); same: they agree.
\set VERBOSITY terse
\pset tuples_only on
\pset format unaligned
-- float8: SET extra_float_digits = 0
CREATE DOMAIN d_float8 AS float8;
CREATE DOMAIN da_float8 AS float8[];
CREATE TABLE r_float8_array (v float8[]);
INSERT INTO r_float8_array VALUES (ARRAY['0.30000000000000004'::float8]);
CREATE INDEX ON r_float8_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float8_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8])::float8[] RETURNING
jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float8_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float8_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8])::float8[] RETURNING
jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float8/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float8_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float8_domain_array (v d_float8[]);
INSERT INTO r_float8_domain_array VALUES
(ARRAY['0.30000000000000004'::float8::d_float8]);
CREATE INDEX ON r_float8_domain_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float8_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8::d_float8])::d_float8[]
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float8_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float8_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8::d_float8])::d_float8[]
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float8/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float8_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float8_2d_array (v float8[]);
INSERT INTO r_float8_2d_array VALUES (ARRAY[['0.30000000000000004'::float8,
'0.30000000000000004'::float8], ['0.30000000000000004'::float8,
'0.30000000000000004'::float8]]);
CREATE INDEX ON r_float8_2d_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float8_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['0.30000000000000004'::float8,
'0.30000000000000004'::float8], ['0.30000000000000004'::float8,
'0.30000000000000004'::float8]])::float8[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float8_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float8_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['0.30000000000000004'::float8,
'0.30000000000000004'::float8], ['0.30000000000000004'::float8,
'0.30000000000000004'::float8]])::float8[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float8/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float8_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float8_domain_over_array (v da_float8);
INSERT INTO r_float8_domain_over_array VALUES
(ARRAY['0.30000000000000004'::float8]::da_float8);
CREATE INDEX ON r_float8_domain_over_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float8_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8]::da_float8)::da_float8
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float8_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float8_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['0.30000000000000004'::float8]::da_float8)::da_float8
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float8/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid =
'r_float8_domain_over_array'::regclass) AS has_idx) x;
CREATE TABLE r_float8_scalar (v float8);
INSERT INTO r_float8_scalar VALUES ('0.30000000000000004'::float8);
CREATE INDEX ON r_float8_scalar ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float8_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('0.30000000000000004'::float8)::float8 RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float8_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float8_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('0.30000000000000004'::float8)::float8 RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float8/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float8_scalar'::regclass) AS
has_idx) x;
-- float4: SET extra_float_digits = 0
CREATE DOMAIN d_float4 AS float4;
CREATE DOMAIN da_float4 AS float4[];
CREATE TABLE r_float4_array (v float4[]);
INSERT INTO r_float4_array VALUES (ARRAY['3.3000002'::float4]);
CREATE INDEX ON r_float4_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float4_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4])::float4[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float4_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float4_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4])::float4[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float4/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float4_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float4_domain_array (v d_float4[]);
INSERT INTO r_float4_domain_array VALUES (ARRAY['3.3000002'::float4::d_float4]);
CREATE INDEX ON r_float4_domain_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float4_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4::d_float4])::d_float4[] RETURNING
jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float4_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float4_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4::d_float4])::d_float4[] RETURNING
jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float4/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float4_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float4_2d_array (v float4[]);
INSERT INTO r_float4_2d_array VALUES (ARRAY[['3.3000002'::float4,
'3.3000002'::float4], ['3.3000002'::float4, '3.3000002'::float4]]);
CREATE INDEX ON r_float4_2d_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float4_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['3.3000002'::float4, '3.3000002'::float4],
['3.3000002'::float4, '3.3000002'::float4]])::float4[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float4_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float4_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['3.3000002'::float4, '3.3000002'::float4],
['3.3000002'::float4, '3.3000002'::float4]])::float4[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float4/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float4_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_float4_domain_over_array (v da_float4);
INSERT INTO r_float4_domain_over_array VALUES
(ARRAY['3.3000002'::float4]::da_float4);
CREATE INDEX ON r_float4_domain_over_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float4_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4]::da_float4)::da_float4 RETURNING
jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float4_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float4_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['3.3000002'::float4]::da_float4)::da_float4 RETURNING
jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float4/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid =
'r_float4_domain_over_array'::regclass) AS has_idx) x;
CREATE TABLE r_float4_scalar (v float4);
INSERT INTO r_float4_scalar VALUES ('3.3000002'::float4);
CREATE INDEX ON r_float4_scalar ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_float4_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('3.3000002'::float4)::float4 RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_float4_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_float4_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('3.3000002'::float4)::float4 RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'float4/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_float4_scalar'::regclass) AS
has_idx) x;
-- bytea: SET bytea_output = escape
CREATE DOMAIN d_bytea AS bytea;
CREATE DOMAIN da_bytea AS bytea[];
CREATE TABLE r_bytea_array (v bytea[]);
INSERT INTO r_bytea_array VALUES (ARRAY['\x01ff'::bytea]);
CREATE INDEX ON r_bytea_array ((json_array(v RETURNING jsonb)));
SET bytea_output = escape;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_bytea_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea])::bytea[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_bytea_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_bytea_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea])::bytea[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'bytea/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_bytea_array'::regclass) AS has_idx)
x;
CREATE TABLE r_bytea_domain_array (v d_bytea[]);
INSERT INTO r_bytea_domain_array VALUES (ARRAY['\x01ff'::bytea::d_bytea]);
CREATE INDEX ON r_bytea_domain_array ((json_array(v RETURNING jsonb)));
SET bytea_output = escape;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_bytea_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea::d_bytea])::d_bytea[] RETURNING jsonb)
\gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_bytea_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_bytea_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea::d_bytea])::d_bytea[] RETURNING jsonb)
\gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'bytea/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_bytea_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_bytea_2d_array (v bytea[]);
INSERT INTO r_bytea_2d_array VALUES (ARRAY[['\x01ff'::bytea, '\x01ff'::bytea],
['\x01ff'::bytea, '\x01ff'::bytea]]);
CREATE INDEX ON r_bytea_2d_array ((json_array(v RETURNING jsonb)));
SET bytea_output = escape;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_bytea_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['\x01ff'::bytea, '\x01ff'::bytea], ['\x01ff'::bytea,
'\x01ff'::bytea]])::bytea[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_bytea_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_bytea_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[['\x01ff'::bytea, '\x01ff'::bytea], ['\x01ff'::bytea,
'\x01ff'::bytea]])::bytea[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'bytea/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_bytea_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_bytea_domain_over_array (v da_bytea);
INSERT INTO r_bytea_domain_over_array VALUES (ARRAY['\x01ff'::bytea]::da_bytea);
CREATE INDEX ON r_bytea_domain_over_array ((json_array(v RETURNING jsonb)));
SET bytea_output = escape;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_bytea_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea]::da_bytea)::da_bytea RETURNING jsonb)
\gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_bytea_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_bytea_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY['\x01ff'::bytea]::da_bytea)::da_bytea RETURNING jsonb)
\gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'bytea/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_bytea_domain_over_array'::regclass)
AS has_idx) x;
CREATE TABLE r_bytea_scalar (v bytea);
INSERT INTO r_bytea_scalar VALUES ('\x01ff'::bytea);
CREATE INDEX ON r_bytea_scalar ((json_array(v RETURNING jsonb)));
SET bytea_output = escape;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_bytea_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('\x01ff'::bytea)::bytea RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_bytea_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_bytea_scalar WHERE
json_array(v RETURNING jsonb) =
json_array(('\x01ff'::bytea)::bytea RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'bytea/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_bytea_scalar'::regclass) AS
has_idx) x;
-- point: SET extra_float_digits = 0
CREATE DOMAIN d_point AS point;
CREATE DOMAIN da_point AS point[];
CREATE TABLE r_point_array (v point[]);
INSERT INTO r_point_array VALUES (ARRAY[point(0.30000000000000004, 1)]);
CREATE INDEX ON r_point_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_point_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)])::point[] RETURNING jsonb)
\gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_point_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_point_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)])::point[] RETURNING jsonb)
\gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'point/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_point_array'::regclass) AS has_idx)
x;
CREATE TABLE r_point_domain_array (v d_point[]);
INSERT INTO r_point_domain_array VALUES (ARRAY[point(0.30000000000000004,
1)::d_point]);
CREATE INDEX ON r_point_domain_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_point_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)::d_point])::d_point[]
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_point_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_point_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)::d_point])::d_point[]
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'point/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_point_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_point_2d_array (v point[]);
INSERT INTO r_point_2d_array VALUES (ARRAY[[point(0.30000000000000004, 1),
point(0.30000000000000004, 1)], [point(0.30000000000000004, 1),
point(0.30000000000000004, 1)]]);
CREATE INDEX ON r_point_2d_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_point_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[point(0.30000000000000004, 1),
point(0.30000000000000004, 1)], [point(0.30000000000000004, 1),
point(0.30000000000000004, 1)]])::point[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_point_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_point_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[point(0.30000000000000004, 1),
point(0.30000000000000004, 1)], [point(0.30000000000000004, 1),
point(0.30000000000000004, 1)]])::point[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'point/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_point_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_point_domain_over_array (v da_point);
INSERT INTO r_point_domain_over_array VALUES (ARRAY[point(0.30000000000000004,
1)]::da_point);
CREATE INDEX ON r_point_domain_over_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_point_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)]::da_point)::da_point
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_point_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_point_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[point(0.30000000000000004, 1)]::da_point)::da_point
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'point/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_point_domain_over_array'::regclass)
AS has_idx) x;
CREATE TABLE r_point_scalar (v point);
INSERT INTO r_point_scalar VALUES (point(0.30000000000000004, 1));
CREATE INDEX ON r_point_scalar ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_point_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((point(0.30000000000000004, 1))::point RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_point_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_point_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((point(0.30000000000000004, 1))::point RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'point/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_point_scalar'::regclass) AS
has_idx) x;
-- box: SET extra_float_digits = 0
CREATE DOMAIN d_box AS box;
CREATE DOMAIN da_box AS box[];
CREATE TABLE r_box_array (v box[]);
INSERT INTO r_box_array VALUES (ARRAY[box(point(0.30000000000000004, 0),
point(1, 1))]);
CREATE INDEX ON r_box_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_box_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1, 1))])::box[]
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_box_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_box_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1, 1))])::box[]
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'box/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_box_array'::regclass) AS has_idx) x;
CREATE TABLE r_box_domain_array (v d_box[]);
INSERT INTO r_box_domain_array VALUES (ARRAY[box(point(0.30000000000000004, 0),
point(1, 1))::d_box]);
CREATE INDEX ON r_box_domain_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_box_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1,
1))::d_box])::d_box[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_box_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_box_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1,
1))::d_box])::d_box[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'box/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_box_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_box_2d_array (v box[]);
INSERT INTO r_box_2d_array VALUES (ARRAY[[box(point(0.30000000000000004, 0),
point(1, 1)), box(point(0.30000000000000004, 0), point(1, 1))],
[box(point(0.30000000000000004, 0), point(1, 1)),
box(point(0.30000000000000004, 0), point(1, 1))]]);
CREATE INDEX ON r_box_2d_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_box_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[box(point(0.30000000000000004, 0), point(1, 1)),
box(point(0.30000000000000004, 0), point(1, 1))],
[box(point(0.30000000000000004, 0), point(1, 1)),
box(point(0.30000000000000004, 0), point(1, 1))]])::box[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_box_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_box_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[box(point(0.30000000000000004, 0), point(1, 1)),
box(point(0.30000000000000004, 0), point(1, 1))],
[box(point(0.30000000000000004, 0), point(1, 1)),
box(point(0.30000000000000004, 0), point(1, 1))]])::box[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'box/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_box_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_box_domain_over_array (v da_box);
INSERT INTO r_box_domain_over_array VALUES
(ARRAY[box(point(0.30000000000000004, 0), point(1, 1))]::da_box);
CREATE INDEX ON r_box_domain_over_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_box_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1,
1))]::da_box)::da_box RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_box_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_box_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[box(point(0.30000000000000004, 0), point(1,
1))]::da_box)::da_box RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'box/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_box_domain_over_array'::regclass)
AS has_idx) x;
CREATE TABLE r_box_scalar (v box);
INSERT INTO r_box_scalar VALUES (box(point(0.30000000000000004, 0), point(1,
1)));
CREATE INDEX ON r_box_scalar ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_box_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((box(point(0.30000000000000004, 0), point(1, 1)))::box RETURNING
jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_box_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_box_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((box(point(0.30000000000000004, 0), point(1, 1)))::box RETURNING
jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'box/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_box_scalar'::regclass) AS has_idx)
x;
-- circle: SET extra_float_digits = 0
CREATE DOMAIN d_circle AS circle;
CREATE DOMAIN da_circle AS circle[];
CREATE TABLE r_circle_array (v circle[]);
INSERT INTO r_circle_array VALUES (ARRAY[circle(point(0.30000000000000004, 0),
1)]);
CREATE INDEX ON r_circle_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_circle_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0), 1)])::circle[]
RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_circle_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_circle_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0), 1)])::circle[]
RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'circle/array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_circle_array'::regclass) AS
has_idx) x;
CREATE TABLE r_circle_domain_array (v d_circle[]);
INSERT INTO r_circle_domain_array VALUES
(ARRAY[circle(point(0.30000000000000004, 0), 1)::d_circle]);
CREATE INDEX ON r_circle_domain_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_circle_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0),
1)::d_circle])::d_circle[] RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_circle_domain_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_circle_domain_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0),
1)::d_circle])::d_circle[] RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'circle/domain-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_circle_domain_array'::regclass) AS
has_idx) x;
CREATE TABLE r_circle_2d_array (v circle[]);
INSERT INTO r_circle_2d_array VALUES (ARRAY[[circle(point(0.30000000000000004,
0), 1), circle(point(0.30000000000000004, 0), 1)],
[circle(point(0.30000000000000004, 0), 1), circle(point(0.30000000000000004,
0), 1)]]);
CREATE INDEX ON r_circle_2d_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_circle_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[circle(point(0.30000000000000004, 0), 1),
circle(point(0.30000000000000004, 0), 1)], [circle(point(0.30000000000000004,
0), 1), circle(point(0.30000000000000004, 0), 1)]])::circle[] RETURNING jsonb)
\gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_circle_2d_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_circle_2d_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[[circle(point(0.30000000000000004, 0), 1),
circle(point(0.30000000000000004, 0), 1)], [circle(point(0.30000000000000004,
0), 1), circle(point(0.30000000000000004, 0), 1)]])::circle[] RETURNING jsonb)
\gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'circle/2d-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_circle_2d_array'::regclass) AS
has_idx) x;
CREATE TABLE r_circle_domain_over_array (v da_circle);
INSERT INTO r_circle_domain_over_array VALUES
(ARRAY[circle(point(0.30000000000000004, 0), 1)]::da_circle);
CREATE INDEX ON r_circle_domain_over_array ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_circle_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0),
1)]::da_circle)::da_circle RETURNING jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_circle_domain_over_array'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_circle_domain_over_array WHERE
json_array(v RETURNING jsonb) =
json_array((ARRAY[circle(point(0.30000000000000004, 0),
1)]::da_circle)::da_circle RETURNING jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'circle/domain-over-array', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid =
'r_circle_domain_over_array'::regclass) AS has_idx) x;
CREATE TABLE r_circle_scalar (v circle);
INSERT INTO r_circle_scalar VALUES (circle(point(0.30000000000000004, 0), 1));
CREATE INDEX ON r_circle_scalar ((json_array(v RETURNING jsonb)));
SET extra_float_digits = 0;
BEGIN; SET LOCAL enable_seqscan = off;
SELECT count(*) AS n_idx FROM r_circle_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((circle(point(0.30000000000000004, 0), 1))::circle RETURNING
jsonb) \gset
SELECT coalesce(sum(pg_stat_get_xact_numscans(indexrelid)), 0) > 0 AS used
FROM pg_index WHERE indrelid = 'r_circle_scalar'::regclass \gset
COMMIT;
RESET enable_seqscan; SET enable_indexscan = off; SET enable_indexonlyscan =
off;
SET enable_bitmapscan = off;
SELECT count(*) AS n_seq FROM r_circle_scalar WHERE
json_array(v RETURNING jsonb) =
json_array((circle(point(0.30000000000000004, 0), 1))::circle RETURNING
jsonb) \gset
RESET enable_indexscan; RESET enable_indexonlyscan; RESET enable_bitmapscan;
RESET extra_float_digits; RESET bytea_output;
SELECT format('ORACLE %s | index_exists=%s index_used=%s | index=%s seq=%s |
%s',
'circle/scalar', x.has_idx, :'used', :n_idx, :n_seq,
CASE WHEN NOT x.has_idx THEN 'no-index'
WHEN :n_idx <> :n_seq THEN 'DIVERGE' ELSE 'same' END)
FROM (SELECT EXISTS (SELECT 1 FROM pg_index
WHERE indrelid = 'r_circle_scalar'::regclass) AS
has_idx) x;
-- gucprobe.sql : run in the "regression" database left by make installcheck.
For every
-- column of a user table whose type is a built-in base type, an array of one,
or a domain over
-- either, hash json_array(col RETURNING jsonb)::text over all rows, once with
the defaults and
-- once per setting below. A type whose hash changes has a JSON output that
depends on that
-- setting. Run after make installcheck, with a locale that formats money
-- differently from C:
-- psql -X -d regression -v loc=de_DE.utf8 -f gucprobe.sql.txt
\set VERBOSITY terse
\pset tuples_only on
\pset format unaligned
SET TimeZone = 'UTC'; SET DateStyle = 'ISO, MDY'; SET IntervalStyle = postgres;
SET extra_float_digits = 1; SET bytea_output = hex;
SET lc_monetary = 'C'; SET lc_numeric = 'C';
DROP TABLE IF EXISTS probe_res, probe_cols CASCADE;
DROP FUNCTION IF EXISTS probe_gen();
CREATE TABLE probe_res (variant text, typ text, tbl text, col text, h text);
-- The columns, resolved once: domain -> base, array -> element, element domain
-> base.
CREATE TABLE probe_cols AS
WITH cols AS (
SELECT c.oid::regclass::text AS tbl, a.attname::text AS col,
CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE t.oid END AS b1
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT
a.attisdropped
JOIN pg_type t ON t.oid = a.atttypid
WHERE c.relkind IN ('r', 'm') AND n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
AND c.relname NOT IN ('probe_res', 'probe_cols')),
el AS (
SELECT cols.*, CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN t.typelem ELSE
b1 END AS e1
FROM cols JOIN pg_type t ON t.oid = cols.b1),
base AS (
SELECT el.*, CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE t.oid END AS e2
FROM el JOIN pg_type t ON t.oid = el.e1)
SELECT base.tbl, base.col, pg_catalog.format_type(base.e2, NULL) AS typ
FROM base JOIN pg_type t ON t.oid = base.e2
WHERE base.e2 < 16384 AND t.typtype = 'b';
-- One INSERT per column, for the settings now in effect; probe.variant names
them.
CREATE FUNCTION probe_gen() RETURNS SETOF text LANGUAGE sql AS $f$
SELECT format('INSERT INTO probe_res SELECT %L, %L, %L, %L, '
'md5(coalesce(string_agg(json_array(%I RETURNING jsonb)::text, '
'%L ORDER BY ctid), %L)) FROM %s',
current_setting('probe.variant'), typ, tbl, col, col, E'\n',
'', tbl)
FROM probe_cols ORDER BY tbl, col
$f$;
SELECT set_config('probe.variant', 'base', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 0;
SELECT set_config('probe.variant', 'extra_float_digits=0', false);
SELECT probe_gen() \gexec
SET extra_float_digits = -15;
SELECT set_config('probe.variant', 'extra_float_digits=-15', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 3;
SELECT set_config('probe.variant', 'extra_float_digits=3', false);
SELECT probe_gen() \gexec
SET extra_float_digits = 1;
SET bytea_output = escape;
SELECT set_config('probe.variant', 'bytea_output=escape', false);
SELECT probe_gen() \gexec
SET bytea_output = hex;
SET DateStyle = 'SQL, DMY';
SELECT set_config('probe.variant', 'DateStyle=SQL,DMY', false);
SELECT probe_gen() \gexec
SET DateStyle = 'Postgres, MDY';
SELECT set_config('probe.variant', 'DateStyle=Postgres', false);
SELECT probe_gen() \gexec
SET DateStyle = 'German';
SELECT set_config('probe.variant', 'DateStyle=German', false);
SELECT probe_gen() \gexec
SET DateStyle = 'ISO, MDY';
SET IntervalStyle = postgres_verbose;
SELECT set_config('probe.variant', 'IntervalStyle=postgres_verbose', false);
SELECT probe_gen() \gexec
SET IntervalStyle = sql_standard;
SELECT set_config('probe.variant', 'IntervalStyle=sql_standard', false);
SELECT probe_gen() \gexec
SET IntervalStyle = iso_8601;
SELECT set_config('probe.variant', 'IntervalStyle=iso_8601', false);
SELECT probe_gen() \gexec
SET IntervalStyle = postgres;
SET TimeZone = 'Asia/Kathmandu';
SELECT set_config('probe.variant', 'TimeZone=Asia/Kathmandu', false);
SELECT probe_gen() \gexec
SET TimeZone = 'America/Santiago';
SELECT set_config('probe.variant', 'TimeZone=America/Santiago', false);
SELECT probe_gen() \gexec
SET TimeZone = 'UTC';
SET lc_monetary = :'loc';
SELECT set_config('probe.variant', 'lc_monetary=' || :'loc' || '', false);
SELECT probe_gen() \gexec
SET lc_monetary = 'C';
SET lc_numeric = :'loc';
SELECT set_config('probe.variant', 'lc_numeric=' || :'loc' || '', false);
SELECT probe_gen() \gexec
SET lc_numeric = 'C';
-- Types covered, with the number of columns and the output function's
volatility.
SELECT 'COVER ' || r.typ || ' cols=' || count(*) || ' out=' || p.proname::text
|| ':' || p.provolatile::text
FROM probe_res r JOIN pg_type t ON t.oid = r.typ::regtype JOIN pg_proc p ON
p.oid = t.typoutput
WHERE r.variant = 'base' GROUP BY r.typ, p.proname, p.provolatile ORDER BY 1;
-- Per type: the settings under which its JSON changed, on how many columns.
SELECT 'CHANGES ' || r.typ || ' out=' || p.proname::text || ':' ||
p.provolatile::text || ' | ' ||
string_agg(r.variant || ' (' || r.n::text || ')', ', ' ORDER BY
r.variant)
FROM (SELECT v.variant, v.typ, count(*) AS n FROM probe_res v JOIN probe_res b
ON b.variant = 'base' AND b.tbl = v.tbl AND b.col = v.col
WHERE v.variant <> 'base' AND v.h IS DISTINCT FROM b.h GROUP BY 1, 2) r
JOIN pg_type t ON t.oid = r.typ::regtype JOIN pg_proc p ON p.oid = t.typoutput
GROUP BY r.typ, p.proname, p.provolatile ORDER BY 1;
-- The columns behind each change.
SELECT 'COLS ' || v.typ || ' | ' || v.variant || ' | ' || string_agg(v.tbl ||
'.' || v.col, ' ' ORDER BY v.tbl, v.col)
FROM probe_res v JOIN probe_res b ON b.variant = 'base' AND b.tbl = v.tbl AND
b.col = v.col
WHERE v.variant <> 'base' AND v.h IS DISTINCT FROM b.h GROUP BY v.typ,
v.variant ORDER BY 1;
-- Columns hashed per setting (equal counts: no INSERT failed).
SELECT 'ROWS ' || variant || ' ' || count(*) FROM probe_res GROUP BY variant
ORDER BY 1;
SELECT 'PROBECOLS ' || count(*) FROM probe_cols;
