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 car
(
  seq INTEGER, 
  name VARCHAR(8), 
  "date" DATE
);
INSERT INTO car (name, "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 cars 
(
  id INTEGER NOT NULL,
  car TEXT,
  d DATE
);
INSERT INTO cars 
SELECT 
  ROW_NUMBER() OVER (ORDER BY "date", name),
  name, "date"
FROM car;
23 rows affected

SELECT * FROM cars ORDER BY id;


id
car
d


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


;WITH First (id, car, d, is_first, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            ,CASE WHEN ((LEAD(car,1) OVER (ORDER BY id) = car) AND (LAG(car,1) OVER (ORDER BY id) <> car OR LAG(car,1) OVER (ORDER BY id) IS NULL)) THEN 1 ELSE 0 END is_first
        FROM 
            cars
    ) t
    WHERE t.is_first = 1
),
Last (id, car, d, is_last, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            , CASE 
                WHEN (LEAD(car,1) OVER (ORDER BY id) <> car OR LEAD(car,1) OVER (ORDER BY id) IS NULL) AND (LAG(car,1) OVER (ORDER BY id) = car ) THEN 1 ELSE 0 END is_last
        FROM 
            cars
    ) t
    WHERE t.is_last = 1
)
SELECT
    c.car, COUNT(*) cnt, f.d min_date, l.d max_date
FROM
    First f
    LEFT JOIN Last l ON f.rn = l.rn
    LEFT JOIN cars c ON c.car = f.car AND c.id BETWEEN f.id AND l.id
GROUP BY 
    c.car, f.d, l.d, f.rn
ORDER BY 
    min_date, cnt DESC, car;


car
cnt
min_date
max_date


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)
WITH First (id, car, d, is_first, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            ,CASE WHEN ((LEAD(car,1) OVER (ORDER BY id) = car) AND (LAG(car,1) OVER (ORDER BY id) <> car OR LAG(car,1) OVER (ORDER BY id) IS NULL)) THEN 1 ELSE 0 END is_first
        FROM 
            cars
    ) t
    WHERE t.is_first = 1
),
Last (id, car, d, is_last, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            , CASE 
                WHEN (LEAD(car,1) OVER (ORDER BY id) <> car OR LEAD(car,1) OVER (ORDER BY id) IS NULL) AND (LAG(car,1) OVER (ORDER BY id) = car ) THEN 1 ELSE 0 END is_last
        FROM 
            cars
    ) t
    WHERE t.is_last = 1
)
SELECT
    c.car, COUNT(*) cnt, f.d min_date, l.d max_date
FROM
    First f
    LEFT JOIN Last l ON f.rn = l.rn
    LEFT JOIN cars c ON c.car = f.car AND c.id BETWEEN f.id AND l.id
GROUP BY 
    c.car, f.d, l.d, f.rn
ORDER BY 
    min_date, cnt DESC, car;


QUERY PLAN


