Postgres 12

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 12.8 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
(
  row_number INTEGER NOT NULL,
  code       TEXT    NOT NULL,
  from_date  DATE    NOT NULL,
  to_date    DATE    NULL
);
INSERT INTO test VALUES
(72,  'B', '2021-09-24', NULL),
(45,  'B', '2021-09-16', '2021-09-23'),
(645, 'A', '2021-09-07', '2021-09-15'), 	
(25,  'A', '2021-04-01', '2021-09-06'),
(45,  'B', '2021-03-13', '2021-03-31'),
(55,  'A', '2021-01-13', '2021-03-12'),
(5,   'A', '2021-01-01', '2021-01-12');
7 rows affected

SELECT * FROM test ORDER BY from_date DESC;


row_number
code
from_date
to_date


72
B
2021-09-24
null

45
B
2021-09-16
2021-09-23

645
A
2021-09-07
2021-09-15

25
A
2021-04-01
2021-09-06

45
B
2021-03-13
2021-03-31

55
A
2021-01-13
2021-03-12

5
A
2021-01-01
2021-01-12


SELECT 
  ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
  code, from_date, to_date,

  CASE 
    WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
    ELSE 1
  END AS code_change

FROM test;


rn
code
from_date
to_date
code_change


1
B
2021-09-24
null
1

2
B
2021-09-16
2021-09-23
0

3
A
2021-09-07
2021-09-15
1

4
A
2021-04-01
2021-09-06
0

5
B
2021-03-13
2021-03-31
1

6
A
2021-01-13
2021-03-12
1

7
A
2021-01-01
2021-01-12
0


SELECT
  *, SUM(code_change) OVER (ORDER BY from_date DESC)
FROM
(
  SELECT 
    ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
    code, from_date, to_date,

    CASE 
      WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
      ELSE 1
    END AS code_change

  FROM test
) AS tab_01
ORDER BY from_date DESC;


rn
code
from_date
to_date
code_change
sum


1
B
2021-09-24
null
1
1

2
B
2021-09-16
2021-09-23
0
1

3
A
2021-09-07
2021-09-15
1
2

4
A
2021-04-01
2021-09-06
0
2

5
B
2021-03-13
2021-03-31
1
3

6
A
2021-01-13
2021-03-12
1
4

7
A
2021-01-01
2021-01-12
0
4


SELECT 
  code, MIN(sum_cc) 
FROM
(
SELECT
  *, SUM(code_change) OVER (ORDER BY from_date DESC) AS sum_cc
FROM
(
  SELECT 
    ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
    code, from_date, to_date,

    CASE 
      WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
      ELSE 1
    END AS code_change

  FROM test
) AS tab_01
ORDER BY from_date DESC
) AS tab_02
WHERE code = 'A'
GROUP BY code;


code
min


A
2


SELECT
  *, SUM(tab_01.code_change) OVER (ORDER BY t.from_date DESC)
FROM
(
  SELECT 
    ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
    code, from_date, to_date,

    CASE 
      WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
      ELSE 1
    END AS code_change

  FROM test
) AS tab_01
JOIN test t
ON t.from_date = tab_01.from_date;


rn
code
from_date
to_date
code_change
row_number
code
from_date
to_date
sum


1
B
2021-09-24
null
1
72
B
2021-09-24
null
1

2
B
2021-09-16
2021-09-23
0
45
B
2021-09-16
2021-09-23
1

3
A
2021-09-07
2021-09-15
1
645
A
2021-09-07
2021-09-15
2

4
A
2021-04-01
2021-09-06
0
25
A
2021-04-01
2021-09-06
2

5
B
2021-03-13
2021-03-31
1
45
B
2021-03-13
2021-03-31
3

6
A
2021-01-13
2021-03-12
1
55
A
2021-01-13
2021-03-12
4

7
A
2021-01-01
2021-01-12
0
5
A
2021-01-01
2021-01-12
4


SELECT tab_03.*, tab_02.*
FROM
(
  SELECT
    *, SUM(tab_01.code_change) OVER (ORDER BY tab_01.from_date DESC) AS xx 
  FROM
  (
    SELECT 
      ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
      code, from_date, to_date,

      CASE 
        WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
        ELSE 1
      END AS code_change

    FROM test
  ) AS tab_01
) AS tab_03
JOIN 
(
  SELECT 
  code, MIN(sum_cc) AS yy
  FROM
  (
    SELECT
      *, SUM(code_change) OVER (ORDER BY from_date DESC) AS sum_cc
    FROM
    (
      SELECT 
        ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
        code, from_date, to_date,

        CASE 
          WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
          ELSE 1
        END AS code_change

      FROM test
    ) AS tab_01
    ORDER BY from_date DESC
  ) AS tab_02
  WHERE code = 'A'
  GROUP BY code
) AS tab_02
ON tab_03.xx = tab_02.yy;


