Postgres 13

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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE test
(
  id INT NOT NULL,
  dc TIMESTAMPTZ NOT NULL,
  cs TEXT NOT NULL,
  ce TEXT NOT NULL,
  UNIQUE (id, dc)
);
INSERT INTO test VALUES
(1, '2020-05-27 16:33:52.000', 'green',  'yellow'),
(1, '2020-06-11 20:12:18.000', 'yellow', 'red'),
(1, '2020-06-11 20:20:58.000', 'red',    'green'),

(2, '2020-08-06 14:59:21.000', 'green',  'yellow'),              
(2, '2021-03-03 14:31:44.000', 'yellow', 'red'),

(3, '2021-04-28 12:36:45.000', 'green',  'red');
6 rows affected

-- SELECT * FROM test;  -- check
SELECT
  id, 
  LAG(dc) OVER (PARTITION BY id ORDER BY dc),
  dc AS date_from, 
  LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  cs, ce
FROM test;


id
lag
date_from
lead
cs
ce


1
null
2020-05-27 16:33:52+01
2020-06-11 20:12:18+01
green
yellow

1
2020-05-27 16:33:52+01
2020-06-11 20:12:18+01
2020-06-11 20:20:58+01
yellow
red

1
2020-06-11 20:12:18+01
2020-06-11 20:20:58+01
null
red
green

2
null
2020-08-06 14:59:21+01
2021-03-03 14:31:44+00
green
yellow

2
2020-08-06 14:59:21+01
2021-03-03 14:31:44+00
null
yellow
red

3
null
2021-04-28 12:36:45+01
null
green
red


SELECT
  id, 
  dc AS date_from, 

  TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'),
  
  cs, 
  
  TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'),
  
  ce
FROM test;


id
date_from
tstzrange
cs
tstzrange
ce


1
2020-05-27 16:33:52+01
(,"2020-05-27 16:33:52+01")
green
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow

1
2020-06-11 20:12:18+01
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red

1
2020-06-11 20:20:58+01
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red
["2020-06-11 20:20:58+01",)
green

2
2020-08-06 14:59:21+01
(,"2020-08-06 14:59:21+01")
green
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
2021-03-03 14:31:44+00
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow
["2021-03-03 14:31:44+00",)
red

3
2021-04-28 12:36:45+01
(,"2021-04-28 12:36:45+01")
green
["2021-04-28 12:36:45+01",)
red


SELECT
  id, 
  LAG(dc) OVER (PARTITION BY id ORDER BY dc),
  dc AS date_from, 
  LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
  TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'),
  
  cs, 
  
  TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'),
  
  ce
FROM test;


id
lag
date_from
lead
tstzrange
cs
tstzrange
ce


1
null
2020-05-27 16:33:52+01
2020-06-11 20:12:18+01
(,"2020-05-27 16:33:52+01")
green
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow

1
2020-05-27 16:33:52+01
2020-06-11 20:12:18+01
2020-06-11 20:20:58+01
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red

1
2020-06-11 20:12:18+01
2020-06-11 20:20:58+01
null
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red
["2020-06-11 20:20:58+01",)
green

2
null
2020-08-06 14:59:21+01
2021-03-03 14:31:44+00
(,"2020-08-06 14:59:21+01")
green
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
2020-08-06 14:59:21+01
2021-03-03 14:31:44+00
null
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow
["2021-03-03 14:31:44+00",)
red

3
null
2021-04-28 12:36:45+01
null
(,"2021-04-28 12:36:45+01")
green
["2021-04-28 12:36:45+01",)
red


