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 55127
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=151.25..154.70 rows=1378 width=37) (actual time=0.038..0.040 rows=7 loops=1) |
Sort Key: "*VALUES*".column1, c.code |
Sort Method: quicksort Memory: 25kB |
-> Nested Loop Left Join (cost=0.28..79.40 rows=1378 width=37) (actual time=0.014..0.029 rows=7 loops=1) |
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=32) (actual time=0.001..0.003 rows=5 loops=1) |
-> Index Only Scan using medical_codes_code_spgist_idx on medical_codes c (cost=0.28..13.11 rows=276 width=5) (actual time=0.004..0.004 rows=1 loops=5) |
Index Cond: (code ^@ "*VALUES*".column1) |
Heap Fetches: 0 |
Planning Time: 0.085 ms |
Execution Time: 0.061 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=2767847.90..2805842.12 rows=15197687 width=10) (actual time=1504.437..1889.250 rows=388283 loops=1) |
Sort Key: p.code, c.code |
Sort Method: external merge Disk: 7848kB |
-> Nested Loop Left Join (cost=0.28..435509.33 rows=15197687 width=10) (actual time=0.023..542.787 rows=388283 loops=1) |
-> Seq Scan on medical_codes p (cost=0.00..802.32 rows=55132 width=5) (actual time=0.010..11.420 rows=55132 loops=1) |
-> Index Only Scan using medical_codes_code_spgist_idx on medical_codes c (cost=0.28..5.12 rows=276 width=5) (actual time=0.007..0.008 rows=7 loops=55132) |
Index Cond: (code ^@ p.code) |
Heap Fetches: 0 |
Planning Time: 0.094 ms |
Execution Time: 1921.236 ms |
EXPLAIN