rn
code
from_date
to_date
code_change
xx
code
yy


3
A
2021-09-07
2021-09-15
1
2
A
2

4
A
2021-04-01
2021-09-06
0
2
A
2


SELECT row_number, 
    code, 
    from_date, 
    to_date, 
    lag(from_date) OVER (ORDER BY row_number) AS next_from 
FROM test
WHERE code='A'
ORDER BY row_number;


row_number
code
from_date
to_date
next_from


5
A
2021-01-01
2021-01-12
null

25
A
2021-04-01
2021-09-06
2021-01-01

55
A
2021-01-13
2021-03-12
2021-04-01

645
A
2021-09-07
2021-09-15
2021-01-13


WITH start AS (
SELECT row_number, 
    code, 
    from_date, 
    to_date, 
    lag(from_date) OVER (ORDER BY row_number) AS next_from 
FROM test
WHERE code='A'
ORDER BY row_number
),
group_identify AS 
(
  SELECT *,
    CASE WHEN row_number = 1 OR next_from - to_date = 1 THEN 1
    ELSE 0
    END AS group_cnt
FROM    start
)
SELECT *  FROM group_identify;


row_number
code
from_date
to_date
next_from
group_cnt


5
A
2021-01-01
2021-01-12
null
0

25
A
2021-04-01
2021-09-06
2021-01-01
0

55
A
2021-01-13
2021-03-12
2021-04-01
0

645
A
2021-09-07
2021-09-15
2021-01-13
0


WITH start AS (
SELECT row_number, 
    code, 
    from_date, 
    to_date, 
    lag(from_date) OVER (ORDER BY row_number) AS next_from 
FROM test
WHERE code='A'
ORDER BY row_number
),
group_identify AS (
SELECT *,
    CASE WHEN row_number = 1 OR next_from - to_date = 1
    THEN 1
    ELSE 0
    END AS group_cnt
FROM    start
), 
group_enumerate AS (
SELECT *,
    row_number - sum(group_cnt) OVER (ORDER BY row_number) AS grpn
FROM group_identify
)
SELECT * FROM group_enumerate;


row_number
code
from_date
to_date
next_from
group_cnt
grpn


5
A
2021-01-01
2021-01-12
null
0
5

25
A
2021-04-01
2021-09-06
2021-01-01
0
25

55
A
2021-01-13
2021-03-12
2021-04-01
0
55

645
A
2021-09-07
2021-09-15
2021-01-13
0
645


WITH start AS (
SELECT row_number, 
    code, 
    from_date, 
    to_date, 
    lag(from_date) OVER (ORDER BY row_number) AS next_from 
FROM test
WHERE code='A'
ORDER BY row_number
),
group_identify AS (
SELECT *,
    CASE WHEN row_number = 1 OR next_from - to_date = 1
    THEN 1
    ELSE 0
    END AS group_cnt
FROM    start
), 
group_enumerate AS (
SELECT *,
    row_number - sum(group_cnt) OVER (ORDER BY row_number) AS grpn
FROM group_identify
)
SELECT row_number, code, from_date, to_date
FROM group_enumerate
WHERE grpn=(select min(grpn) from group_enumerate)
ORDER BY row_number;


row_number
code
from_date
to_date


5
A
2021-01-01
2021-01-12


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT tab_03.*, tab_02.*
FROM
(
  SELECT
    *, SUM(tab_01.code_change) OVER (ORDER BY tab_01.from_date DESC) AS xx 
  FROM
  (
    SELECT 
      ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
      code, from_date, to_date,

      CASE 
        WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
        ELSE 1
      END AS code_change

    FROM test
  ) AS tab_01
) AS tab_03
JOIN 
(
  SELECT 
  code, MIN(sum_cc) AS yy
  FROM
  (
    SELECT
      *, SUM(code_change) OVER (ORDER BY from_date DESC) AS sum_cc
    FROM
    (
      SELECT 
        ROW_NUMBER() OVER (ORDER BY from_date DESC) AS rn,
        code, from_date, to_date,

        CASE 
          WHEN LAG(code) OVER (ORDER BY from_date DESC) = code THEN 0
          ELSE 1
        END AS code_change

      FROM test
    ) AS tab_01
    ORDER BY from_date DESC
  ) AS tab_02
  WHERE code = 'A'
  GROUP BY code
) AS tab_02
ON tab_03.xx = tab_02.yy;


