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 2000
CREATE TABLE
INSERT 0 17000
CREATE INDEX
ANALYZE
ANALYZE
| QUERY PLAN |
|---|
| Unique (cost=31957.90..32807.90 rows=17000 width=14) (actual time=79.562..79.812 rows=1139 loops=1) |
| -> Sort (cost=31957.90..32382.90 rows=170000 width=14) (actual time=79.561..79.626 rows=1166 loops=1) |
| Sort Key: n.number, p.code DESC |
| Sort Method: quicksort Memory: 103kB |
| -> Nested Loop (cost=0.79..14280.50 rows=170000 width=14) (actual time=0.646..77.620 rows=1166 loops=1) |
| -> Seq Scan on prefix p (cost=0.00..31.00 rows=2000 width=5) (actual time=0.020..0.535 rows=2000 loops=1) |
| -> Bitmap Heap Scan on num n (cost=0.79..6.27 rows=85 width=9) (actual time=0.035..0.036 rows=1 loops=2000) |
| Recheck Cond: ("right"(number, '-1'::integer) ~~ (p.code || '%'::text)) |
| Rows Removed by Index Recheck: 0 |
| Heap Blocks: exact=1123 |
| -> Bitmap Index Scan on num_trgm_gin_idx (cost=0.00..0.77 rows=85 width=0) (actual time=0.033..0.033 rows=1 loops=2000) |
| Index Cond: ("right"(number, '-1'::integer) ~~ (p.code || '%'::text)) |
| Planning time: 4.518 ms |
| Execution time: 80.076 ms |
EXPLAIN