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 test1 (
id bigserial PRIMARY KEY
, joincol varchar
);
CREATE TABLE test2 (
joincol varchar
);
INSERT INTO test1 (joincol)
SELECT left(md5(g::text), 3)
FROM generate_series(1, 100000) g;
INSERT INTO test2 (joincol)
SELECT left(md5(random()::text), 3)
FROM generate_series(1, 100000) s(i);
CREATE INDEX on test1 (joincol);
CREATE INDEX on test2 (joincol);
CREATE VIEW testview AS
SELECT t1.id, joincol
FROM test1 t1
JOIN test2 t2 USING (joincol);
CREATE TABLE
CREATE TABLE
INSERT 0 100000
INSERT 0 100000
CREATE INDEX
CREATE INDEX
CREATE VIEW
VACUUM ANALYZE test1;
VACUUM
VACUUM ANALYZE test2;
VACUUM
-- aggregate functions min() & max()
-- FAST plans for query on table directly:
EXPLAIN ANALYZE SELECT min(id) FROM test1;
EXPLAIN ANALYZE SELECT max(id) FROM test1;
| QUERY PLAN |
|---|
| Result (cost=0.32..0.33 rows=1 width=8) (actual time=0.057..0.058 rows=1 loops=1) |
| InitPlan 1 (returns $0) |
| -> Limit (cost=0.29..0.32 rows=1 width=8) (actual time=0.050..0.051 rows=1 loops=1) |
| -> Index Only Scan using test1_pkey on test1 (cost=0.29..2854.29 rows=100000 width=8) (actual time=0.049..0.049 rows=1 loops=1) |
| Index Cond: (id IS NOT NULL) |
| Heap Fetches: 0 |
| Planning Time: 0.316 ms |
| Execution Time: 0.093 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Result (cost=0.32..0.33 rows=1 width=8) (actual time=0.054..0.055 rows=1 loops=1) |
| InitPlan 1 (returns $0) |
| -> Limit (cost=0.29..0.32 rows=1 width=8) (actual time=0.052..0.053 rows=1 loops=1) |
| -> Index Only Scan Backward using test1_pkey on test1 (cost=0.29..2854.29 rows=100000 width=8) (actual time=0.052..0.052 rows=1 loops=1) |
| Index Cond: (id IS NOT NULL) |
| Heap Fetches: 0 |
| Planning Time: 0.083 ms |
| Execution Time: 0.067 ms |
EXPLAIN
-- SLOW plans for view (same for the nested join-query):
EXPLAIN ANALYZE SELECT min(id) FROM testview;
EXPLAIN ANALYZE SELECT max(id) FROM testview;
EXPLAIN ANALYZE SELECT min(t1.id) FROM test1 t1 JOIN test2 t2 USING (joincol);
EXPLAIN ANALYZE SELECT max(t1.id) FROM test1 t1 JOIN test2 t2 USING (joincol);
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=546.920..546.987 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=546.907..546.979 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=507.563..507.566 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=51.122..374.792 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.037..5.947 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=49.392..49.393 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.034..9.608 rows=100000 loops=2) |
| Planning Time: 0.518 ms |
| Execution Time: 547.089 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=540.414..540.472 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=540.405..540.466 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=520.118..520.120 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=51.924..403.958 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.031..17.205 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=51.358..51.359 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.026..14.463 rows=100000 loops=2) |
| Planning Time: 0.424 ms |
| Execution Time: 540.517 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=523.375..523.437 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=523.365..523.431 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=497.845..497.848 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=43.479..360.939 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.022..16.438 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=42.960..42.961 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.020..19.307 rows=100000 loops=2) |
| Planning Time: 0.391 ms |
| Execution Time: 523.485 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=523.718..523.773 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=523.709..523.767 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=503.772..503.774 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=37.480..350.714 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.022..10.898 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=36.946..36.947 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.021..13.389 rows=100000 loops=2) |
| Planning Time: 0.378 ms |
| Execution Time: 523.818 ms |
EXPLAIN
-- ORDER BY ... LIMIT 1
-- FAST with sort order matching index (on view and table!)
EXPLAIN ANALYZE SELECT id FROM testview ORDER BY ID DESC LIMIT 1; -- from view
EXPLAIN ANALYZE SELECT id FROM test1 ORDER BY ID DESC LIMIT 1; -- from table
| QUERY PLAN |
|---|
| Limit (cost=0.58..0.63 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=1) |
| -> Nested Loop (cost=0.58..98782.29 rows=2442149 width=8) (actual time=0.043..0.044 rows=1 loops=1) |
| -> Index Scan Backward using test1_pkey on test1 t1 (cost=0.29..3148.29 rows=100000 width=12) (actual time=0.030..0.031 rows=1 loops=1) |
| -> Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..0.72 rows=24 width=4) (actual time=0.009..0.009 rows=1 loops=1) |
| Index Cond: (joincol = (t1.joincol)::text) |
| Heap Fetches: 0 |
| Planning Time: 0.428 ms |
| Execution Time: 0.064 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Limit (cost=0.29..0.32 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1) |
| -> Index Only Scan Backward using test1_pkey on test1 (cost=0.29..2604.29 rows=100000 width=8) (actual time=0.006..0.007 rows=1 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.041 ms |
| Execution Time: 0.016 ms |
EXPLAIN
-- SLOW with sort order NOT matching index (on view and table!)
EXPLAIN ANALYZE SELECT id FROM testview ORDER BY ID DESC NULLS LAST LIMIT 1; -- from view
EXPLAIN ANALYZE SELECT id FROM test1 ORDER BY ID DESC NULLS LAST LIMIT 1; -- from table
| QUERY PLAN |
|---|
| Limit (cost=33029.48..33029.59 rows=1 width=8) (actual time=626.952..627.011 rows=1 loops=1) |
| -> Gather Merge (cost=33029.48..198233.65 rows=1436558 width=8) (actual time=626.950..627.009 rows=1 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Sort (cost=32029.47..35620.86 rows=1436558 width=8) (actual time=600.255..600.257 rows=1 loops=2) |
| Sort Key: t1.id DESC NULLS LAST |
| Sort Method: top-N heapsort Memory: 25kB |
| Worker 0: Sort Method: top-N heapsort Memory: 25kB |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=37.101..388.500 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.021..5.289 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=36.559..36.560 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.018..13.543 rows=100000 loops=2) |
| Planning Time: 0.319 ms |
| Execution Time: 627.046 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Limit (cost=2041.00..2041.00 rows=1 width=8) (actual time=19.614..19.615 rows=1 loops=1) |
| -> Sort (cost=2041.00..2291.00 rows=100000 width=8) (actual time=19.613..19.613 rows=1 loops=1) |
| Sort Key: id DESC NULLS LAST |
| Sort Method: top-N heapsort Memory: 25kB |
| -> Seq Scan on test1 (cost=0.00..1541.00 rows=100000 width=8) (actual time=0.013..9.649 rows=100000 loops=1) |
| Planning Time: 0.091 ms |
| Execution Time: 19.633 ms |
EXPLAIN
-- add index with id DESC NULLS LAST
CREATE INDEX test1_id_desc_nulls_last_idx on test1 (id DESC NULLS LAST);
CREATE INDEX
-- ORDER BY ... LIMIT 1: NOW also fast (!)
EXPLAIN ANALYZE SELECT id FROM testview ORDER BY ID DESC NULLS LAST LIMIT 1; -- from view
EXPLAIN ANALYZE SELECT id FROM test1 ORDER BY ID DESC NULLS LAST LIMIT 1; -- from table
| QUERY PLAN |
|---|
| Limit (cost=0.58..0.63 rows=1 width=8) (actual time=0.040..0.041 rows=1 loops=1) |
| -> Nested Loop (cost=0.58..98782.29 rows=2442149 width=8) (actual time=0.039..0.039 rows=1 loops=1) |
| -> Index Scan using test1_id_desc_nulls_last_idx on test1 t1 (cost=0.29..3148.29 rows=100000 width=12) (actual time=0.025..0.025 rows=1 loops=1) |
| -> Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..0.72 rows=24 width=4) (actual time=0.009..0.009 rows=1 loops=1) |
| Index Cond: (joincol = (t1.joincol)::text) |
| Heap Fetches: 0 |
| Planning Time: 0.545 ms |
| Execution Time: 0.063 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Limit (cost=0.29..0.32 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1) |
| -> Index Only Scan using test1_id_desc_nulls_last_idx on test1 (cost=0.29..2604.29 rows=100000 width=8) (actual time=0.012..0.013 rows=1 loops=1) |
| Heap Fetches: 0 |
| Planning Time: 0.045 ms |
| Execution Time: 0.022 ms |
EXPLAIN
-- But no effect on min() & max()
EXPLAIN ANALYZE SELECT min(id) FROM testview;
EXPLAIN ANALYZE SELECT max(id) FROM testview;
EXPLAIN ANALYZE SELECT min(t1.id) FROM test1 t1 JOIN test2 t2 USING (joincol);
EXPLAIN ANALYZE SELECT max(t1.id) FROM test1 t1 JOIN test2 t2 USING (joincol);
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=530.008..530.069 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=529.998..530.063 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=501.660..501.662 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=46.035..356.469 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.022..5.468 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=45.446..45.446 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.021..13.294 rows=100000 loops=2) |
| Planning Time: 0.325 ms |
| Execution Time: 530.112 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=537.624..537.683 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=537.615..537.677 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=514.171..514.174 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=42.759..376.794 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.023..5.465 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=42.242..42.243 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.021..8.103 rows=100000 loops=2) |
| Planning Time: 0.419 ms |
| Execution Time: 537.729 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=536.478..537.942 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=536.469..537.936 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=519.062..519.064 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=32.358..370.559 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.022..11.047 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=31.818..31.819 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.021..13.730 rows=100000 loops=2) |
| Planning Time: 0.404 ms |
| Execution Time: 537.987 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=29438.19..29438.20 rows=1 width=8) (actual time=529.127..529.198 rows=1 loops=1) |
| -> Gather (cost=29438.07..29438.18 rows=1 width=8) (actual time=529.115..529.191 rows=2 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Partial Aggregate (cost=28438.07..28438.08 rows=1 width=8) (actual time=496.887..496.890 rows=1 loops=2) |
| -> Hash Join (cost=3280.29..24846.68 rows=1436558 width=8) (actual time=42.680..387.207 rows=1220244 loops=2) |
| Hash Cond: ((t2.joincol)::text = (t1.joincol)::text) |
| -> Parallel Index Only Scan using test2_joincol_idx on test2 t2 (cost=0.29..1472.53 rows=58824 width=4) (actual time=0.022..5.445 rows=50000 loops=2) |
| Heap Fetches: 0 |
| -> Hash (cost=1541.00..1541.00 rows=100000 width=12) (actual time=42.155..42.156 rows=100000 loops=2) |
| Buckets: 131072 Batches: 2 Memory Usage: 3250kB |
| -> Seq Scan on test1 t1 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.021..18.898 rows=100000 loops=2) |
| Planning Time: 0.386 ms |
| Execution Time: 529.249 ms |
EXPLAIN