QUERY PLAN


Hash Join  (cost=222.41..291.96 rows=34 width=100) (actual time=0.111..0.121 rows=2 loops=1)

  Output: tab_01.rn, tab_01.code, tab_01.from_date, tab_01.to_date, tab_01.code_change, (sum(tab_01.code_change) OVER (?)), tab_02.code, (min(tab_02.sum_cc))

  Hash Cond: ((sum(tab_01.code_change) OVER (?)) = (min(tab_02.sum_cc)))

  Buffers: shared hit=2

  ->  WindowAgg  (cost=78.60..132.28 rows=1130 width=60) (actual time=0.034..0.046 rows=7 loops=1)

        Output: tab_01.rn, tab_01.code, tab_01.from_date, tab_01.to_date, tab_01.code_change, sum(tab_01.code_change) OVER (?)

        Buffers: shared hit=1

        ->  Subquery Scan on tab_01  (cost=78.60..115.33 rows=1130 width=52) (actual time=0.027..0.036 rows=7 loops=1)

              Output: tab_01.from_date, tab_01.rn, tab_01.code, tab_01.to_date, tab_01.code_change

              Buffers: shared hit=1

              ->  WindowAgg  (cost=78.60..104.03 rows=1130 width=52) (actual time=0.026..0.034 rows=7 loops=1)

                    Output: row_number() OVER (?), test.code, test.from_date, test.to_date, CASE WHEN (lag(test.code) OVER (?) = test.code) THEN 0 ELSE 1 END

                    Buffers: shared hit=1

                    ->  Sort  (cost=78.60..81.43 rows=1130 width=40) (actual time=0.015..0.016 rows=7 loops=1)

                          Output: test.from_date, test.code, test.to_date

                          Sort Key: test.from_date DESC

                          Sort Method: quicksort  Memory: 25kB

                          Buffers: shared hit=1

                          ->  Seq Scan on public.test  (cost=0.00..21.30 rows=1130 width=40) (actual time=0.008..0.010 rows=7 loops=1)

                                Output: test.from_date, test.code, test.to_date

                                Buffers: shared hit=1

  ->  Hash  (cost=143.73..143.73 rows=6 width=40) (actual time=0.053..0.054 rows=1 loops=1)

        Output: tab_02.code, (min(tab_02.sum_cc))

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        Buffers: shared hit=1

        ->  GroupAggregate  (cost=78.60..143.67 rows=6 width=40) (actual time=0.052..0.053 rows=1 loops=1)

              Output: tab_02.code, min(tab_02.sum_cc)

              Group Key: tab_02.code

              Buffers: shared hit=1

              ->  Subquery Scan on tab_02  (cost=78.60..143.58 rows=6 width=40) (actual time=0.040..0.048 rows=4 loops=1)

                    Output: tab_02.rn, tab_02.code, tab_02.from_date, tab_02.to_date, tab_02.code_change, tab_02.sum_cc

                    Filter: (tab_02.code = 'A'::text)

                    Rows Removed by Filter: 3

                    Buffers: shared hit=1

                    ->  WindowAgg  (cost=78.60..129.45 rows=1130 width=60) (actual time=0.035..0.046 rows=7 loops=1)

                          Output: NULL::bigint, tab_01_1.code, tab_01_1.from_date, NULL::date, NULL::integer, sum(tab_01_1.code_change) OVER (?)

                          Buffers: shared hit=1

                          ->  Subquery Scan on tab_01_1  (cost=78.60..112.50 rows=1130 width=40) (actual time=0.026..0.034 rows=7 loops=1)

                                Output: tab_01_1.from_date, tab_01_1.code, tab_01_1.code_change

                                Buffers: shared hit=1

                                ->  WindowAgg  (cost=78.60..101.20 rows=1130 width=52) (actual time=0.025..0.032 rows=7 loops=1)

                                      Output: NULL::bigint, test_1.code, test_1.from_date, NULL::date, CASE WHEN (lag(test_1.code) OVER (?) = test_1.code) THEN 0 ELSE 1 END

                                      Buffers: shared hit=1

                                      ->  Sort  (cost=78.60..81.43 rows=1130 width=36) (actual time=0.013..0.014 rows=7 loops=1)

                                            Output: test_1.from_date, test_1.code

                                            Sort Key: test_1.from_date DESC

                                            Sort Method: quicksort  Memory: 25kB

                                            Buffers: shared hit=1

                                            ->  Seq Scan on public.test test_1  (cost=0.00..21.30 rows=1130 width=36) (actual time=0.005..0.006 rows=7 loops=1)

                                                  Output: test_1.from_date, test_1.code

                                                  Buffers: shared hit=1

