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