explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ISwc

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

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

WindowAgg (cost=8.47..8.55 rows=1 width=11,529) (actual time=12.425..12.484 rows=4 loops=1)

4. 0.020 12.379 ↓ 4.0 4 1

Sort (cost=8.47..8.47 rows=1 width=11,495) (actual time=12.375..12.379 rows=4 loops=1)

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

Index Scan using idxs_feature_geometry on feature f_1 (cost=0.42..8.46 rows=1 width=11,495) (actual time=0.795..12.359 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. 12.507 12.603 ↓ 2.0 2 1

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

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

10. 0.080 0.080 ↑ 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.019..0.020 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 : 2.468 ms
Execution time : 29.586 ms