explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FqMQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 24,322.410 ↓ 0.0 0 1
→

Update on calculations_gps tep (cost=4,852.17..5,419.67 rows=1 width=215) (actual time=24,322.410..24,322.410 rows=0 loops=1)

2.          

CTE track_table
3. 17.613 33.174 ↑ 1.0 1 1
→

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

4. 13.116 15.561 ↓ 99.8 26,457 1
→

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

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

  • Index Cond: (traccar_session_id = 589541)
6.          

CTE concerned_parcels
7. 8.718 24,256.952 ↑ 140.6 14 1
→

Nested Loop (cost=0.42..823.01 rows=1,969 width=505) (actual time=3,838.047..24,256.952 rows=14 loops=1)

8. 33.375 33.375 ↑ 1.0 1 1
→

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

9. 24,214.859 24,214.859 ↑ 14.1 14 1
→

Index Scan using idx_parcel_geometry on parcel p (cost=0.42..821.02 rows=197 width=505) (actual time=3,795.993..24,214.859 rows=14 loops=1)

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

CTE client_parcels
11. 0.000 0.000 ↓ 0.0 0
→

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

12. 0.000 0.000 ↓ 0.0 0
→

Index Scan using idx_parcel_cluster_id on parcel p_1 (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)
13. 0.001 24,322.408 ↓ 0.0 0 1
→

Nested Loop (cost=7.01..574.51 rows=1 width=215) (actual time=24,322.408..24,322.408 rows=0 loops=1)

  • Join Filter: ((client_parcels.nb = 0) OR ((client_parcels.nb > 0) AND ((NOT (concerned_parcels.geometry_area && (client_parcels.geometry_area)::geography)) OR (_st_distance(concerned_parcels.geometry_area, (client_parcels.geometry_area)::geography, '0'::double precision, false) >= '1e-05'::double precision))))
14. 39.251 24,322.407 ↓ 0.0 0 1
→

Nested Loop (cost=7.01..574.21 rows=1 width=183) (actual time=24,322.407..24,322.407 rows=0 loops=1)

  • Join Filter: (((concerned_parcels.geometry_area)::geometry ~ tep.point_geometry) AND _st_contains((concerned_parcels.geometry_area)::geometry, tep.point_geometry))
  • Rows Removed by Join Filter: 26614
15. 5.871 11.188 ↓ 1,901.0 1,901 1
→

Bitmap Heap Scan on calculations_gps tep (cost=7.01..8.12 rows=1 width=87) (actual time=5.450..11.188 rows=1,901 loops=1)

  • Recheck Cond: ((parcel_id IS NULL) AND (traccar_session_id = 589541))
  • Heap Blocks: exact=378
16. 0.024 5.317 ↓ 0.0 0 1
→

BitmapAnd (cost=7.01..7.01 rows=1 width=0) (actual time=5.317..5.317 rows=0 loops=1)

17. 1.870 1.870 ↓ 99.8 26,457 1
→

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

  • Index Cond: (parcel_id IS NULL)
18. 3.423 3.423 ↓ 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.423..3.423 rows=51,013 loops=1)

  • Index Cond: (traccar_session_id = 589541)
19. 24,271.968 24,271.968 ↑ 140.6 14 1,901
→

CTE Scan on concerned_parcels (cost=0.00..39.38 rows=1,969 width=96) (actual time=2.020..12.768 rows=14 loops=1,901)

20. 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))