Postgres 9.5

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?.
CREATE TABLE test (
  colony INTEGER,
  date_check date,
  protection text
);

INSERT INTO test
  (colony, date_check, protection)
VALUES
  (7, '2019-10-12', 'Y'),
  (7, '2019-10-13', 'Y'),
  (7, '2019-10-14', 'Y'),
  (7, '2019-10-15', 'Y'),
  (7, '2019-10-16', 'Y'),
  (7, '2019-10-17', 'Y'),
  (7, '2019-10-20', 'N'),
  (7, '2019-10-21', 'N'),
  (7, '2019-10-22', 'N'),
  (7, '2019-10-23', 'Y'),
  (7, '2019-10-24', 'Y'),
  (7, '2019-10-25', 'Y'),
  (7, '2019-10-26', 'Y'),
  (7, '2019-10-27', 'N'),
  (7, '2019-11-01', 'Y'),
  (7, '2019-11-04', 'Y'),
  (7, '2019-11-05', 'N'),
  (2, '2019-10-26', 'N'),
  (2, '2019-10-27', 'N'),
  (2, '2019-11-01', 'Y'),
  (2, '2019-11-03', 'Y'),
  (2, '2019-11-05', 'N');
22 rows affected

CREATE INDEX test_date_check_ix ON test USING BTREE (date_check ASC);
SELECT colony, date_check, protection
FROM  test
ORDER by colony, date_check;


colony
date_check
protection


2
2019-10-26
N

2
2019-10-27
N

2
2019-11-01
Y

2
2019-11-03
Y

2
2019-11-05
N

7
2019-10-12
Y

7
2019-10-13
Y

7
2019-10-14
Y

7
2019-10-15
Y

7
2019-10-16
Y

7
2019-10-17
Y

7
2019-10-20
N

7
2019-10-21
N

7
2019-10-22
N

7
2019-10-23
Y

7
2019-10-24
Y

7
2019-10-25
Y

7
2019-10-26
Y

7
2019-10-27
N

7
2019-11-01
Y

7
2019-11-04
Y

7
2019-11-05
N


CREATE TABLE results (
  colony INTEGER,
  date_in date,
  date_out date
);

INSERT INTO results
  (colony, date_in, date_out )
VALUES
(2,'2019-11-01','2019-11-03'),
(7,'2019-10-12','2019-10-17'),
(7,'2019-10-23','2019-10-26'),
(7,'2019-11-01','2019-11-04');

SELECT * from results;
4 rows affected



colony
date_in
date_out


2
2019-11-01
2019-11-03

7
2019-10-12
2019-10-17

7
2019-10-23
2019-10-26

7
2019-11-01
2019-11-04


SELECT colony, MIN(date_check), MAX(date_check)
FROM test
WHERE protection = 'Y'
GROUP BY colony
ORDER BY colony;


colony
min
max


2
2019-11-01
2019-11-03

7
2019-10-12
2019-11-04


SELECT 
  colony, 
  protection, 
  date_check,
  CASE 
    WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                   ORDER BY date_check) != protection) THEN 1
    ELSE 0
  END AS change 
FROM test;


colony
protection
date_check
change


2
N
2019-10-26
0

2
N
2019-10-27
0

2
Y
2019-11-01
1

2
Y
2019-11-03
0

2
N
2019-11-05
1

7
Y
2019-10-12
0

7
Y
2019-10-13
0

7
Y
2019-10-14
0

7
Y
2019-10-15
0

7
Y
2019-10-16
0

7
Y
2019-10-17
0

7
N
2019-10-20
1

7
N
2019-10-21
0

7
N
2019-10-22
0

7
Y
2019-10-23
1

7
Y
2019-10-24
0

7
Y
2019-10-25
0

7
Y
2019-10-26
0

7
N
2019-10-27
1

7
Y
2019-11-01
1

7
Y
2019-11-04
0

7
N
2019-11-05
1


SELECT 
  colony,
  date_check, 
  SUM(change) OVER (PARTITION BY colony ORDER BY date_check) AS sc
FROM
(
  SELECT 
    colony, 
    protection, 
    date_check,
    CASE 
      WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                     ORDER BY date_check) != protection) THEN 1
      ELSE 0
    END AS change 
  FROM test
) AS tab
WHERE protection = 'Y'  -- test what happens when we comment out this line...
ORDER BY colony, date_check;


colony
date_check
sc


2
2019-11-01
1

2
2019-11-03
1

7
2019-10-12
0

7
2019-10-13
0

7
2019-10-14
0

7
2019-10-15
0

7
2019-10-16
0

7
2019-10-17
0

7
2019-10-23
1

7
2019-10-24
1

7
2019-10-25
1

7
2019-10-26
1

7
2019-11-01
2

7
2019-11-04
2


