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 3394
CREATE TABLE
INSERT 0 40000
ANALYZE
ANALYZE
CREATE INDEX
CREATE INDEX
QUERY PLAN |
---|
Hash Join (cost=32.28..759.10 rows=1874 width=16) (actual time=0.794..41.169 rows=1803 loops=1) |
Hash Cond: (t.one_id = o.one_id) |
-> Seq Scan on two t (cost=0.00..617.00 rows=40000 width=8) (actual time=0.031..24.041 rows=40000 loops=1) |
-> Hash (cost=30.29..30.29 rows=159 width=16) (actual time=0.723..0.727 rows=157 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 16kB |
-> Bitmap Heap Scan on one o (cost=5.91..30.29 rows=159 width=16) (actual time=0.568..0.674 rows=157 loops=1) |
Recheck Cond: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone)) |
Heap Blocks: exact=22 |
-> Bitmap Index Scan on one_cut_time (cost=0.00..5.87 rows=159 width=0) (actual time=0.529..0.529 rows=157 loops=1) |
Index Cond: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone)) |
Planning Time: 2.109 ms |
Execution Time: 41.826 ms |
EXPLAIN
SET
SET
SET
QUERY PLAN |
---|
Hash Join (cost=20000000143.44..20000001905.25 rows=1874 width=16) (actual time=1.433..43.312 rows=1803 loops=1) |
Hash Cond: (t.one_id = o.one_id) |
-> Index Scan using two_one_id on two t (cost=10000000000.29..10000001652.29 rows=40000 width=8) (actual time=0.019..32.601 rows=40000 loops=1) |
-> Hash (cost=10000000141.16..10000000141.16 rows=159 width=16) (actual time=1.401..1.403 rows=157 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 16kB |
-> Index Scan using one_pkey on one o (cost=10000000000.28..10000000141.16 rows=159 width=16) (actual time=0.015..1.358 rows=157 loops=1) |
Filter: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone)) |
Rows Removed by Filter: 3237 |
Planning Time: 0.489 ms |
Execution Time: 43.613 ms |
EXPLAIN