explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPzu

Settings
# exclusive inclusive rows x rows loops node
1. 44.319 44.319 ↓ 0.0 0 1

CTE Scan on features f (cost=21.21..23.99 rows=1 width=111) (actual time=44.318..44.319 rows=0 loops=1)

  • Filter: ((geometry_index && '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry) AND (feature_type = ANY ('{1668,4435,1931,3280,2803,1929,11444,10350,1669,3697}'::integer[])) AND _st_intersects(geometry_index, '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry))
  • Rows Removed by Filter: 2
2.          

CTE squashed_features

3. 0.227 20.552 ↓ 4.0 4 1

WindowAgg (cost=8.47..8.55 rows=1 width=11,519) (actual time=20.466..20.552 rows=4 loops=1)

4. 0.045 20.325 ↓ 4.0 4 1

Sort (cost=8.47..8.47 rows=1 width=11,485) (actual time=20.321..20.325 rows=4 loops=1)

  • Sort Key: f_1.id
  • Sort Method: quicksort Memory: 25kB
5. 20.280 20.280 ↓ 4.0 4 1

Index Scan using idxs_feature_geometry on feature f_1 (cost=0.42..8.46 rows=1 width=11,485) (actual time=1.113..20.280 rows=4 loops=1)

  • Index Cond: (geometry_index && '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry)
  • Filter: ((version > '421022838'::bigint) AND (version <= '423208098'::bigint) AND (branch = '233b38a4-f0bf-4289-bfdc-7f2a04fc4ab3'::uuid) AND (feature_type = ANY ('{1668,4435,1931,3280,2803,1929,11444,10350,1669,3697}'::integer[])))
  • Rows Removed by Filter: 615
6.          

CTE features

7. 20.588 20.672 ↓ 2.0 2 1

CTE Scan on squashed_features f_2 (cost=0.00..12.65 rows=1 width=145) (actual time=20.589..20.672 rows=2 loops=1)

  • Filter: (((NOT re) OR (NOT ad)) AND (SubPlan 2))
  • Rows Removed by Filter: 2
8.          

SubPlan (for CTE Scan)

9. 0.020 0.084 ↑ 1.0 1 4

Limit (cost=0.57..12.63 rows=1 width=24) (actual time=0.018..0.021 rows=1 loops=4)

10. 0.064 0.064 ↑ 1.0 1 4

Index Only Scan Backward using feature_pkey on feature f1 (cost=0.57..12.63 rows=1 width=24) (actual time=0.015..0.016 rows=1 loops=4)

  • Index Cond: ((id = f_2.id) AND (branch = '233b38a4-f0bf-4289-bfdc-7f2a04fc4ab3'::uuid))
  • Filter: (((version + 0) > '421022838'::bigint) AND ((version + 0) <= '423208098'::bigint))
  • Heap Fetches: 4
Planning time : 11.948 ms
Execution time : 44.520 ms