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 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
INSERT 0 4
INSERT 0 4
| num | new_num |
|---|---|
| 2 | 2 |
| null | |
| null | null |
| null |
SELECT 4
| num | new_num |
|---|---|
| 2 | 2 |
| null | |
| null | null |
| null |
SELECT 4
| QUERY PLAN |
|---|
| Insert on public.t (cost=3750.11..3754.61 rows=200 width=36) (actual time=267.606..267.608 rows=0 loops=1) |
| Buffers: shared hit=29637 read=2 dirtied=77 written=77, temp read=281 written=632 |
| -> Subquery Scan on "*SELECT*" (cost=3750.11..3754.61 rows=200 width=36) (actual time=67.904..119.536 rows=10000 loops=1) |
| Output: "*SELECT*".string_agg, NULL::integer |
| Buffers: temp read=281 written=632 |
| -> HashAggregate (cost=3750.11..3752.61 rows=200 width=36) (actual time=67.903..117.699 rows=10000 loops=1) |
| Output: string_agg(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'::text, (((random() * '36'::double precision) + '1'::double precision))::integer, 1), ''::text), words.idx |
| Group Key: words.idx |
| Batches: 5 Memory Usage: 2953kB Disk Usage: 3432kB |
| Buffers: temp read=281 written=632 |
| -> Nested Loop (cost=0.01..2000.11 rows=100000 width=4) (actual time=1.261..18.299 rows=100000 loops=1) |
| Output: words.idx |
| -> Function Scan on pg_catalog.generate_series word (cost=0.00..0.10 rows=10 width=0) (actual time=0.014..0.016 rows=10 loops=1) |
| Output: word.chr_idx |
| Function Call: generate_series(1, 10) |
| -> Function Scan on pg_catalog.generate_series words (cost=0.00..100.00 rows=10000 width=4) (actual time=0.124..0.773 rows=10000 loops=10) |
| Output: words.idx |
| Function Call: generate_series(1, 10000) |
| Planning: |
| Buffers: shared hit=17 read=6 |
| Planning Time: 2.685 ms |
| Execution Time: 268.779 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Insert on public.s (cost=3750.11..3754.61 rows=200 width=36) (actual time=200.829..200.832 rows=0 loops=1) |
| Buffers: shared hit=29661 read=2 dirtied=78 written=78, temp read=281 written=632 |
| -> Subquery Scan on "*SELECT*" (cost=3750.11..3754.61 rows=200 width=36) (actual time=63.874..115.495 rows=10000 loops=1) |
| Output: "*SELECT*".string_agg, NULL::integer |
| Buffers: temp read=281 written=632 |
| -> HashAggregate (cost=3750.11..3752.61 rows=200 width=36) (actual time=63.873..112.913 rows=10000 loops=1) |
| Output: string_agg(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'::text, (((random() * '36'::double precision) + '1'::double precision))::integer, 1), ''::text), words.idx |
| Group Key: words.idx |
| Batches: 5 Memory Usage: 2953kB Disk Usage: 3432kB |
| Buffers: temp read=281 written=632 |
| -> Nested Loop (cost=0.01..2000.11 rows=100000 width=4) (actual time=0.555..17.283 rows=100000 loops=1) |
| Output: words.idx |
| -> Function Scan on pg_catalog.generate_series word (cost=0.00..0.10 rows=10 width=0) (actual time=0.007..0.009 rows=10 loops=1) |
| Output: word.chr_idx |
| Function Call: generate_series(1, 10) |
| -> Function Scan on pg_catalog.generate_series words (cost=0.00..100.00 rows=10000 width=4) (actual time=0.055..0.685 rows=10000 loops=10) |
| Output: words.idx |
| Function Call: generate_series(1, 10000) |
| Planning Time: 0.155 ms |
| Execution Time: 201.519 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.s (cost=0.00..194.70 rows=6985 width=4) (actual time=0.025..13.154 rows=10004 loops=1) |
| Output: (NULLIF(translate(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. '::text, ''::text), ''::text))::integer |
| Buffers: shared hit=55 |
| Planning Time: 0.056 ms |
| Execution Time: 13.589 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.t (cost=0.00..194.70 rows=6985 width=4) (actual time=0.021..44.429 rows=10004 loops=1) |
| Output: (NULLIF(regexp_replace(num, '\D'::text, ''::text, 'g'::text), ''::text))::integer |
| Buffers: shared hit=55 |
| Planning Time: 0.027 ms |
| Execution Time: 44.924 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.s (cost=0.00..124.85 rows=6985 width=36) (actual time=0.009..0.744 rows=10004 loops=1) |
| Output: num, new_num |
| Buffers: shared hit=55 |
| Planning Time: 0.036 ms |
| Execution Time: 1.151 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.t (cost=0.00..124.85 rows=6985 width=36) (actual time=0.008..0.739 rows=10004 loops=1) |
| Output: num, new_num |
| Buffers: shared hit=55 |
| Planning Time: 0.022 ms |
| Execution Time: 1.144 ms |
EXPLAIN