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 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit |
8 rows affected
id | name |
---|---|
1 | A |
2 | B |
3 | C |
3 rows affected
id | name |
---|---|
1 | A |
2 | B |
3 | C |
3 rows affected
3 rows affected
3 rows affected
QUERY PLAN |
---|
Insert on objs (cost=33.72..37.92 rows=200 width=150) (actual time=0.058..0.058 rows=0 loops=1) |
Buffers: shared hit=16 |
-> Subquery Scan on "*SELECT*" (cost=33.72..37.92 rows=200 width=150) (actual time=0.026..0.030 rows=3 loops=1) |
Buffers: shared hit=1 |
-> Unique (cost=33.72..35.92 rows=200 width=154) (actual time=0.025..0.028 rows=3 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=33.72..34.82 rows=440 width=154) (actual time=0.025..0.026 rows=8 loops=1) |
Sort Key: all_names.id, all_names.priority DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on all_names (cost=0.00..14.40 rows=440 width=154) (actual time=0.008..0.009 rows=8 loops=1) |
Buffers: shared hit=1 |
Planning time: 0.067 ms |
Execution time: 0.084 ms |
3 rows affected
QUERY PLAN |
---|
Insert on objs (cost=42.52..52.42 rows=2 width=150) (actual time=0.087..0.087 rows=0 loops=1) |
Buffers: shared hit=16 |
CTE cte |
-> WindowAgg (cost=33.72..42.52 rows=440 width=162) (actual time=0.043..0.051 rows=8 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=33.72..34.82 rows=440 width=154) (actual time=0.036..0.037 rows=8 loops=1) |
Sort Key: all_names.id, all_names.priority DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on all_names (cost=0.00..14.40 rows=440 width=154) (actual time=0.008..0.010 rows=8 loops=1) |
Buffers: shared hit=1 |
-> CTE Scan on cte (cost=0.00..9.90 rows=2 width=150) (actual time=0.045..0.057 rows=3 loops=1) |
Filter: (rn = 1) |
Rows Removed by Filter: 5 |
Buffers: shared hit=1 |
Planning time: 0.070 ms |
Execution time: 0.148 ms |