Postgres 9.6

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 9.6.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
),
cte2 AS
(
  SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
  FROM cte1
  WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE
),
cte3 AS
(
  SELECT 
    '(' || 
    STRING_AGG((d1::TEXT) || '-' || (d2::TEXT) || ',', E'\n') 
    || ')' 
    AS date_string1
  FROM cte2
)
SELECT REPLACE(date_string1, ',)', ')') AS date_string FROM cte3;
 


date_string


(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28)


WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
)
SELECT * FROM cte1;


d1


2020-06-01

2020-06-08

2020-06-15

2020-06-22

2020-06-29


WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
),
cte2 AS
(
  SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
  FROM cte1
  WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE
)
SELECT * FROM cte2;


d1
d2


2020-06-01
2020-06-07

2020-06-08
2020-06-14

2020-06-15
2020-06-21

2020-06-22
2020-06-28


WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
),
cte2 AS
(
  SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
  FROM cte1
  WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE
),
cte3 AS
(
  SELECT 
    '(' || 
    STRING_AGG((d1::TEXT) || '-' || (d2::TEXT) || ',', E'\n') 
    || ')' 
    AS date_string1
  FROM cte2
)
SELECT * FROM cte3;


date_string1


(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28,)


WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
),
cte2 AS
(
  SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
  FROM cte1
  WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE
),
cte3 AS
(
  SELECT 
    '(' || 
    STRING_AGG((d1::TEXT) || '-' || (d2::TEXT) || ',', E'\n') 
    || ')' 
    AS date_string1
  FROM cte2
)
SELECT REPLACE(date_string1, ',)', ')') AS date_string FROM cte3;


date_string


(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28)

d1	d2
2020-06-01	2020-06-07
2020-06-08	2020-06-14
2020-06-15	2020-06-21
2020-06-22	2020-06-28