Postgres 13

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 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE users
(
  user_id INTEGER NOT NULL PRIMARY KEY,
  u_date  DATE NOT NULL,
  country VARCHAR (100) NOT NULL
);
CREATE TABLE submissions
(
  submission_id   INTEGER PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  submission_date DATE,
  category        VARCHAR (100) NOT NULL,
  CONSTRAINT sub_user_fk FOREIGN KEY (user_id) REFERENCES users (user_id)
);
INSERT INTO users VALUES
(123, '2022-12-01', 'USA'),
(124, '2022-12-01', 'Ireland'),
(125, '2022-12-01', 'Japan');
3 rows affected

INSERT INTO submissions VALUES
(1, 123, '2022-12-02', 'business'),
(2, 123, '2022-12-02', 'business'),
(3, 123, '2022-12-02', 'tourism'),
(4, 123, '2022-12-02', 'tourism'),
(5, 123, '2022-12-02', 'tourism'),
(6, 124, '2022-12-02', 'business'),
(7, 124, '2022-12-02', 'tourism');

--
-- Note: no records in submissions for user 125
--
7 rows affected

SELECT 
  u.user_id, u.country, s.category, 
  COUNT(s.category) AS cat_cnt
FROM
  users u
INNER JOIN
  submissions s
  ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;


user_id
country
category
cat_cnt


123
USA
tourism
3

123
USA
business
2

124
Ireland
tourism
1

124
Ireland
business
1


SELECT 
  u.user_id, u.country, COALESCE(s.category, '------'), 
  COUNT(s.category) AS cat_cnt
FROM
  users u
LEFT JOIN
  submissions s
  ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;


user_id
country
coalesce
cat_cnt


123
USA
tourism
3

123
USA
business
2

124
Ireland
tourism
1

124
Ireland
business
1

125
Japan
------
0

user_id	country	category	cat_cnt
123	USA	tourism	3
123	USA	business	2
124	Ireland	tourism	1
124	Ireland	business	1