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?.

CREATE TABLE tablename(id int, author text, size int, file_ext text);
    
INSERT INTO tablename VALUES
    (1, 'a', 13661, 'python'),
    (1, 'a', 13513, 'cpp'),
    (1, 'a',  1211, 'non-code'),
    (2, 'b',  1019, 'python'),
    (2, 'b',  6881, 'cpp'),
    (2, 'b',  1525, 'python'),
    (2, 'b',  1208, 'non-code'),
    (3, 'c',  1039, 'python'),
    (3, 'c',  1299, 'cpp')
;

CREATE TABLE



INSERT 0 9


-- cross tabulation without crosstab()
SELECT id, author
     , sum(size) AS size
     , count(*) FILTER (WHERE file_ext = 'python')   AS python
     , count(*) FILTER (WHERE file_ext = 'cpp')      AS cpp
     , count(*) FILTER (WHERE file_ext = 'non-code') AS "non-code"
FROM   tablename
GROUP  BY id, author;


id
author
size
python
cpp
non-code


1
a
28385
1
1
1

2
b
10633
2
1
1

3
c
2338
1
1
0



SELECT 3


-- step 1
SELECT id, author
     , sum(size) AS size
     , file_ext
     , count(*) AS ct
FROM   tablename
GROUP  BY id, author, file_ext
ORDER  BY id, author, file_ext;


id
author
size
file_ext
ct


1
a
13513
cpp
1

1
a
1211
non-code
1

1
a
13661
python
1

2
b
6881
cpp
1

2
b
1208
non-code
1

2
b
2544
python
2

3
c
1299
cpp
1

3
c
1039
python
1



SELECT 8


-- step 2
SELECT id, author
     , sum(sum(size)) OVER (PARTITION BY id) AS size  -- !
     , file_ext
     , count(*) AS ct
FROM   tablename
GROUP  BY id, author, file_ext
ORDER  BY id, author, file_ext;


id
author
size
file_ext
ct


1
a
28385
cpp
1

1
a
28385
non-code
1

1
a
28385
python
1

2
b
10633
cpp
1

2
b
10633
non-code
1

2
b
10633
python
2

3
c
2338
cpp
1

3
c
2338
python
1



SELECT 8


-- crosstab()
SELECT id, author, size
     , COALESCE(python    , 0) AS python
     , COALESCE(cpp       , 0) AS cpp
     , COALESCE("non-code", 0) AS "non-code"
FROM   crosstab(
$$
SELECT id, author
     , sum(sum(size)) OVER (PARTITION BY id) AS size
     , file_ext
     , count(*) AS ct
FROM   tablename
GROUP  BY id, author, file_ext
ORDER  BY id, author, file_ext
$$
, $$VALUES ('python'), ('cpp'), ('non-code')$$
) AS (id int, author text, size numeric
    , python int, cpp int, "non-code" int);


id
author
size
python
cpp
non-code


1
a
28385
1
1
1

2
b
10633
2
1
1

3
c
2338
1
1
0



SELECT 3

id	author	size	file_ext	ct
1	a	13513	cpp	1
1	a	1211	non-code	1
1	a	13661	python	1
2	b	6881	cpp	1
2	b	1208	non-code	1
2	b	2544	python	2
3	c	1299	cpp	1
3	c	1039	python	1

id	author	size	file_ext	ct
1	a	28385	cpp	1
1	a	28385	non-code	1
1	a	28385	python	1
2	b	10633	cpp	1
2	b	10633	non-code	1
2	b	10633	python	2
3	c	2338	cpp	1
3	c	2338	python	1