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 polls (id INTEGER, title TEXT, owner_username TEXT);
CREATE TABLE options (id INTEGER PRIMARY KEY, option_text TEXT, poll_id INTEGER);
CREATE TABLE votes (username TEXT, option_id INTEGER, FOREIGN KEY(option_id) REFERENCES options(id));
INSERT INTO polls VALUES (1, 'Flask vs. Django', 'jose');
INSERT INTO polls VALUES (2, 'Python vs. Java', 'rolf');
INSERT INTO polls VALUES (3, 'Windows vs. Mac', 'bob');
INSERT INTO options VALUES (1, 'Flask', 1);
INSERT INTO options VALUES (2, 'Django', 1);
INSERT INTO options VALUES (3, 'It Depends', 1);
INSERT INTO options VALUES (4, 'Python', 2);
INSERT INTO options VALUES (5, 'Java', 2);
INSERT INTO options VALUES (6, 'Windows', 3);
INSERT INTO options VALUES (7, 'Mac', 3);
INSERT INTO votes VALUES ('jose', 1);
INSERT INTO votes VALUES ('charlie', 1);
INSERT INTO votes VALUES ('ammar', 1);
INSERT INTO votes VALUES ('rolf', 2);
INSERT INTO votes VALUES ('bob', 2);
INSERT INTO votes VALUES ('anne', 4);
INSERT INTO votes VALUES ('eric', 4);
INSERT INTO votes VALUES ('jose', 4);
INSERT INTO votes VALUES ('charlie', 4);
INSERT INTO votes VALUES ('ammar', 4);
INSERT INTO votes VALUES ('rolf', 4);
INSERT INTO votes VALUES ('bob', 4);
INSERT INTO votes VALUES ('anne', 5);
INSERT INTO votes VALUES ('eric', 5);
INSERT INTO votes VALUES ('bob', 4);
INSERT INTO votes VALUES ('anne', 6);
INSERT INTO votes VALUES ('eric', 7);
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
SELECT
polls.title,
COUNT(votes) as vote_count,
RANK() OVER(ORDER BY COUNT(votes) DESC)
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title
ORDER BY vote_count ASC;
| title | vote_count | rank |
|---|---|---|
| Windows vs. Mac | 2 | 3 |
| Flask vs. Django | 5 | 2 |
| Python vs. Java | 10 | 1 |
SELECT 3