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_1
(
  name   VARCHAR (8), 
  p_date DATE
);
 
   
INSERT INTO test_1 (name, p_date)
VALUES 
('MERCEDES', '2018-01-01'), 
('SEAT', '2018-02-01'), 
('MERCEDES', '2018-04-01'),
('BMW', '2018-01-01'), 
('MERCEDES', '2018-01-01'), 
('MERCEDES', '2018-01-05'),
('MERCEDES', '2018-01-09'), 
('BMW', '2017-12-01'), 
('BMW', '2017-12-05'),
('SEAT', '2017-12-01'), 
('BMW', '2017-01-01'), 
('AUDI', '2017-12-01'),
('BMW', '2017-12-29'), 
('BMW', '2018-01-01'), 
(NULL, '2018-03-01'),
('SEAT', '2018-04-01'), 
('SEAT', '2018-05-01'), 
('SEAT', '2018-07-01'),
('BMW', '2018-01-01'), 
(NULL, '2018-01-01'), 
('MERCEDES', '2018-03-01'),    
('MERCEDES', '2018-01-05'), 
('MERCEDES', '2018-04-09');
23 rows affected

CREATE TABLE test
(
  rn     INTEGER NOT NULL,
  name   VARCHAR (8), 
  p_date DATE NOT NULL
);
 
INSERT INTO test 
SELECT ROW_NUMBER() OVER (ORDER BY p_date, name),
  name, p_date
FROM test_1;
23 rows affected

SELECT * FROM test ORDER BY p_date, name;

-- note the sequenct of 5 BMWs!


rn
name
p_date


1
BMW
2017-01-01

2
AUDI
2017-12-01

3
BMW
2017-12-01

4
SEAT
2017-12-01

5
BMW
2017-12-05

6
BMW
2017-12-29

7
BMW
2018-01-01

8
BMW
2018-01-01

9
BMW
2018-01-01

10
MERCEDES
2018-01-01

11
MERCEDES
2018-01-01

12
null
2018-01-01

13
MERCEDES
2018-01-05

14
MERCEDES
2018-01-05

15
MERCEDES
2018-01-09

16
SEAT
2018-02-01

17
MERCEDES
2018-03-01

18
null
2018-03-01

19
MERCEDES
2018-04-01

20
SEAT
2018-04-01

21
MERCEDES
2018-04-09

22
SEAT
2018-05-01

23
SEAT
2018-07-01


SELECT 
  rn,
  name, LAG(name) OVER (ORDER BY p_date, name), 
  CASE 
    WHEN name = LAG(name) OVER (ORDER BY p_date, name) THEN 0
    ELSE 1
  END AS c_diff,
  p_date
FROM test
ORDER BY p_date;


rn
name
lag
c_diff
p_date


1
BMW
null
1
2017-01-01

2
AUDI
BMW
1
2017-12-01

3
BMW
AUDI
1
2017-12-01

4
SEAT
BMW
1
2017-12-01

5
BMW
SEAT
1
2017-12-05

6
BMW
BMW
0
2017-12-29

7
BMW
BMW
0
2018-01-01

8
BMW
BMW
0
2018-01-01

9
BMW
BMW
0
2018-01-01

10
MERCEDES
BMW
1
2018-01-01

11
MERCEDES
MERCEDES
0
2018-01-01

12
null
MERCEDES
1
2018-01-01

13
MERCEDES
null
1
2018-01-05

14
MERCEDES
MERCEDES
0
2018-01-05

15
MERCEDES
MERCEDES
0
2018-01-09

16
SEAT
MERCEDES
1
2018-02-01

17
MERCEDES
SEAT
1
2018-03-01

18
null
MERCEDES
1
2018-03-01

19
MERCEDES
null
1
2018-04-01

20
SEAT
MERCEDES
1
2018-04-01

21
MERCEDES
SEAT
1
2018-04-09

22
SEAT
MERCEDES
1
2018-05-01

23
SEAT
SEAT
0
2018-07-01


SELECT
  rn, name, lgn, c_diff,  
  SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
  p_date
FROM
(
  SELECT 
    rn,
    name, LAG(name) OVER (ORDER BY p_date) AS lgn, 
    CASE 
      WHEN name = LAG(name) OVER (ORDER BY p_date) THEN 0
      ELSE 1
    END AS c_diff,
    p_date
  FROM test
  ORDER BY p_date
) AS tab01
ORDER BY rn;


rn
name
lgn
c_diff
sd
p_date


1
BMW
null
1
1
2017-01-01

2
AUDI
BMW
1
2
2017-12-01

3
BMW
AUDI
1
3
2017-12-01

4
SEAT
BMW
1
4
2017-12-01

5
BMW
SEAT
1
5
2017-12-05

