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 5
CREATE TABLE
transaction_id | address | value |
---|---|---|
1 | a1 | h1.a1.1 |
1 | a1 | h1.a1.2 |
1 | a3 | h1.a3.1 |
2 | a1 | h2.a1.1 |
2 | a2 | h2.a2.1 |
2 | a2 | h2.a2.2 |
2 | a3 | h2.a3.1 |
3 | a2 | h3.a2.1 |
4 | a1 | h4.a1.1 |
5 | a2 | h5.a2.1 |
5 | a3 | h5.a3.1 |
INSERT 0 11
CREATE INDEX
id | logs_array |
---|---|
2 | {"{"address" : "a1", "value" : "h2.a1.1"}","{"address" : "a2", "value" : "h2.a2.1"}","{"address" : "a2", "value" : "h2.a2.2"}","{"address" : "a3", "value" : "h2.a3.1"}"} |
3 | {"{"address" : "a2", "value" : "h3.a2.1"}"} |
SELECT 2
id | logs_json_array |
---|---|
2 | [{"address":"a1","value":"h2.a1.1"}, {"address":"a2","value":"h2.a2.1"}, {"address":"a2","value":"h2.a2.2"}, {"address":"a3","value":"h2.a3.1"}] |
3 | [{"address":"a2","value":"h3.a2.1"}] |
SELECT 2
id | logs_array |
---|---|
2 | {"{"address" : "a1", "value" : "h2.a1.1"}","{"address" : "a2", "value" : "h2.a2.1"}","{"address" : "a2", "value" : "h2.a2.2"}","{"address" : "a3", "value" : "h2.a3.1"}"} |
3 | {"{"address" : "a2", "value" : "h3.a2.1"}"} |
SELECT 2
SET
PREPARE
id | logs |
---|---|
2 | [{"address":"a1","value":"h2.a1.1"}, {"address":"a2","value":"h2.a2.1"}, {"address":"a2","value":"h2.a2.2"}, {"address":"a3","value":"h2.a3.1"}] |
3 | [{"address":"a2","value":"h3.a2.1"}] |
SELECT 2
SET
QUERY PLAN |
---|
Limit (cost=826.66..863.45 rows=3 width=36) (actual time=0.081..0.216 rows=3 loops=1) |
CTE adr_trans |
-> Recursive Union (cost=0.14..826.66 rows=101 width=4) (actual time=0.020..0.118 rows=3 loops=1) |
-> Limit (cost=0.14..8.15 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1) |
-> Index Only Scan using logs_address_transaction_id on logs l_1 (cost=0.14..8.15 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1) |
Index Cond: (address = 'a2'::text) |
Heap Fetches: 1 |
-> WorkTable Scan on adr_trans a_1 (cost=0.00..81.75 rows=10 width=4) (actual time=0.045..0.046 rows=1 loops=2) |
Filter: (transaction_id IS NOT NULL) |
SubPlan 1 |
-> Limit (cost=0.14..8.15 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=2) |
-> Index Only Scan using logs_address_transaction_id on logs l (cost=0.14..8.15 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=2) |
Index Cond: ((address = 'a2'::text) AND (transaction_id > a_1.transaction_id)) |
Heap Fetches: 2 |
-> CTE Scan on adr_trans a (cost=0.00..1226.27 rows=100 width=36) (actual time=0.079..0.211 rows=3 loops=1) |
Filter: (transaction_id IS NOT NULL) |
SubPlan 3 |
-> Aggregate (cost=12.23..12.24 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=3) |
-> Index Only Scan using logs_address_transaction_id on logs l_2 (cost=0.14..12.23 rows=1 width=64) (actual time=0.011..0.013 rows=2 loops=3) |
Index Cond: (transaction_id = a.transaction_id) |
Heap Fetches: 7 |
Planning Time: 0.430 ms |
Execution Time: 0.312 ms |
EXPLAIN