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
INSERT 0 5
INSERT 0 54969
CREATE INDEX
VACUUM
prefix | code |
---|---|
A04 | A04 |
A04 | A04.1 |
A04 | A04.2 |
A04.1 | A04.1 |
A04.2 | A04.2 |
A05 | A05 |
A06 | A06 |
SELECT 7
QUERY PLAN |
---|
Sort (cost=150.88..154.31 rows=1374 width=37) (actual time=0.047..0.048 rows=7 loops=1) |
Sort Key: "*VALUES*".column1, c.code |
Sort Method: quicksort Memory: 25kB |
-> Nested Loop Left Join (cost=0.28..79.26 rows=1374 width=37) (actual time=0.018..0.033 rows=7 loops=1) |
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=32) (actual time=0.005..0.007 rows=5 loops=1) |
-> Index Only Scan using medical_codes_code_spgist_idx on medical_codes c (cost=0.28..13.09 rows=275 width=5) (actual time=0.004..0.004 rows=1 loops=5) |
Index Cond: (code ^@ "*VALUES*".column1) |
Heap Fetches: 0 |
Planning Time: 0.081 ms |
Execution Time: 0.068 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=2751126.77..2788903.53 rows=15110703 width=10) (actual time=2164.402..2459.079 rows=385941 loops=1) |
Sort Key: p.code, c.code |
Sort Method: external merge Disk: 7800kB |
-> Nested Loop Left Join (cost=0.28..432764.90 rows=15110703 width=10) (actual time=397.609..1204.629 rows=385941 loops=1) |
-> Seq Scan on medical_codes p (cost=0.00..800.74 rows=54974 width=5) (actual time=0.016..26.644 rows=54974 loops=1) |
-> Index Only Scan using medical_codes_code_spgist_idx on medical_codes c (cost=0.28..5.11 rows=275 width=5) (actual time=0.010..0.012 rows=7 loops=54974) |
Index Cond: (code ^@ p.code) |
Heap Fetches: 0 |
Planning Time: 0.089 ms |
JIT: |
Functions: 4 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
Timing: Generation 0.519 ms, Inlining 280.996 ms, Optimization 68.604 ms, Emission 47.939 ms, Total 398.059 ms |
Execution Time: 2848.998 ms |
EXPLAIN
SET
QUERY PLAN |
---|
Sort (cost=2751126.77..2788903.53 rows=15110703 width=10) (actual time=1651.891..1930.406 rows=385941 loops=1) |
Sort Key: p.code, c.code |
Sort Method: external merge Disk: 7800kB |
-> Nested Loop Left Join (cost=0.28..432764.90 rows=15110703 width=10) (actual time=0.030..775.433 rows=385941 loops=1) |
-> Seq Scan on medical_codes p (cost=0.00..800.74 rows=54974 width=5) (actual time=0.011..10.926 rows=54974 loops=1) |
-> Index Only Scan using medical_codes_code_spgist_idx on medical_codes c (cost=0.28..5.11 rows=275 width=5) (actual time=0.010..0.012 rows=7 loops=54974) |
Index Cond: (code ^@ p.code) |
Heap Fetches: 0 |
Planning Time: 0.121 ms |
Execution Time: 1963.604 ms |
EXPLAIN