SELECT 
  colony,
  MIN(date_check) AS "Date in",
  MAX(date_check) AS "Date out"
  , sc  -- not strictly necessary here in the SELECT - illustrative!
FROM
(
  SELECT 
    colony,
    date_check, 
    SUM(change) OVER (PARTITION BY colony ORDER BY date_check) AS sc
  FROM
  (
    SELECT 
      colony, 
      protection, 
      date_check,
      CASE 
        WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                       ORDER BY date_check) != protection) THEN 1
        ELSE 0
      END AS change 
    FROM test
  ) AS tab1
  WHERE protection = 'Y'
  ORDER BY colony, date_check
) AS tab2
GROUP BY colony, sc   -- sc not necessary in the SELECT but 
ORDER BY colony, sc;  -- it is required in the   GROUP BY - test!!!


colony
Date in
Date out
sc


2
2019-11-01
2019-11-03
1

7
2019-10-12
2019-10-17
0

7
2019-10-23
2019-10-26
1

7
2019-11-01
2019-11-04
2


SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check < t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check < t3.date_check
                                      AND t3.date_check < t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;


colony
date_in
date_out


2
2019-11-01
2019-11-03

7
2019-10-12
2019-10-17

7
2019-10-23
2019-10-26

7
2019-11-01
2019-11-04


