add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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