6
BMW
BMW
0
5
2017-12-29

7
BMW
BMW
0
5
2018-01-01

8
BMW
BMW
0
5
2018-01-01

9
BMW
BMW
0
5
2018-01-01

10
MERCEDES
BMW
1
6
2018-01-01

11
MERCEDES
MERCEDES
0
6
2018-01-01

12
null
MERCEDES
1
7
2018-01-01

13
MERCEDES
null
1
8
2018-01-05

14
MERCEDES
MERCEDES
0
8
2018-01-05

15
MERCEDES
MERCEDES
0
8
2018-01-09

16
SEAT
MERCEDES
1
9
2018-02-01

17
MERCEDES
SEAT
1
10
2018-03-01

18
null
MERCEDES
1
11
2018-03-01

19
MERCEDES
null
1
12
2018-04-01

20
SEAT
MERCEDES
1
13
2018-04-01

21
MERCEDES
SEAT
1
14
2018-04-09

22
SEAT
MERCEDES
1
15
2018-05-01

23
SEAT
SEAT
0
15
2018-07-01


SELECT
  *,  -- NOT good practice - SQL can be cleaned up here - left for demonstration
  FIRST_VALUE(p_date) OVER (PARTITION BY sd) AS fv_sd,
   LAST_VALUE(p_date) OVER (PARTITION BY sd) AS lv_sd,
   ROW_NUMBER() OVER (PARTITION BY sd ORDER BY rn) AS rn2  
FROM
(
  SELECT
    rn, name, lgn, c_diff,  
    SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
    p_date
  FROM
  (
    SELECT 
      rn,
      name, LAG(name) OVER (ORDER BY p_date, name) AS lgn, 
      CASE 
        WHEN name = LAG(name) OVER (ORDER BY p_date, name) THEN 0
        ELSE 1
      END AS c_diff,
      p_date
    FROM test
    ORDER BY p_date
  ) AS tab01
  ORDER BY rn
) AS tab02 
ORDER BY rn;


rn
name
lgn
c_diff
sd
p_date
fv_sd
lv_sd
rn2


1
BMW
null
1
1
2017-01-01
2017-01-01
2017-01-01
1

2
AUDI
BMW
1
2
2017-12-01
2017-12-01
2017-12-01
1

3
BMW
AUDI
1
3
2017-12-01
2017-12-01
2017-12-01
1

4
SEAT
BMW
1
4
2017-12-01
2017-12-01
2017-12-01
1

5
BMW
SEAT
1
5
2017-12-05
2017-12-05
2018-01-01
1

6
BMW
BMW
0
5
2017-12-29
2017-12-05
2018-01-01
2

7
BMW
BMW
0
5
2018-01-01
2017-12-05
2018-01-01
3

8
BMW
BMW
0
5
2018-01-01
2017-12-05
2018-01-01
4

9
BMW
BMW
0
5
2018-01-01
2017-12-05
2018-01-01
5

10
MERCEDES
BMW
1
6
2018-01-01
2018-01-01
2018-01-01
1

11
MERCEDES
MERCEDES
0
6
2018-01-01
2018-01-01
2018-01-01
2

12
null
MERCEDES
1
7
2018-01-01
2018-01-01
2018-01-01
1

13
MERCEDES
null
1
8
2018-01-05
2018-01-05
2018-01-09
1

14
MERCEDES
MERCEDES
0
8
2018-01-05
2018-01-05
2018-01-09
2

15
MERCEDES
MERCEDES
0
8
2018-01-09
2018-01-05
2018-01-09
3

16
SEAT
MERCEDES
1
9
2018-02-01
2018-02-01
2018-02-01
1

17
MERCEDES
SEAT
1
10
2018-03-01
2018-03-01
2018-03-01
1

18
null
MERCEDES
1
11
2018-03-01
2018-03-01
2018-03-01
1

19
MERCEDES
null
1
12
2018-04-01
2018-04-01
2018-04-01
1

20
SEAT
MERCEDES
1
13
2018-04-01
2018-04-01
2018-04-01
1

21
MERCEDES
SEAT
1
14
2018-04-09
2018-04-09
2018-04-09
1

22
SEAT
MERCEDES
1
15
2018-05-01
2018-05-01
2018-07-01
1

23
SEAT
SEAT
0
15
2018-07-01
2018-05-01
2018-07-01
2


SELECT 
  name, MAX(rn2) AS mrn2, fv_sd, lv_sd
