explain.depesz.com

PostgreSQL's explain analyze made readable

Result: snk6

Settings
# exclusive inclusive rows x rows loops node
1. 3,957.965 186,514.385 ↓ 77,470.1 12,395,219 1

Nested Loop (cost=1,000.56..3,449,138.93 rows=160 width=24) (actual time=0.841..186,514.385 rows=12,395,219 loops=1)

  • Buffers: shared hit=83949565 read=1533376 dirtied=141
2. 0.000 152,865.388 ↓ 92,784.5 14,845,516 1

Gather (cost=1,000.28..3,448,091.96 rows=160 width=20) (actual time=0.817..152,865.388 rows=14,845,516 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=46719856 read=1533376 dirtied=139
3. 12,889.155 177,733.902 ↓ 74,227.6 2,969,103 5

Nested Loop (cost=0.28..3,447,075.96 rows=40 width=20) (actual time=1.394..177,733.902 rows=2,969,103 loops=5)

  • Buffers: shared hit=46718966 read=1533374 dirtied=139
  • # All entries have lat/ln
  • -?\d{1,2}\.\d{1,12}, # lat
  • -?\d{1,3}\.\d{1,12} # lon
  • ( # older firmware only supports lat/lon, there are two extended versions:
  • | # empty, the first version
  • ,\d{1,3}\.\d{1,2} # speed in kmh
  • ,\d{1,3}\.\d{1,3} # speeed in kmh
  • ,\d\.\d{1,8} # battery voltage
  • ,\d{1,3} # battery status
  • ,\d{1,3}\.\d{1,3} # speeed in kmh
  • ,\d\.\d{1,8} # battery voltage
  • ,\d{1,3} # battery status
  • ,\d{1,3}\.\d{1,2} # battery percentage
  • ,\d{1,3}\.\d{1,3} # speeed in kmh
  • ,\d\.\d{1,8} # battery voltage
  • ,\d{1,3} # battery status
  • ,\d{1,3}\.\d{1,2} # battery percentage
  • ,\d{1,3} # sensor data
  • $'::text) AND ((topic)::text = 'gps'::text) AND ((split_part((payload)::text, ','::text, 1))::numeric <= '90'::numeric) AND ((split_part((payload)::text, ','::text, 1))::numeric >= '-90'::numeric) AND ((split_part((payload)::text, ','::text, 2))::numeric <= '180'::numeric) AND ((split_part((payload)::text, ','::text, 2))::numeric >= '-180'::numeric))
4. 164,844.744 164,844.744 ↓ 74,227.6 2,969,103 5

Parallel Seq Scan on controller_event_log (cost=0.00..3,446,780.56 rows=40 width=20) (actual time=1.331..164,844.744 rows=2,969,103 loops=5)

  • Filter: (((payload)::text ~ '(?x) # turn on expanded regex syntax
  • Rows Removed by Filter: 5943078
  • Buffers: shared hit=1309283 read=1533369 dirtied=139
5. 0.003 0.003 ↑ 1.0 1 14,845,516

Index Scan using hardware_controller_pkey on hardware_controller hc (cost=0.28..7.38 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=14,845,516)

  • Index Cond: (id = controller_event_log.controller_id)
  • Buffers: shared hit=45409683 read=5
6. 29,691.032 29,691.032 ↑ 1.0 1 14,845,516

Index Only Scan using vehicle_pkey on vehicle v (cost=0.28..6.53 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=14,845,516)

  • Index Cond: (id = hc.vehicle_id)
  • Heap Fetches: 12395221
  • Buffers: shared hit=37229709 dirtied=2