explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KR2d

Settings
# exclusive inclusive rows x rows loops node
1. 0.688 3,245.342 ↓ 0.0 0 1

Update on calculations_gps tep (cost=0.85..8,313,245,942.26 rows=1 width=113) (actual time=3,245.342..3,245.342 rows=0 loops=1)

  • Functions: 28
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.948 ms, Inlining 15.390 ms, Optimization 270.257 ms, Emission 160.404 ms, Total 449.999 ms
2. 23.246 3,244.654 ↓ 30.0 30 1

Nested Loop (cost=0.85..8,313,245,942.26 rows=1 width=113) (actual time=774.847..3,244.654 rows=30 loops=1)

  • Join Filter: st_contains((p.geometry_area)::geometry, tep.point_geometry)
  • Rows Removed by Join Filter: 46,284
3. 8.429 3,213.407 ↓ 9.0 9 1

Nested Loop Left Join (cost=0.85..8,313,117,096.48 rows=1 width=516) (actual time=773.733..3,213.407 rows=9 loops=1)

  • Join Filter: ((st_intersects((p.geometry_area)::geometry, (p_1.geometry_area)::geometry) IS TRUE) AND (st_area((st_intersection((p.geometry_area)::geometry, (p_1.geometry_area)::geometry))::geography, true) > '0'::double precision) AND ((st_area((st_intersection((p.geometry_area)::geometry, (p_1.geometry_area)::geometry))::geography, true) / st_area(((p.geometry_area)::geometry)::geography, true)) > '0.9'::double precision))
  • Rows Removed by Join Filter: 9,414
  • Filter: (p_1.id IS NULL)
4. 0.114 3,200.154 ↑ 982.4 9 1

Nested Loop (cost=0.41..23,188.62 rows=8,842 width=510) (actual time=768.700..3,200.154 rows=9 loops=1)

5. 446.875 446.875 ↑ 1.0 1 1

Seq Scan on calculations_geometries cg (cost=0.00..1.01 rows=1 width=38) (actual time=446.873..446.875 rows=1 loops=1)

6. 2,753.165 2,753.165 ↑ 98.2 9 1

Index Scan using idx_connectagri_parcel_geometry_area_as_geometry on parcel p (cost=0.41..23,178.77 rows=884 width=504) (actual time=321.730..2,753.165 rows=9 loops=1)

  • Index Cond: ((geometry_area)::geometry && cg.geom)
  • Filter: ((cluster_id IS NULL) AND ((status)::text = 'ACTIVE'::text) AND st_intersects((geometry_area)::geometry, cg.geom))
  • Rows Removed by Filter: 113,921
7. 1.594 4.824 ↑ 6.0 1,046 9

Materialize (cost=0.43..4,944.78 rows=6,266 width=504) (actual time=0.004..0.536 rows=1,046 loops=9)

8. 3.230 3.230 ↑ 6.0 1,046 1

Index Scan using idx_parcel_cluster_id on parcel p_1 (cost=0.43..4,913.45 rows=6,266 width=504) (actual time=0.025..3.230 rows=1,046 loops=1)

  • Index Cond: (cluster_id = ANY ('{5048,5138,5139,5490,5499,5671,5672,5675,15519,15523,15559,15576,15582,15588,15589,15590,15594,15596,15599,15600,15601,15608,15678,15682,15686,15687,15690,15694,15702,15705,15710,15730,15731,15732,15733,15734,15735,15736}'::integer[]))
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 2,498
9. 8.001 8.001 ↑ 1.0 5,146 9

Seq Scan on calculations_gps tep (cost=0.00..131.46 rows=5,146 width=91) (actual time=0.011..0.889 rows=5,146 loops=9)

  • Filter: (parcel_id IS NULL)
Planning time : 1.270 ms
Execution time : 3,249.860 ms