FROM
(
  SELECT
    name, rn, p_date,  
    FIRST_VALUE(p_date) OVER (PARTITION BY sd) AS fv_sd,
     LAST_VALUE(p_date) OVER (PARTITION BY sd) AS lv_sd,
    ROW_NUMBER() OVER (PARTITION BY sd ORDER BY rn) AS rn2
  FROM
  (
    SELECT
      rn, name, lgn, c_diff,  
      SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
      p_date
    FROM
    (
      SELECT 
        rn,
        name, LAG(name) OVER (ORDER BY p_date, name) AS lgn, 
        CASE 
          WHEN name = LAG(name) OVER (ORDER BY p_date, name) THEN 0
          ELSE 1
        END AS c_diff,
        p_date
      FROM test
      ORDER BY p_date, name
    ) AS tab01
    ORDER BY rn
  ) AS tab02 
  ORDER BY rn
) AS tab03
GROUP BY name, fv_sd, lv_sd
HAVING MAX(rn2) > 1
ORDER BY fv_sd, mrn2 DESC, name;


name
mrn2
fv_sd
lv_sd


BMW
5
2017-12-05
2018-01-01

MERCEDES
2
2018-01-01
2018-01-01

MERCEDES
3
2018-01-05
2018-01-09

SEAT
2
2018-05-01
2018-07-01


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
  name, MAX(rn2) AS mrn2, fv_sd, lv_sd
FROM
(
  SELECT
    name, rn, p_date,  
    FIRST_VALUE(p_date) OVER (PARTITION BY sd) AS fv_sd,
     LAST_VALUE(p_date) OVER (PARTITION BY sd) AS lv_sd,
    ROW_NUMBER() OVER (PARTITION BY sd ORDER BY rn) AS rn2
  FROM
  (
    SELECT
      rn, name, lgn, c_diff,  
      SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
      p_date
    FROM
    (
      SELECT 
        ROW_NUMBER() OVER (ORDER BY p_date, name) AS rn,
        name, LAG(name) OVER (ORDER BY p_date, name) AS lgn, 
        CASE 
          WHEN name = LAG(name) OVER (ORDER BY p_date, name) THEN 0
          ELSE 1
        END AS c_diff,
        p_date
      FROM test
      ORDER BY p_date, name
    ) AS tab01
    ORDER BY rn
  ) AS tab02 
  ORDER BY rn
) AS tab03
GROUP BY name, fv_sd, lv_sd
HAVING MAX(rn2) > 1
ORDER BY fv_sd, mrn2 DESC, name;


QUERY PLAN


Sort  (cost=410.21..410.37 rows=67 width=50) (actual time=0.181..0.183 rows=4 loops=1)

  Output: tab02.name, (max((row_number() OVER (?)))), (first_value(tab02.p_date) OVER (?)), (last_value(tab02.p_date) OVER (?))

  Sort Key: (first_value(tab02.p_date) OVER (?)), (max((row_number() OVER (?)))) DESC, tab02.name

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=1

  ->  HashAggregate  (cost=405.67..408.17 rows=67 width=50) (actual time=0.175..0.179 rows=4 loops=1)

        Output: tab02.name, max((row_number() OVER (?))), (first_value(tab02.p_date) OVER (?)), (last_value(tab02.p_date) OVER (?))

        Group Key: tab02.name, (first_value(tab02.p_date) OVER (?)), (last_value(tab02.p_date) OVER (?))

        Filter: (max((row_number() OVER (?))) > 1)

        Rows Removed by Filter: 11

        Buffers: shared hit=1

        ->  Sort  (cost=376.67..379.57 rows=1160 width=70) (actual time=0.162..0.164 rows=23 loops=1)

              Output: tab02.name, tab02.rn, NULL::date, (first_value(tab02.p_date) OVER (?)), (last_value(tab02.p_date) OVER (?)), (row_number() OVER (?)), tab02.sd

              Sort Key: tab02.rn

              Sort Method: quicksort  Memory: 26kB

              Buffers: shared hit=1

              ->  WindowAgg  (cost=274.13..317.63 rows=1160 width=70) (actual time=0.113..0.151 rows=23 loops=1)

                    Output: tab02.name, tab02.rn, NULL::date, first_value(tab02.p_date) OVER (?), last_value(tab02.p_date) OVER (?), (row_number() OVER (?)), tab02.sd

                    Buffers: shared hit=1

                    ->  WindowAgg  (cost=274.13..297.33 rows=1160 width=62) (actual time=0.105..0.123 rows=23 loops=1)

                          Output: tab02.rn, tab02.sd, tab02.name, tab02.p_date, row_number() OVER (?)

                          Buffers: shared hit=1

                          ->  Sort  (cost=274.13..277.03 rows=1160 width=54) (actual time=0.103..0.106 rows=23 loops=1)

                                Output: tab02.rn, tab02.sd, tab02.name, tab02.p_date

                                Sort Key: tab02.sd, tab02.rn

                                Sort Method: quicksort  Memory: 26kB

                                Buffers: shared hit=1

                                ->  Subquery Scan on tab02  (cost=180.29..215.09 rows=1160 width=54) (actual time=0.075..0.095 rows=23 loops=1)

                                      Output: tab02.rn, tab02.sd, tab02.name, tab02.p_date

                                      Buffers: shared hit=1

                                      ->  WindowAgg  (cost=180.29..203.49 rows=1160 width=90) (actual time=0.074..0.092 rows=23 loops=1)

                                            Output: tab01.rn, tab01.name, NULL::character varying, NULL::integer, sum(tab01.c_diff) OVER (?), tab01.p_date

                                            Buffers: shared hit=1

                                            ->  Sort  (cost=180.29..183.19 rows=1160 width=50) (actual time=0.071..0.073 rows=23 loops=1)

                                                  Output: tab01.rn, tab01.name, tab01.c_diff, tab01.p_date

                                                  Sort Key: tab01.rn, tab01.name

                                                  Sort Method: quicksort  Memory: 26kB

                                                  Buffers: shared hit=1

                                                  ->  Subquery Scan on tab01  (cost=80.64..121.24 rows=1160 width=50) (actual time=0.039..0.062 rows=23 loops=1)

                                                        Output: tab01.rn, tab01.name, tab01.c_diff, tab01.p_date

                                                        Buffers: shared hit=1

                                                        ->  WindowAgg  (cost=80.64..109.64 rows=1160 width=82) (actual time=0.038..0.059 rows=23 loops=1)

                                                              Output: row_number() OVER (?), test.name, NULL::character varying, CASE WHEN ((test.name)::text = (lag(test.name) OVER (?))::text) THEN 0 ELSE 1 END, test.p_date

                                                              Buffers: shared hit=1

                                                              ->  Sort  (cost=80.64..83.54 rows=1160 width=38) (actual time=0.030..0.032 rows=23 loops=1)

                                                                    Output: test.name, test.p_date

                                                                    Sort Key: test.p_date, test.name

                                                                    Sort Method: quicksort  Memory: 26kB

                                                                    Buffers: shared hit=1

                                                                    ->  Seq Scan on public.test  (cost=0.00..21.60 rows=1160 width=38) (actual time=0.007..0.010 rows=23 loops=1)

                                                                          Output: test.name, test.p_date

                                                                          Buffers: shared hit=1

