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.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
1 rows affected
10 rows affected
100000 rows affected
100000 rows affected
250329 rows affected
QUERY PLAN |
---|
Subquery Scan on supervisor_agenda (cost=8031.20..8081.28 rows=6 width=176) |
Filter: (supervisor_agenda.scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid) |
-> WindowAgg (cost=8031.20..8065.63 rows=1252 width=176) |
-> Sort (cost=8031.20..8034.33 rows=1252 width=96) |
Sort Key: sch.planned_start |
-> Nested Loop (cost=4340.56..7966.79 rows=1252 width=96) |
-> Hash Join (cost=4340.14..7334.11 rows=1252 width=80) |
Hash Cond: (su.scheduled_activity_id = sch.uuid) |
-> Bitmap Heap Scan on supervised_activity su (cost=18.00..2059.68 rows=1252 width=48) |
Recheck Cond: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid) |
-> Bitmap Index Scan on idx_supervised_activity_supervisor (cost=0.00..17.69 rows=1252 width=0) |
Index Cond: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid) |
-> Hash (cost=2082.62..2082.62 rows=105162 width=48) |
-> Seq Scan on scheduled_activity sch (cost=0.00..2082.62 rows=105162 width=48) |
-> Index Scan using pkey_activity_planning on activity_planning act (cost=0.42..0.51 rows=1 width=48) |
Index Cond: (uuid = sch.activity_planning_id) |
QUERY PLAN |
---|
WindowAgg (cost=83.93..84.10 rows=6 width=176) |
-> Sort (cost=83.93..83.95 rows=6 width=96) |
Sort Key: sch.planned_start |
-> Nested Loop (cost=44.58..83.86 rows=6 width=96) |
-> Nested Loop (cost=0.83..16.87 rows=1 width=64) |
-> Index Scan using pkey_scheduled_activity on scheduled_activity sch (cost=0.42..8.44 rows=1 width=48) |
Index Cond: (uuid = '00000000-0000-0000-0000-000000000000'::uuid) |
-> Index Scan using pkey_activity_planning on activity_planning act (cost=0.42..8.44 rows=1 width=48) |
Index Cond: (uuid = sch.activity_planning_id) |
-> Bitmap Heap Scan on supervised_activity su (cost=43.75..66.93 rows=6 width=48) |
Recheck Cond: ((supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid) AND (scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid)) |
-> BitmapAnd (cost=43.75..43.75 rows=6 width=0) |
-> Bitmap Index Scan on idx_supervised_activity_supervisor (cost=0.00..17.69 rows=1252 width=0) |
Index Cond: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid) |
-> Bitmap Index Scan on idx_supervised_activity_scheduled_activity (cost=0.00..25.81 rows=1252 width=0) |
Index Cond: (scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid) |