Postgres 11

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 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE ttable(id SERIAL NOT NULL PRIMARY KEY, txt TEXT, dtd TIMESTAMP, cnt INTEGER);

insert into ttable values (1,'Pen','2019-01-01',10), (2,'Pencil','2019-01-01',10),
  (3,'Pen','2019-01-02',20), (4,'Eraser','2019-01-02',1), (5,'Eraser','2019-01-03',1),
  (6,'Other1','2019-01-03',10), (7,'Other1','2019-01-04',10);
7 rows affected

-- SELECT * FROM ttable ORDER BY dtd, txt;
SELECT * FROM
(
  SELECT 'Eraser' AS txt UNION
  SELECT 'Pen'     AS txt UNION
  SELECT 'Pencil'  AS txt
) AS item


txt


Pen

Pencil

Eraser


WITH item AS
(
  SELECT txt FROM ttable WHERE txt IN ('Eraser', 'Pen', 'Pencil')
),
item_date AS
(
  SELECT DISTINCT i.txt, t.dtd FROM item i, ttable t
  ORDER BY dtd, txt
)
SELECT id.dtd, id.txt, COALESCE(t.cnt, 0) AS icnt 
FROM item_date id
LEFT JOIN ttable t
  ON id.txt = t.txt
  AND id.dtd = t.dtd
UNION
SELECT t.dtd, t.txt, t.cnt
FROM ttable t
WHERE t.txt NOT IN (SELECT txt FROM item)
ORDER BY dtd, txt
  
-- SELECT id.txt, id.dtd, t.id, t.txt, t.dtd, COALESCE(t.cnt, 0) AS icnt 


dtd
txt
icnt


2019-01-01 00:00:00
Eraser
0

2019-01-01 00:00:00
Pen
10

2019-01-01 00:00:00
Pencil
10

2019-01-02 00:00:00
Eraser
1

2019-01-02 00:00:00
Pen
20

2019-01-02 00:00:00
Pencil
0

2019-01-03 00:00:00
Eraser
1

2019-01-03 00:00:00
Other1
10

2019-01-03 00:00:00
Pen
0

2019-01-03 00:00:00
Pencil
0

2019-01-04 00:00:00
Eraser
0

2019-01-04 00:00:00
Other1
10

2019-01-04 00:00:00
Pen
0

2019-01-04 00:00:00
Pencil
0

dtd	txt	icnt
2019-01-01 00:00:00	Eraser	0
2019-01-01 00:00:00	Pen	10
2019-01-01 00:00:00	Pencil	10
2019-01-02 00:00:00	Eraser	1
2019-01-02 00:00:00	Pen	20
2019-01-02 00:00:00	Pencil	0
2019-01-03 00:00:00	Eraser	1
2019-01-03 00:00:00	Other1	10
2019-01-03 00:00:00	Pen	0
2019-01-03 00:00:00	Pencil	0
2019-01-04 00:00:00	Eraser	0
2019-01-04 00:00:00	Other1	10
2019-01-04 00:00:00	Pen	0
2019-01-04 00:00:00	Pencil	0