Planning Time: 0.198 ms

Execution Time: 0.239 ms


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH start AS (
SELECT row_number, 
    code, 
    from_date, 
    to_date, 
    lag(from_date) OVER (ORDER BY row_number) AS next_from 
FROM test
WHERE code='A'
ORDER BY row_number
),
group_identify AS (
SELECT *,
    CASE WHEN row_number = 1 OR next_from - to_date = 1
    THEN 1
    ELSE 0
    END AS group_cnt
FROM    start
), 
group_enumerate AS (
SELECT *,
    row_number - sum(group_cnt) OVER (ORDER BY row_number) AS grpn
FROM group_identify
)
SELECT row_number, code, from_date, to_date
FROM group_enumerate
WHERE grpn=(select min(grpn) from group_enumerate)
ORDER BY row_number;


QUERY PLAN


Sort  (cost=24.85..24.86 rows=1 width=44) (actual time=0.053..0.054 rows=1 loops=1)

  Output: group_enumerate.row_number, group_enumerate.code, group_enumerate.from_date, group_enumerate.to_date

  Sort Key: group_enumerate.row_number

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=1

  CTE group_enumerate

    ->  WindowAgg  (cost=24.20..24.56 rows=6 width=60) (actual time=0.036..0.043 rows=4 loops=1)

          Output: test.row_number, test.code, test.from_date, test.to_date, (lag(test.from_date) OVER (?)), CASE WHEN ((test.row_number = 1) OR (((lag(test.from_date) OVER (?)) - test.to_date) = 1)) THEN 1 ELSE 0 END, (test.row_number - sum(CASE WHEN ((test.row_number = 1) OR (((lag(test.from_date) OVER (?)) - test.to_date) = 1)) THEN 1 ELSE 0 END) OVER (?))

          Buffers: shared hit=1

          ->  WindowAgg  (cost=24.20..24.31 rows=6 width=48) (actual time=0.025..0.029 rows=4 loops=1)

                Output: test.row_number, test.code, test.from_date, test.to_date, lag(test.from_date) OVER (?)

                Buffers: shared hit=1

                ->  Sort  (cost=24.20..24.22 rows=6 width=44) (actual time=0.014..0.015 rows=4 loops=1)

                      Output: test.row_number, test.code, test.from_date, test.to_date

                      Sort Key: test.row_number

                      Sort Method: quicksort  Memory: 25kB

                      Buffers: shared hit=1

                      ->  Seq Scan on public.test  (cost=0.00..24.12 rows=6 width=44) (actual time=0.010..0.011 rows=4 loops=1)

                            Output: test.row_number, test.code, test.from_date, test.to_date

                            Filter: (test.code = 'A'::text)

                            Rows Removed by Filter: 3

                            Buffers: shared hit=1

  InitPlan 2 (returns $1)

    ->  Aggregate  (cost=0.14..0.15 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

          Output: min(group_enumerate_1.grpn)

          ->  CTE Scan on group_enumerate group_enumerate_1  (cost=0.00..0.12 rows=6 width=8) (actual time=0.000..0.008 rows=4 loops=1)

                Output: group_enumerate_1.row_number, group_enumerate_1.code, group_enumerate_1.from_date, group_enumerate_1.to_date, group_enumerate_1.next_from, group_enumerate_1.group_cnt, group_enumerate_1.grpn

  ->  CTE Scan on group_enumerate  (cost=0.00..0.14 rows=1 width=44) (actual time=0.050..0.051 rows=1 loops=1)

        Output: group_enumerate.row_number, group_enumerate.code, group_enumerate.from_date, group_enumerate.to_date

        Filter: (group_enumerate.grpn = $1)

        Rows Removed by Filter: 3

        Buffers: shared hit=1

Planning Time: 0.132 ms

Execution Time: 0.093 ms
row_number	code	from_date	to_date
72	B	2021-09-24	null
45	B	2021-09-16	2021-09-23
645	A	2021-09-07	2021-09-15
25	A	2021-04-01	2021-09-06
45	B	2021-03-13	2021-03-31
55	A	2021-01-13	2021-03-12
5	A	2021-01-01	2021-01-12