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?.
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