explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v7Ji

Settings
# exclusive inclusive rows x rows loops node
1. 521.679 3,649.535 ↓ 0.0 0 1

Update on calculations_gps tep (cost=5,295.81..5,297.51 rows=1 width=215) (actual time=3,649.535..3,649.535 rows=0 loops=1)

2.          

CTE track_table

3. 15.729 21.008 ↑ 1.0 1 1

Aggregate (cost=122.51..122.53 rows=1 width=32) (actual time=21.006..21.008 rows=1 loops=1)

4. 3.812 5.279 ↓ 200.4 26,457 1

Bitmap Heap Scan on calculations_gps (cost=2.41..122.18 rows=132 width=40) (actual time=1.528..5.279 rows=26,457 loops=1)

  • Recheck Cond: (traccar_session_id = 589541)
  • Heap Blocks: exact=378
5. 1.467 1.467 ↓ 200.4 26,457 1

Bitmap Index Scan on calculations_gps_traccar_session_id_idx (cost=0.00..2.38 rows=132 width=0) (actual time=1.467..1.467 rows=26,457 loops=1)

  • Index Cond: (traccar_session_id = 589541)
6.          

CTE parcels_bbox

7. 1.027 292.416 ↑ 2.1 941 1

Nested Loop (cost=0.42..824.49 rows=1,969 width=818) (actual time=1.304..292.416 rows=941 loops=1)

8. 0.091 0.091 ↑ 1.0 1 1

CTE Scan on track_table (cost=0.00..0.02 rows=1 width=32) (actual time=0.086..0.091 rows=1 loops=1)

9. 291.298 291.298 ↓ 4.8 941 1

Index Scan using idx_parcel_geometry on parcel p (cost=0.42..822.50 rows=197 width=786) (actual time=0.973..291.298 rows=941 loops=1)

  • Index Cond: (geometry_area && (st_envelope(track_table.line))::geography)
  • Filter: ((cluster_id IS NULL) AND ((status)::text = 'ACTIVE'::text) AND (_st_distance(geometry_area, (st_envelope(track_table.line))::geography, '0'::double precision, false) < '1e-05'::double precision))
  • Rows Removed by Filter: 73
10.          

CTE concerned_parcels

11. 506.075 1,438.777 ↓ 14.0 14 1

Nested Loop (cost=0.00..566.11 rows=1 width=36) (actual time=398.205..1,438.777 rows=14 loops=1)

  • Join Filter: (((p_1.geometry_area)::geometry && track_table_1.line) AND _st_intersects((p_1.geometry_area)::geometry, track_table_1.line))
  • Rows Removed by Join Filter: 927
12. 21.199 21.199 ↑ 1.0 1 1

CTE Scan on track_table track_table_1 (cost=0.00..0.02 rows=1 width=32) (actual time=21.198..21.199 rows=1 loops=1)

13. 911.503 911.503 ↑ 2.1 941 1

CTE Scan on parcels_bbox p_1 (cost=0.00..39.38 rows=1,969 width=36) (actual time=1.544..911.503 rows=941 loops=1)

14.          

CTE client_parcels

15. 25.330 27.952 ↑ 1.0 1 1

Aggregate (cost=3,778.47..3,778.48 rows=1 width=40) (actual time=27.951..27.952 rows=1 loops=1)

16. 2.622 2.622 ↑ 25.9 137 1

Index Scan using idx_parcel_cluster_id on parcel p_2 (cost=0.43..3,751.84 rows=3,550 width=505) (actual time=0.032..2.622 rows=137 loops=1)

  • Index Cond: (cluster_id = ANY ('{603,2854,2868,2873,4353,4354,4355,4357,4358,4364,4366,4367,4550,4551,4579,4599,4605,4714}'::integer[]))
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 3
17. 1,417.069 3,127.856 ↓ 24,556.0 24,556 1

Nested Loop (cost=4.20..5.90 rows=1 width=215) (actual time=433.097..3,127.856 rows=24,556 loops=1)

  • Join Filter: ((client_parcels.nb = 0) OR ((client_parcels.nb > 0) AND ((NOT ((concerned_parcels.geometry_area)::geometry && client_parcels.geometry_area)) OR (NOT _st_intersects((concerned_parcels.geometry_area)::geometry, client_parcels.geometry_area)))))
18. 5.034 1,538.895 ↓ 24,556.0 24,556 1

Nested Loop (cost=4.20..5.60 rows=1 width=183) (actual time=400.169..1,538.895 rows=24,556 loops=1)

19. 1,438.871 1,438.871 ↓ 14.0 14 1

CTE Scan on concerned_parcels (cost=0.00..0.02 rows=1 width=96) (actual time=398.219..1,438.871 rows=14 loops=1)

20. 78.092 94.990 ↓ 1,754.0 1,754 14

Bitmap Heap Scan on calculations_gps tep (cost=4.20..5.57 rows=1 width=87) (actual time=1.574..6.785 rows=1,754 loops=14)

  • Recheck Cond: (((concerned_parcels.geometry_area)::geometry ~ point_geometry) AND (parcel_id IS NULL))
  • Filter: ((traccar_session_id = 589541) AND _st_contains((concerned_parcels.geometry_area)::geometry, point_geometry))
  • Rows Removed by Filter: 630
  • Heap Blocks: exact=820
21. 0.154 16.898 ↓ 0.0 0 14

BitmapAnd (cost=4.20..4.20 rows=1 width=0) (actual time=1.207..1.207 rows=0 loops=14)

22. 4.228 4.228 ↓ 97.8 2,543 14

Bitmap Index Scan on calculations_gps_point_geometry_idx (cost=0.00..1.58 rows=26 width=0) (actual time=0.302..0.302 rows=2,543 loops=14)

  • Index Cond: ((concerned_parcels.geometry_area)::geometry ~ point_geometry)
23. 12.516 12.516 ↓ 200.4 26,457 14

Bitmap Index Scan on calculations_gps_parcel_id_idx (cost=0.00..2.38 rows=132 width=0) (actual time=0.894..0.894 rows=26,457 loops=14)

  • Index Cond: (parcel_id IS NULL)
24. 171.892 171.892 ↑ 1.0 1 24,556

CTE Scan on client_parcels (cost=0.00..0.03 rows=1 width=104) (actual time=0.007..0.007 rows=1 loops=24,556)

  • Filter: ((nb = 0) OR (nb > 0))