Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


-- bezieht sich auf 
-- https://www.datenbankforum.com/threads/laesst-sich-diese-aufgabe-ich-habe-kein-namen-dafuer-direkt-sinnvoll-in-postgresql-loesen.6794/

-- Tabelle anlegen
create table brokenrangetest(id	int, gesamt_start int, gesamt_ende int, start int, ende int);

CREATE TABLE


-- Beispieldaten
insert into brokenrangetest values (
999,	5,	300,	2,	95),          -- id block mit kontinuierlichen Einzelwerten
(999,	5,	300,	90,	223),         -- die zum Gesamtbereich passen
(999,	5,	300,	95,	223),
(999,	5,	300,	223,	284),
(999,	5,	300,	284,	291),
(999,	5,	300,	284,	291),
(999,	5,	300,	284,	300),
(333,	5,	300,	2,	89),          -- id block mit Lücken in den Einzelwerten
(333,	5,	300,	90,	223),         -- das soll gefunden werden
(333,	5,	300,	223,	284),
(333,	5,	300,	284,	291),
(333,	5,	300,	284,	291),
(333,	5,	300,	284,	300);

INSERT 0 13


-- die Beispieldaten
select id, numrange(gesamt_start, gesamt_ende), numrange(start,ende) from brokenrangetest;


id
numrange
numrange


999
[5,300)
[2,95)

999
[5,300)
[90,223)

999
[5,300)
[95,223)

999
[5,300)
[223,284)

999
[5,300)
[284,291)

999
[5,300)
[284,291)

999
[5,300)
[284,300)

333
[5,300)
[2,89)

333
[5,300)
[90,223)

333
[5,300)
[223,284)

333
[5,300)
[284,291)

333
[5,300)
[284,291)

333
[5,300)
[284,300)



SELECT 13


-- Lücke finden
-- id 333 hat eine Lücke in den Start/End Werten
select id, gesamtrange, rangearray
  from (
        select id, gesamtrange, range_agg(teilrange) as rangearray 
                             -- teilrange aggregieren 
          from (
                select id,   -- ranges aus den spaltendaten erzeugen 
                       numrange(gesamt_start, gesamt_ende) as gesamtrange, 
                       numrange(start,ende) as teilrange 
                  from brokenrangetest 
               )x
         group by x.id, x.gesamtrange ) y
 where not(rangearray @> gesamtrange)
                             -- nur die ausgeben, wo rangearray nicht in gesamtrange


id
gesamtrange
rangearray


333
[5,300)
{[2,89),[90,300)}



SELECT 1


-- Kontrollabfrage: ungefiltert alles ausgeben
select id, gesamtrange, rangearray
  from (
        select id, gesamtrange, range_agg(teilrange) as rangearray 
                             -- teilrange aggregieren 
          from (
                select id,   -- ranges aus den spaltendaten erzeugen 
                       numrange(gesamt_start, gesamt_ende) as gesamtrange, 
                       numrange(start,ende) as teilrange 
                  from brokenrangetest 
               )x
         group by x.id, x.gesamtrange ) y


id
gesamtrange
rangearray


333
[5,300)
{[2,89),[90,300)}

999
[5,300)
{[2,300)}



SELECT 2

id	numrange	numrange
999	[5,300)	[2,95)
999	[5,300)	[90,223)
999	[5,300)	[95,223)
999	[5,300)	[223,284)
999	[5,300)	[284,291)
999	[5,300)	[284,291)
999	[5,300)	[284,300)
333	[5,300)	[2,89)
333	[5,300)	[90,223)
333	[5,300)	[223,284)
333	[5,300)	[284,291)
333	[5,300)	[284,291)
333	[5,300)	[284,300)