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?.
version |
---|
PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
1 rows affected
29999 rows affected
QUERY PLAN |
---|
Incremental Sort (cost=0.33..2361.29 rows=30000 width=28) (actual rows=30000 loops=1) |
Sort Key: id, t_field |
Presorted Key: id |
Full-sort Groups: 938 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Index Scan using test_pkey on test (cost=0.29..1011.29 rows=30000 width=28) (actual rows=30000 loops=1) |
Planning Time: 0.306 ms |
Execution Time: 8.088 ms |
t_field | result |
---|---|
bar$foo$john$doe$xxx | bar$foo$john$doe-xxx |
02e0f$e9b$c3f7$64c$fd5 | 02e0f$e9b$c3f7$64c-fd5 |
QUERY PLAN |
---|
Seq Scan on test (cost=0.00..972.00 rows=30000 width=32) (actual rows=30000 loops=1) |
Planning Time: 0.035 ms |
Execution Time: 56.102 ms |
t_field | result |
---|---|
bar$foo$john$doe$xxx | bar$foo$john$doe-xxx |
02e0f$e9b$c3f7$64c$fd5 | 02e0f$e9b$c3f7$64c-fd5 |
QUERY PLAN |
---|
Seq Scan on test (cost=0.00..747.00 rows=30000 width=32) (actual rows=30000 loops=1) |
Planning Time: 0.032 ms |
Execution Time: 93.267 ms |
t_field | regexp_replace |
---|---|
bar$foo$john$doe$xxx | bar$foo$john$doe-xxx |
02e0f$e9b$c3f7$64c$fd5 | 02e0f$e9b$c3f7$64c-fd5 |
QUERY PLAN |
---|
Seq Scan on test (cost=0.00..597.00 rows=30000 width=32) (actual rows=30000 loops=1) |
Planning Time: 0.069 ms |
Execution Time: 216.401 ms |
t_field | reverse |
---|---|
bar$foo$john$doe$xxx | bar$foo$john$doe-xxx |
02e0f$e9b$c3f7$64c$fd5 | 02e0f$e9b$c3f7$64c-fd5 |
QUERY PLAN |
---|
Seq Scan on test (cost=0.00..1422.00 rows=30000 width=32) (actual rows=30000 loops=1) |
Planning Time: 0.062 ms |
Execution Time: 430.342 ms |
unnest |
---|
xxx |
eod |
nhoj |
oof |
rab |
t_field | id | elem | num |
---|---|---|---|
bar$foo$john$doe$xxx | 1 | xxx | 1 |
bar$foo$john$doe$xxx | 1 | eod | 2 |
bar$foo$john$doe$xxx | 1 | nhoj | 3 |
bar$foo$john$doe$xxx | 1 | oof | 4 |
bar$foo$john$doe$xxx | 1 | rab | 5 |
02e0f$e9b$c3f7$64c$fd5 | 2 | 5df | 1 |
02e0f$e9b$c3f7$64c$fd5 | 2 | c46 | 2 |
02e0f$e9b$c3f7$64c$fd5 | 2 | 7f3c | 3 |
02e0f$e9b$c3f7$64c$fd5 | 2 | b9e | 4 |
02e0f$e9b$c3f7$64c$fd5 | 2 | f0e20 | 5 |
t_field | result |
---|---|
bar$foo$john$doe$xxx | bar$foo$john$doe-xxx |
02e0f$e9b$c3f7$64c$fd5 | 02e0f$e9b$c3f7$64c-fd5 |
QUERY PLAN |
---|
GroupAggregate (cost=8.60..256019.60 rows=30000 width=36) (actual rows=30000 loops=1) |
Group Key: t.id |
-> Nested Loop (cost=8.60..5669.60 rows=90000 width=36) (actual rows=120000 loops=1) |
-> Index Only Scan using test_pkey on test t (cost=0.29..1011.29 rows=30000 width=4) (actual rows=30000 loops=1) |
Heap Fetches: 30000 |
-> Function Scan on unnest x (cost=8.31..8.44 rows=3 width=32) (actual rows=4 loops=30000) |
Filter: (num > 1) |
Rows Removed by Filter: 1 |
SubPlan 2 |
-> Index Scan using test_pkey on test test_1 (cost=0.29..8.30 rows=1 width=24) (actual rows=1 loops=30000) |
Index Cond: (id = t.id) |
SubPlan 1 |
-> Index Scan using test_pkey on test (cost=0.29..8.31 rows=1 width=32) (actual rows=1 loops=30000) |
Index Cond: (id = t.id) |
Planning Time: 0.220 ms |
Execution Time: 928.290 ms |