add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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