WITH cte1 AS (SELECT *, 
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;


colony
date_in
date_out


2
2019-11-01
2019-11-03

7
2019-10-12
2019-10-17

7
2019-10-23
2019-10-26

7
2019-11-01
2019-11-04


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check < t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check < t3.date_check
                                      AND t3.date_check < t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;


QUERY PLAN


Sort  (cost=4.18..4.18 rows=1 width=12) (actual time=0.364..0.365 rows=4 loops=1)

  Sort Key: t1.colony, (min(t1.date_check))

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=16

  ->  HashAggregate  (cost=4.16..4.17 rows=1 width=12) (actual time=0.347..0.347 rows=4 loops=1)

        Group Key: t1.colony, max(t2.date_check)

        Buffers: shared hit=16

        ->  HashAggregate  (cost=4.13..4.14 rows=1 width=12) (actual time=0.339..0.341 rows=10 loops=1)

              Group Key: t1.colony, t1.date_check

              Buffers: shared hit=16

              ->  Hash Anti Join  (cost=1.54..4.12 rows=1 width=12) (actual time=0.101..0.325 rows=23 loops=1)

                    Hash Cond: (t1.colony = t3.colony)

                    Join Filter: ((t1.date_check < t3.date_check) AND (t3.date_check < t2.date_check))

                    Rows Removed by Join Filter: 181

                    Buffers: shared hit=16

                    ->  Nested Loop  (cost=0.00..2.56 rows=1 width=12) (actual time=0.047..0.211 rows=67 loops=1)

                          Join Filter: ((t1.date_check < t2.date_check) AND (t1.colony = t2.colony))

                          Rows Removed by Join Filter: 129

                          Buffers: shared hit=15

                          ->  Seq Scan on test t1  (cost=0.00..1.27 rows=1 width=8) (actual time=0.013..0.017 rows=14 loops=1)

                                Filter: (protection = 'Y'::text)

                                Rows Removed by Filter: 8

                                Buffers: shared hit=1

                          ->  Seq Scan on test t2  (cost=0.00..1.27 rows=1 width=8) (actual time=0.003..0.007 rows=14 loops=14)

                                Filter: (protection = 'Y'::text)

                                Rows Removed by Filter: 8

                                Buffers: shared hit=14

                    ->  Hash  (cost=1.27..1.27 rows=21 width=8) (actual time=0.023..0.023 rows=8 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          Buffers: shared hit=1

                          ->  Seq Scan on test t3  (cost=0.00..1.27 rows=21 width=8) (actual time=0.012..0.014 rows=8 loops=1)

                                Filter: (protection <> 'Y'::text)

                                Rows Removed by Filter: 14

                                Buffers: shared hit=1

Planning time: 0.268 ms

Execution time: 0.477 ms


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
WITH cte1 AS (SELECT *, 
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;


QUERY PLAN


Sort  (cost=4.32..4.33 rows=1 width=16) (actual time=0.191..0.191 rows=4 loops=1)

  Sort Key: cte2.colony, (min(cte2.date_check))

  Sort Method: quicksort  Memory: 25kB

  Buffers: shared hit=1

  CTE cte1

    ->  WindowAgg  (cost=1.71..2.43 rows=22 width=40) (actual time=0.052..0.080 rows=22 loops=1)

          Buffers: shared hit=1

          ->  Sort  (cost=1.71..1.77 rows=22 width=40) (actual time=0.025..0.027 rows=22 loops=1)

                Sort Key: test.colony, test.date_check

                Sort Method: quicksort  Memory: 26kB

                Buffers: shared hit=1

                ->  Seq Scan on test  (cost=0.00..1.22 rows=22 width=40) (actual time=0.005..0.006 rows=22 loops=1)

                      Buffers: shared hit=1

  CTE cte2

    ->  WindowAgg  (cost=0.93..1.37 rows=22 width=48) (actual time=0.113..0.133 rows=22 loops=1)

          Buffers: shared hit=1

          ->  Sort  (cost=0.93..0.99 rows=22 width=48) (actual time=0.103..0.104 rows=22 loops=1)

                Sort Key: cte1.colony, cte1.date_check

                Sort Method: quicksort  Memory: 26kB

                Buffers: shared hit=1

                ->  CTE Scan on cte1  (cost=0.00..0.44 rows=22 width=48) (actual time=0.054..0.088 rows=22 loops=1)

                      Buffers: shared hit=1

  ->  HashAggregate  (cost=0.51..0.52 rows=1 width=16) (actual time=0.172..0.174 rows=4 loops=1)

        Group Key: cte2.colony, cte2.group_no

        Buffers: shared hit=1

        ->  CTE Scan on cte2  (cost=0.00..0.49 rows=1 width=16) (actual time=0.132..0.160 rows=14 loops=1)

              Filter: (protection = 'Y'::text)

              Rows Removed by Filter: 8

              Buffers: shared hit=1

Planning time: 0.138 ms

Execution time: 0.280 ms


EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT 
  colony,
  MIN(date_check) AS "Date in",
  MAX(date_check) AS "Date out"
  , sc  -- not strictly necessary here in the SELECT - illustrative!
FROM
(
  SELECT 
    colony,
    date_check, 
    SUM(change) OVER (PARTITION BY colony ORDER BY date_check) AS sc
  FROM
  (
    SELECT 
      colony, 
      protection, 
      date_check,
      CASE 
        WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                       ORDER BY date_check) != protection) THEN 1
        ELSE 0
      END AS change 
    FROM test
  ) AS tab1
  WHERE protection = 'Y'
  ORDER BY colony, date_check
) AS tab2
GROUP BY colony, sc   -- sc not necessary in the SELECT but 
ORDER BY colony, sc;  -- it is required in the   GROUP BY - test!!!


QUERY PLAN


GroupAggregate  (cost=2.52..2.54 rows=1 width=16) (actual time=0.076..0.081 rows=4 loops=1)

  Group Key: tab2.colony, tab2.sc

  Buffers: shared hit=1

  ->  Sort  (cost=2.52..2.52 rows=1 width=16) (actual time=0.073..0.073 rows=14 loops=1)

        Sort Key: tab2.colony, tab2.sc

        Sort Method: quicksort  Memory: 26kB

        Buffers: shared hit=1

        ->  Subquery Scan on tab2  (cost=1.71..2.51 rows=1 width=16) (actual time=0.031..0.067 rows=14 loops=1)

              Buffers: shared hit=1

              ->  WindowAgg  (cost=1.71..2.50 rows=1 width=12) (actual time=0.031..0.066 rows=14 loops=1)

                    Buffers: shared hit=1

                    ->  Subquery Scan on tab1  (cost=1.71..2.48 rows=1 width=12) (actual time=0.026..0.048 rows=14 loops=1)

                          Filter: (tab1.protection = 'Y'::text)

                          Rows Removed by Filter: 8

                          Buffers: shared hit=1

                          ->  WindowAgg  (cost=1.71..2.21 rows=22 width=40) (actual time=0.020..0.041 rows=22 loops=1)

                                Buffers: shared hit=1

                                ->  Sort  (cost=1.71..1.77 rows=22 width=40) (actual time=0.015..0.019 rows=22 loops=1)

                                      Sort Key: test.colony, test.date_check

                                      Sort Method: quicksort  Memory: 26kB

                                      Buffers: shared hit=1

                                      ->  Seq Scan on test  (cost=0.00..1.22 rows=22 width=40) (actual time=0.004..0.009 rows=22 loops=1)

                                            Buffers: shared hit=1

Planning time: 0.112 ms

Execution time: 0.134 ms
colony	date_check	protection
2	2019-10-26	N
2	2019-10-27	N
2	2019-11-01	Y
2	2019-11-03	Y
2	2019-11-05	N
7	2019-10-12	Y
7	2019-10-13	Y
7	2019-10-14	Y
7	2019-10-15	Y
7	2019-10-16	Y
7	2019-10-17	Y
7	2019-10-20	N
7	2019-10-21	N
7	2019-10-22	N
7	2019-10-23	Y
7	2019-10-24	Y
7	2019-10-25	Y
7	2019-10-26	Y
7	2019-10-27	N
7	2019-11-01	Y
7	2019-11-04	Y
7	2019-11-05	N