explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Si

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 1,370.272 ↓ 0.0 0 1

Update on calculations_gps tep (cost=5,418.18..5,419.87 rows=1 width=215) (actual time=1,370.272..1,370.272 rows=0 loops=1)

2.          

CTE track_table

3. 14.933 30.451 ↑ 1.0 1 1

Aggregate (cost=243.66..243.68 rows=1 width=32) (actual time=30.450..30.451 rows=1 loops=1)

4. 11.983 15.518 ↓ 99.8 26,457 1

Bitmap Heap Scan on calculations_gps (cost=3.44..243.00 rows=265 width=40) (actual time=3.703..15.518 rows=26,457 loops=1)

  • Recheck Cond: (traccar_session_id = 589541)
  • Heap Blocks: exact=756
5. 3.535 3.535 ↓ 192.5 51,013 1

Bitmap Index Scan on calculations_gps_traccar_session_id_idx (cost=0.00..3.38 rows=265 width=0) (actual time=3.535..3.535 rows=51,013 loops=1)

  • Index Cond: (traccar_session_id = 589541)
6.          

CTE parcels_bbox

7. 0.929 273.496 ↑ 2.1 941 1

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

8. 0.341 0.341 ↑ 1.0 1 1

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

9. 272.226 272.226 ↓ 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.966..272.226 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. 0.061 1,343.074 ↓ 14.0 14 1

Sort (cost=566.12..566.12 rows=1 width=36) (actual time=1,343.064..1,343.074 rows=14 loops=1)

  • Sort Key: p_1.id
  • Sort Method: quicksort Memory: 33kB
12. 430.857 1,343.013 ↓ 14.0 14 1

Nested Loop (cost=0.00..566.11 rows=1 width=36) (actual time=375.588..1,343.013 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
13. 30.905 30.905 ↑ 1.0 1 1

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

14. 881.251 881.251 ↑ 2.1 941 1

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

15.          

CTE client_parcels

16. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3,778.47..3,778.48 rows=1 width=40) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_parcel_cluster_id on parcel p_2 (cost=0.43..3,751.84 rows=3,550 width=505) (never executed)

  • 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)
18. 0.001 1,370.264 ↓ 0.0 0 1

Nested Loop (cost=5.41..7.10 rows=1 width=215) (actual time=1,370.263..1,370.264 rows=0 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)))))
19. 0.075 1,370.263 ↓ 0.0 0 1

Nested Loop (cost=5.41..6.81 rows=1 width=183) (actual time=1,370.263..1,370.263 rows=0 loops=1)

20. 1,343.126 1,343.126 ↓ 14.0 14 1

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

21. 7.910 27.062 ↓ 0.0 0 14

Bitmap Heap Scan on calculations_gps tep (cost=5.41..6.78 rows=1 width=87) (actual time=1.933..1.933 rows=0 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: 129
  • Heap Blocks: exact=820
22. 0.168 19.152 ↓ 0.0 0 14

BitmapAnd (cost=5.41..5.41 rows=1 width=0) (actual time=1.368..1.368 rows=0 loops=14)

23. 7.728 7.728 ↓ 87.5 4,638 14

Bitmap Index Scan on calculations_gps_point_geometry_idx (cost=0.00..1.78 rows=53 width=0) (actual time=0.552..0.552 rows=4,638 loops=14)

  • Index Cond: ((concerned_parcels.geometry_area)::geometry ~ point_geometry)
24. 11.256 11.256 ↓ 99.8 26,457 14

Bitmap Index Scan on calculations_gps_parcel_id_idx (cost=0.00..3.38 rows=265 width=0) (actual time=0.804..0.804 rows=26,457 loops=14)

  • Index Cond: (parcel_id IS NULL)
25. 0.000 0.000 ↓ 0.0 0

CTE Scan on client_parcels (cost=0.00..0.03 rows=1 width=104) (never executed)

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