Sort  (cost=285.02..285.04 rows=6 width=56) (actual time=0.274..0.278 rows=4 loops=1)

  Output: c.car, (count(*)), t.d, l.d, (row_number() OVER (?))

  Sort Key: t.d, (count(*)) DESC, c.car

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=3

  ->  GroupAggregate  (cost=284.79..284.94 rows=6 width=56) (actual time=0.261..0.268 rows=4 loops=1)

        Output: c.car, count(*), t.d, l.d, (row_number() OVER (?))

        Group Key: c.car, t.d, l.d, (row_number() OVER (?))

        Buffers: shared hit=3

        ->  Sort  (cost=284.79..284.81 rows=6 width=48) (actual time=0.250..0.254 rows=12 loops=1)

              Output: c.car, t.d, l.d, (row_number() OVER (?))

              Sort Key: c.car, t.d, l.d, (row_number() OVER (?))

              Sort Method: quicksort  Memory: 25kB

              Buffers: shared hit=3

              ->  Hash Right Join  (cost=257.68..284.72 rows=6 width=48) (actual time=0.223..0.239 rows=12 loops=1)

                    Output: c.car, t.d, l.d, (row_number() OVER (?))

                    Hash Cond: (c.car = t.car)

                    Join Filter: ((c.id >= t.id) AND (c.id <= l.id))

                    Rows Removed by Join Filter: 16

                    Buffers: shared hit=3

                    ->  Seq Scan on public.cars c  (cost=0.00..22.00 rows=1200 width=36) (actual time=0.006..0.008 rows=23 loops=1)

                          Output: c.id, c.car, c.d

                          Buffers: shared hit=1

                    ->  Hash  (cost=257.60..257.60 rows=6 width=56) (actual time=0.199..0.201 rows=4 loops=1)

                          Output: t.d, (row_number() OVER (?)), t.car, t.id, l.d, l.id

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          Buffers: shared hit=2

                          ->  Nested Loop Left Join  (cost=166.75..257.60 rows=6 width=56) (actual time=0.146..0.194 rows=4 loops=1)

                                Output: t.d, (row_number() OVER (?)), t.car, t.id, l.d, l.id

                                Join Filter: ((row_number() OVER (?)) = l.rn)

                                Rows Removed by Join Filter: 12

                                Buffers: shared hit=2

                                ->  WindowAgg  (cost=83.37..128.46 rows=6 width=52) (actual time=0.077..0.097 rows=4 loops=1)

                                      Output: t.id, t.car, t.d, NULL::integer, row_number() OVER (?)

                                      Buffers: shared hit=1

                                      ->  Subquery Scan on t  (cost=83.37..128.37 rows=6 width=40) (actual time=0.061..0.086 rows=4 loops=1)

                                            Output: t.id, t.car, t.d

                                            Filter: (t.is_first = 1)

                                            Rows Removed by Filter: 19

                                            Buffers: shared hit=1

                                            ->  WindowAgg  (cost=83.37..113.37 rows=1200 width=44) (actual time=0.052..0.081 rows=23 loops=1)

                                                  Output: cars.id, cars.car, cars.d, CASE WHEN ((lead(cars.car, 1) OVER (?) = cars.car) AND ((lag(cars.car, 1) OVER (?) <> cars.car) OR (lag(cars.car, 1) OVER (?) IS NULL))) THEN 1 ELSE 0 END

                                                  Buffers: shared hit=1

                                                  ->  Sort  (cost=83.37..86.37 rows=1200 width=40) (actual time=0.023..0.026 rows=23 loops=1)

                                                        Output: cars.id, cars.car, cars.d

                                                        Sort Key: cars.id

                                                        Sort Method: quicksort  Memory: 26kB

                                                        Buffers: shared hit=1

                                                        ->  Seq Scan on public.cars  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.011..0.013 rows=23 loops=1)

                                                              Output: cars.id, cars.car, cars.d

                                                              Buffers: shared hit=1

                                ->  Materialize  (cost=83.37..128.55 rows=6 width=16) (actual time=0.017..0.023 rows=4 loops=4)

                                      Output: l.d, l.rn, l.id

                                      Buffers: shared hit=1

                                      ->  Subquery Scan on l  (cost=83.37..128.52 rows=6 width=16) (actual time=0.056..0.077 rows=4 loops=1)

                                            Output: l.d, l.rn, l.id

                                            Buffers: shared hit=1

                                            ->  WindowAgg  (cost=83.37..128.46 rows=6 width=52) (actual time=0.056..0.076 rows=4 loops=1)

                                                  Output: t_1.id, NULL::text, t_1.d, NULL::integer, row_number() OVER (?)

                                                  Buffers: shared hit=1

                                                  ->  Subquery Scan on t_1  (cost=83.37..128.37 rows=6 width=8) (actual time=0.045..0.066 rows=4 loops=1)

                                                        Output: t_1.id, t_1.d

                                                        Filter: (t_1.is_last = 1)

                                                        Rows Removed by Filter: 19

                                                        Buffers: shared hit=1

                                                        ->  WindowAgg  (cost=83.37..113.37 rows=1200 width=44) (actual time=0.034..0.063 rows=23 loops=1)

                                                              Output: cars_1.id, NULL::text, cars_1.d, CASE WHEN (((lead(cars_1.car, 1) OVER (?) <> cars_1.car) OR (lead(cars_1.car, 1) OVER (?) IS NULL)) AND (lag(cars_1.car, 1) OVER (?) = cars_1.car)) THEN 1 ELSE 0 END

                                                              Buffers: shared hit=1

                                                              ->  Sort  (cost=83.37..86.37 rows=1200 width=40) (actual time=0.025..0.027 rows=23 loops=1)

                                                                    Output: cars_1.id, cars_1.d, cars_1.car

                                                                    Sort Key: cars_1.id

                                                                    Sort Method: quicksort  Memory: 26kB

                                                                    Buffers: shared hit=1

                                                                    ->  Seq Scan on public.cars cars_1  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.010..0.014 rows=23 loops=1)

                                                                          Output: cars_1.id, cars_1.d, cars_1.car

                                                                          Buffers: shared hit=1

Planning Time: 0.331 ms

Execution Time: 0.451 ms
id	car	d
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