SELECT id, dummy, trb, cs
FROM
(
  SELECT
    id,
    'LAG' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
UNION ALL
SELECT id, dummy, tra, ce
FROM
(
  SELECT
    id,
    'LEAD' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
ORDER BY id, trb;


id
dummy
trb
cs


1
LAG
(,"2020-05-27 16:33:52+01")
green

1
LAG
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow

1
LEAD
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow

1
LEAD
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red

1
LAG
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red

1
LEAD
["2020-06-11 20:20:58+01",)
green

2
LAG
(,"2020-08-06 14:59:21+01")
green

2
LEAD
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
LAG
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
LEAD
["2021-03-03 14:31:44+00",)
red

3
LAG
(,"2021-04-28 12:36:45+01")
green

3
LEAD
["2021-04-28 12:36:45+01",)
red


SELECT DISTINCT *
FROM
(
SELECT id, trb, cs
FROM
(
  SELECT
    id,
    'LAG' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
UNION ALL
SELECT id, tra, ce
FROM
(
  SELECT
    id,
    'LEAD' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
ORDER BY id, trb
) AS t2
ORDER BY id, trb;


id
trb
cs


1
(,"2020-05-27 16:33:52+01")
green

1
["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")
yellow

1
["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")
red

1
["2020-06-11 20:20:58+01",)
green

2
(,"2020-08-06 14:59:21+01")
green

2
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
["2021-03-03 14:31:44+00",)
red

3
(,"2021-04-28 12:36:45+01")
green

3
["2021-04-28 12:36:45+01",)
red


--
-- records where the end of the range falls anywhere >= 2021:01:01 00:00:00
--

SELECT * FROM 
(

SELECT DISTINCT *
FROM
(
SELECT id, trb, cs
FROM
(
  SELECT
    id,
    'LAG' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
    cs, 
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
    ce
  FROM test
) AS t1
UNION ALL
SELECT id, tra, ce
FROM
(
  SELECT
    id,
    'LEAD' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
    cs, 
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
    ce
  FROM test
) AS t1
ORDER BY id, trb
) AS t2
ORDER BY id, trb
) AS t3
WHERE trb && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');


id
trb
cs


1
["2020-06-11 20:20:58+01",)
green

2
["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")
yellow

2
["2021-03-03 14:31:44+00",)
red

3
(,"2021-04-28 12:36:45+01")
green

3
["2021-04-28 12:36:45+01",)
red


--
--  Status counts at exactly New Year, 2021
--

SELECT cs, COUNT(cs), TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') AS r 
FROM 
(

SELECT DISTINCT *
FROM
(
SELECT id, trb, cs
FROM
(
  SELECT
    id,
    'LAG' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
UNION ALL
SELECT id, tra, ce
FROM
(
  SELECT
    id,
    'LEAD' AS dummy,
    LAG(dc) OVER (PARTITION BY id ORDER BY dc),
    dc AS date_from, 
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc),
  
    TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb,
  
    cs, 
  
    TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra,
  
    ce
  FROM test
) AS t1
ORDER BY id, trb
) AS t2
ORDER BY id, trb

) AS t3
WHERE trb && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]')
GROUP BY cs, r;


cs
count
r


green
2
["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]

yellow
1
["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]

id	lag	date_from	lead	cs	ce
1	null	2020-05-27 16:33:52+01	2020-06-11 20:12:18+01	green	yellow
1	2020-05-27 16:33:52+01	2020-06-11 20:12:18+01	2020-06-11 20:20:58+01	yellow	red
1	2020-06-11 20:12:18+01	2020-06-11 20:20:58+01	null	red	green
2	null	2020-08-06 14:59:21+01	2021-03-03 14:31:44+00	green	yellow
2	2020-08-06 14:59:21+01	2021-03-03 14:31:44+00	null	yellow	red
3	null	2021-04-28 12:36:45+01	null	green	red

id	date_from	tstzrange	cs	tstzrange	ce
1	2020-05-27 16:33:52+01	(,"2020-05-27 16:33:52+01")	green	["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")	yellow
1	2020-06-11 20:12:18+01	["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")	yellow	["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")	red
1	2020-06-11 20:20:58+01	["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")	red	["2020-06-11 20:20:58+01",)	green
2	2020-08-06 14:59:21+01	(,"2020-08-06 14:59:21+01")	green	["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")	yellow
2	2021-03-03 14:31:44+00	["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")	yellow	["2021-03-03 14:31:44+00",)	red
3	2021-04-28 12:36:45+01	(,"2021-04-28 12:36:45+01")	green	["2021-04-28 12:36:45+01",)	red

cs	count	r
green	2	["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]
yellow	1	["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]