explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8ksg

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,499.264 ↓ 0.0 0 1

Update on calculations_gps tep (cost=5,418.16..5,419.86 rows=1 width=215) (actual time=1,499.264..1,499.264 rows=0 loops=1)

2.          

CTE track_table

3. 17.826 32.399 ↑ 1.0 1 1

Aggregate (cost=243.66..243.68 rows=1 width=32) (actual time=32.398..32.399 rows=1 loops=1)

4. 10.722 14.573 ↓ 99.8 26,457 1

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

  • Recheck Cond: (traccar_session_id = 589541)
  • Heap Blocks: exact=756
5. 3.851 3.851 ↓ 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.851..3.851 rows=51,013 loops=1)

  • Index Cond: (traccar_session_id = 589541)
6.          

CTE parcels_bbox

7. 1.254 286.477 ↑ 2.1 941 1

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

8. 0.093 0.093 ↑ 1.0 1 1

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

9. 285.130 285.130 ↓ 4.8 941 1

Index Scan using idx_parcel_geometry on parcel p (cost=0.42..822.50 rows=197 width=786) (actual time=1.042..285.130 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. 510.386 1,470.207 ↓ 14.0 14 1

Nested Loop (cost=0.00..566.11 rows=1 width=36) (actual time=410.055..1,470.207 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. 32.608 32.608 ↑ 1.0 1 1

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

13. 927.213 927.213 ↑ 2.1 941 1

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

14.          

CTE client_parcels

15. 0.000 0.000 ↓ 0.0 0

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

16. 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)
17. 0.001 1,499.261 ↓ 0.0 0 1

Nested Loop (cost=5.41..7.10 rows=1 width=215) (actual time=1,499.261..1,499.261 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)))))
18. 0.172 1,499.260 ↓ 0.0 0 1

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

19. 1,470.304 1,470.304 ↓ 14.0 14 1

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

20. 6.776 28.784 ↓ 0.0 0 14

Bitmap Heap Scan on calculations_gps tep (cost=5.41..6.78 rows=1 width=87) (actual time=2.056..2.056 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
21. 0.210 22.008 ↓ 0.0 0 14

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

22. 9.142 9.142 ↓ 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.653..0.653 rows=4,638 loops=14)

  • Index Cond: ((concerned_parcels.geometry_area)::geometry ~ point_geometry)
23. 12.656 12.656 ↓ 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.904..0.904 rows=26,457 loops=14)

  • Index Cond: (parcel_id IS NULL)
24. 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))