explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4y2i

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

CTE Scan on features f (cost=21.21..23.99 rows=1 width=111) (actual time=28.653..28.653 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.094 11.893 ↓ 4.0 4 1

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

4. 0.017 11.799 ↓ 4.0 4 1

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

  • Sort Key: f_1.id
  • Sort Method: quicksort Memory: 25kB
5. 11.782 11.782 ↓ 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=0.663..11.782 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. 11.916 11.976 ↓ 2.0 2 1

CTE Scan on squashed_features f_2 (cost=0.00..12.65 rows=1 width=145) (actual time=11.913..11.976 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.016 0.060 ↑ 1.0 1 4

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

10. 0.044 0.044 ↑ 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.010..0.011 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 : 1.403 ms
Execution time : 28.756 ms