Postgres 14

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?.

select version();

version

PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

-- composite type range.
create type mytype as (t1 int, t2 date);
-- create type my_interval as (t1 int, t2 interval);
select (2,'2022-01-02')::mytype ;
create type mytyperange as range(subtype = mytype);

row

(2,2022-01-02)

--left contain the right
select mytyperange (
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype, '[]') @> (2, '2020-01-19')::mytype;
--right contain the left
select
    (2, '2020-01-19')::mytype <@
    mytyperange(
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype, '[]') ;

?column?

t

?column?

t

--todo left and right adjacent or not.
select
    mytyperange (null, (0, '2022-12-31')::mytype) -|-
    mytyperange(
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype, '[]') ;

?column?

f

-- Is the first range strictly right of the second?
-- based on the first element.
select
    mytyperange (null, (-1, '2022-01-19')::mytype) <<
    mytyperange(
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype, '[]') ;

?column?

t

--does the range overlaps, that is, have any common element.
select
    mytyperange ((-2,'2021-12-03'),
                (2, '2022-02-21')::mytype) &&
    mytyperange(
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype) ;
--does the range overlaps, that is, have any common element.
select
    mytyperange ((2,'2020-12-30')::mytype,
                (2, '2020-12-31')::mytype)
    &&
    mytyperange(
    (1,'2022-01-01')::mytype,
    (8, '2022-01-31')::mytype) ;

?column?

t

?column?

t