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 tbl (
id int PRIMARY KEY
, part int NOT NULL
, ballast text
);
INSERT INTO tbl
SELECT g, g/3000
, '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789' -- 80 char
FROM generate_series(1, 200000) g
ORDER BY random();
CREATE UNIQUE INDEX tbl_part_id_idx ON tbl (part, id);
DELETE FROM tbl WHERE random() > .9; -- some dead tuples
CREATE TABLE
INSERT 0 200000
CREATE INDEX
DELETE 19925
VACUUM ANALYZE tbl;
VACUUM
-- org asc (idx only)
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *, row_number() OVER (PARTITION BY part ORDER BY id) AS rn
FROM tbl
) sub
WHERE rn = 1;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=0.42..10307.80 rows=900 width=8) (actual time=0.067..122.395 rows=67 loops=1) |
| Filter: (sub.rn = 1) |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=0.42..8056.86 rows=180075 width=48) (actual time=0.065..112.954 rows=180075 loops=1) |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.038..32.832 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.258 ms |
| Execution Time: 122.500 ms |
EXPLAIN
-- org desc (Incremental Sort in pg 14+; additional sort step in pg 11)
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *, row_number() OVER (PARTITION BY part ORDER BY id DESC) AS rn
FROM tbl
) sub
WHERE rn = 1;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=23088.51..28940.94 rows=900 width=8) (actual time=92.483..205.751 rows=67 loops=1) |
| Filter: (sub.rn = 1) |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=23088.51..26690.01 rows=180075 width=48) (actual time=92.481..194.962 rows=180075 loops=1) |
| -> Sort (cost=23088.51..23538.69 rows=180075 width=8) (actual time=92.467..114.834 rows=180075 loops=1) |
| Sort Key: tbl.part, tbl.id DESC |
| Sort Method: external merge Disk: 3192kB |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.013..21.566 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.134 ms |
| Execution Time: 206.370 ms |
EXPLAIN
-- org desc, optmized with ROWS (Incremental Sort in pg 14+; additional sort step in pg 11)
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *, row_number() OVER (PARTITION BY part ORDER BY id DESC
ROWS UNBOUNDED PRECEDING) AS rn
FROM tbl
) sub
WHERE rn = 1;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=23088.51..28940.94 rows=900 width=8) (actual time=124.239..217.586 rows=67 loops=1) |
| Filter: (sub.rn = 1) |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=23088.51..26690.01 rows=180075 width=48) (actual time=124.236..207.409 rows=180075 loops=1) |
| -> Sort (cost=23088.51..23538.69 rows=180075 width=8) (actual time=124.225..146.241 rows=180075 loops=1) |
| Sort Key: tbl.part, tbl.id DESC |
| Sort Method: external merge Disk: 3192kB |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.024..36.271 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.316 ms |
| Execution Time: 218.170 ms |
EXPLAIN
-- Paul's query, adapted to get greatest id per group
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *
, CASE WHEN part = lag(part) OVER (ORDER BY part DESC, id DESC)
THEN 0
ELSE 1
END AS f
FROM tbl
) sub
WHERE sub.f = 1;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=0.42..10757.98 rows=900 width=8) (actual time=0.041..133.856 rows=67 loops=1) |
| Filter: (sub.f = 1) |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=0.42..8507.05 rows=180075 width=44) (actual time=0.040..123.854 rows=180075 loops=1) |
| -> Index Only Scan Backward using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.025..30.224 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.120 ms |
| Execution Time: 133.901 ms |
EXPLAIN
-- Paul's query, optimized (without index scan backward)
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *
, CASE WHEN part = lead(part) OVER (ORDER BY part, id ROWS UNBOUNDED PRECEDING)
THEN false
ELSE true END AS qualified
FROM tbl
) sub
WHERE qualified;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=0.42..10307.80 rows=90038 width=8) (actual time=1.675..108.241 rows=67 loops=1) |
| Filter: sub.qualified |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=0.42..8507.05 rows=180075 width=41) (actual time=0.019..99.756 rows=180075 loops=1) |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.014..27.201 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.107 ms |
| Execution Time: 108.298 ms |
EXPLAIN
-- DISTINCT ON is faster for few rows per group (seq scan)
EXPLAIN ANALYZE
SELECT DISTINCT ON (part) id, part
FROM tbl
ORDER BY part, id DESC
| QUERY PLAN |
|---|
| Unique (cost=23088.51..23988.88 rows=67 width=8) (actual time=89.847..119.706 rows=67 loops=1) |
| -> Sort (cost=23088.51..23538.69 rows=180075 width=8) (actual time=89.845..107.966 rows=180075 loops=1) |
| Sort Key: part, id DESC |
| Sort Method: external merge Disk: 3192kB |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.041..21.302 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.092 ms |
| Execution Time: 120.410 ms |
EXPLAIN
-- rCTE is faster for more than a few rows per group (idx scan)
EXPLAIN ANALYZE
WITH RECURSIVE cte AS (
( -- parentheses required
SELECT part, id
FROM tbl
ORDER BY part DESC, id DESC
LIMIT 1
)
UNION ALL
SELECT l.*
FROM cte c
CROSS JOIN LATERAL (
SELECT t.part, t.id
FROM tbl t
WHERE t.part < c.part -- lateral reference
ORDER BY t.part DESC, t.id DESC
LIMIT 1
) l
)
TABLE cte;
| QUERY PLAN |
|---|
| CTE Scan on cte (cost=51.44..53.46 rows=101 width=8) (actual time=0.016..0.659 rows=67 loops=1) |
| CTE cte |
| -> Recursive Union (cost=0.42..51.44 rows=101 width=8) (actual time=0.015..0.640 rows=67 loops=1) |
| -> Limit (cost=0.42..0.45 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1) |
| -> Index Only Scan Backward using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.013..0.013 rows=1 loops=1) |
| Heap Fetches: 0 |
| -> Nested Loop (cost=0.42..4.90 rows=10 width=8) (actual time=0.009..0.009 rows=1 loops=67) |
| -> WorkTable Scan on cte c (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=67) |
| -> Limit (cost=0.42..0.45 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=67) |
| -> Index Only Scan Backward using tbl_part_id_idx on tbl t (cost=0.42..1786.86 rows=60025 width=8) (actual time=0.008..0.008 rows=1 loops=67) |
| Index Cond: (part < c.part) |
| Heap Fetches: 0 |
| Planning Time: 0.429 ms |
| Execution Time: 0.865 ms |
EXPLAIN
-- original woraround, inferior to Paul's ideax.
EXPLAIN ANALYZE
SELECT id, part
FROM (
SELECT *, count(*) OVER w AS ct
, row_number() OVER (w ORDER BY id ROWS UNBOUNDED PRECEDING) AS rn
FROM tbl
WINDOW w AS (PARTITION BY part)
) sub
WHERE rn = ct;
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=26405.63..32258.07 rows=900 width=8) (actual time=114.422..197.831 rows=67 loops=1) |
| Filter: (sub.rn = sub.ct) |
| Rows Removed by Filter: 180008 |
| -> WindowAgg (cost=26405.63..30007.13 rows=180075 width=56) (actual time=113.135..187.730 rows=180075 loops=1) |
| -> Sort (cost=26405.63..26855.82 rows=180075 width=16) (actual time=113.127..133.962 rows=180075 loops=1) |
| Sort Key: tbl.part, tbl.id |
| Sort Method: external merge Disk: 4600kB |
| -> WindowAgg (cost=0.42..7606.67 rows=180075 width=16) (actual time=0.837..78.746 rows=180075 loops=1) |
| -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4905.55 rows=180075 width=8) (actual time=0.011..21.092 rows=180075 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.132 ms |
| Execution Time: 198.590 ms |
EXPLAIN