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 test (id INT, val INT);
INSERT INTO test VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(2,5),
(2,6),
(2,7),
(2,8);
SELECT * FROM test;
8 rows affected
| id | val |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
SELECT id, AVG(val) AS average
FROM test
GROUP BY id;
| id | average |
|---|---|
| 2 | 6.5000000000000000 |
| 1 | 2.5000000000000000 |
SELECT id, AVG(val) AS average
FROM test
GROUP BY id
ORDER BY 1 - average;
ERROR: column "average" does not exist
LINE 4: ORDER BY 1 - average;
^
SELECT id, AVG(val) AS average
FROM test
GROUP BY id
ORDER BY 1 - AVG(val);
| id | average |
|---|---|
| 2 | 6.5000000000000000 |
| 1 | 2.5000000000000000 |