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?.
version
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
1 rows affected
QUERY PLAN
ProjectSet (cost=0.00..6350035.40 rows=1270000000 width=36) (actual rows=1000000 loops=1)
  -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
Planning Time: 0.093 ms
Execution Time: 2505.885 ms
QUERY PLAN
ProjectSet (cost=22.70..5022.72 rows=1000000 width=36) (actual rows=1000000 loops=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.084 ms
Execution Time: 1136.957 ms
QUERY PLAN
ProjectSet (cost=22.70..6350064.45 rows=1270000000 width=36) (actual rows=1000000 loops=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test test_1 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=0) (actual rows=1 loops=1)
Planning Time: 0.139 ms
Execution Time: 1136.764 ms
QUERY PLAN
ProjectSet (cost=128.56..5128.59 rows=1000000 width=36) (actual rows=1000000 loops=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 2 (returns $1)
    -> Seq Scan on test test_1 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  -> Aggregate (cost=83.16..83.17 rows=1 width=32) (actual rows=1 loops=1)
        -> Nested Loop (cost=0.01..73.63 rows=3810 width=32) (actual rows=4 loops=1)
              -> Function Scan on unnest x (cost=0.01..0.13 rows=3 width=32) (actual rows=4 loops=1)
                    Filter: (num > 1)
                    Rows Removed by Filter: 1
              -> Materialize (cost=0.00..29.05 rows=1270 width=0) (actual rows=1 loops=4)
                    -> Seq Scan on test t (cost=0.00..22.70 rows=1270 width=0) (actual rows=1 loops=1)
Planning Time: 0.312 ms
Execution Time: 1026.149 ms
QUERY PLAN
ProjectSet (cost=45.40..6350096.68 rows=1270000000 width=36) (actual rows=1000000 loops=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test test_1 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 2 (returns $1)
    -> Seq Scan on test test_2 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
Planning Time: 0.129 ms
Execution Time: 717.051 ms
QUERY PLAN
ProjectSet (cost=113.50..6350193.35 rows=1270000000 width=36) (actual rows=1000000 loops=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test test_1 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 2 (returns $1)
    -> Seq Scan on test test_2 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 3 (returns $2)
    -> Seq Scan on test test_3 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 4 (returns $3)
    -> Seq Scan on test test_4 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  InitPlan 5 (returns $4)
    -> Seq Scan on test test_5 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)
  -> Seq Scan on test (cost=0.00..22.70 rows=1270 width=0) (actual rows=1 loops=1)
Planning Time: 0.211 ms
Execution Time: 1613.141 ms