By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE FUNCTION f_array_contained_or_no_overlap(anyarray, anyarray)
RETURNS boolean
LANGUAGE sql IMMUTABLE AS
'SELECT $1 <@ $2 OR NOT $1 && $2';
CREATE OPERATOR <@!&& (
FUNCTION = f_array_contained_or_no_overlap
, LEFTARG = anyarray
, RIGHTARG = anyarray
);
CREATE FUNCTION
CREATE OPERATOR
SELECT ARRAY[1, 2] <@!&& ARRAY[1,2,7] AS contained -- true
, ARRAY[1, 2] <@!&& ARRAY[4,5,6] AS no_overlap -- true
, ARRAY[1, 2] <@!&& ARRAY[4,2,6] AS part_overlap; -- false
| contained | no_overlap | part_overlap |
|---|---|---|
| t | t | f |
SELECT 1