Planning Time: 0.182 ms

Execution Time: 0.252 ms


--
-- identical to SQL SErver query!
--

SELECT 
  name, MAX(rn2) AS mrn2, fv_sd, lv_sd
FROM
(
  SELECT
    name, rn, p_date,  
    FIRST_VALUE(p_date) OVER (PARTITION BY sd ORDER BY sd) AS fv_sd,
     LAST_VALUE(p_date) OVER (PARTITION BY sd ORDER BY sd) AS lv_sd,
    ROW_NUMBER() OVER (PARTITION BY sd ORDER BY rn) AS rn2
  FROM
  (
    SELECT
      rn, name, lgn, c_diff,  
      SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
      p_date
    FROM
    (
      SELECT 
        ROW_NUMBER() OVER (ORDER BY p_date, name) AS rn,
        name, LAG(name) OVER (ORDER BY p_date, name) AS lgn, 
        CASE 
          WHEN name = LAG(name) OVER (ORDER BY p_date, name) THEN 0
          ELSE 1
        END AS c_diff,
        p_date
      FROM test
      -- ORDER BY p_date, name
    ) AS tab01
    -- ORDER BY rn
  ) AS tab02 
  -- ORDER BY rn
) AS tab03
GROUP BY name, fv_sd, lv_sd
HAVING MAX(rn2) > 1
ORDER BY fv_sd, mrn2 DESC, name;


name
mrn2
fv_sd
lv_sd


BMW
5
2017-12-05
2018-01-01

MERCEDES
2
2018-01-01
2018-01-01

MERCEDES
3
2018-01-05
2018-01-09

SEAT
2
2018-05-01
2018-07-01
rn	name	p_date
1	BMW	2017-01-01
2	AUDI	2017-12-01
3	BMW	2017-12-01
4	SEAT	2017-12-01
5	BMW	2017-12-05
6	BMW	2017-12-29
7	BMW	2018-01-01
8	BMW	2018-01-01
9	BMW	2018-01-01
10	MERCEDES	2018-01-01
11	MERCEDES	2018-01-01
12	null	2018-01-01
13	MERCEDES	2018-01-05
14	MERCEDES	2018-01-05
15	MERCEDES	2018-01-09
16	SEAT	2018-02-01
17	MERCEDES	2018-03-01
18	null	2018-03-01
19	MERCEDES	2018-04-01
20	SEAT	2018-04-01
21	MERCEDES	2018-04-09
22	SEAT	2018-05-01
23	SEAT	2018-07-01