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
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
INSERT 0 100000
INSERT 0 10000
ANALYZE
version |
---|
PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit |
SELECT 1
QUERY PLAN |
---|
Seq Scan on book (cost=8.30..2443.31 rows=50000 width=41) (actual time=26.878..26.880 rows=0 loops=1) |
Filter: (((name)::text = 'x'::text) OR (hashed SubPlan 1)) |
Rows Removed by Filter: 100000 |
SubPlan 1 |
-> Index Scan using author_name_index on author (cost=0.29..8.30 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1) |
Index Cond: ((name)::text = 'y'::text) |
Planning Time: 0.720 ms |
Execution Time: 26.981 ms |
EXPLAIN
QUERY PLAN |
---|
Bitmap Heap Scan on book (cost=56.45..350.69 rows=101 width=41) (actual time=0.046..0.047 rows=0 loops=1) |
Recheck Cond: (((name)::text = 'x'::text) OR (author = ANY ($0))) |
InitPlan 1 (returns $0) |
-> Index Scan using author_name_index on author (cost=0.29..8.30 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=1) |
Index Cond: ((name)::text = 'y'::text) |
-> BitmapOr (cost=48.15..48.15 rows=101 width=0) (actual time=0.044..0.045 rows=0 loops=1) |
-> Bitmap Index Scan on book_name_index (cost=0.00..4.43 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1) |
Index Cond: ((name)::text = 'x'::text) |
-> Bitmap Index Scan on book_author_index (cost=0.00..43.67 rows=100 width=0) (actual time=0.030..0.030 rows=0 loops=1) |
Index Cond: (author = ANY ($0)) |
Planning Time: 0.166 ms |
Execution Time: 0.080 ms |
EXPLAIN