explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gpto

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,060.008 ↓ 0.0 0 1

Update on observation (cost=416,924.71..628,213,052.08 rows=21,761,800 width=137) (actual time=3,060.008..3,060.008 rows=0 loops=1)

2. 0.001 3,060.007 ↓ 0.0 0 1

Hash Join (cost=416,924.71..628,213,052.08 rows=21,761,800 width=137) (actual time=3,060.007..3,060.007 rows=0 loops=1)

  • Hash Cond: (anon_2.observation_id = observation.id)
3. 147.206 3,060.006 ↓ 0.0 0 1

Nested Loop (cost=4.06..627,151,624.68 rows=21,761,800 width=60) (actual time=3,060.006..3,060.006 rows=0 loops=1)

4. 83.753 83.753 ↓ 1.0 217,619 1

Seq Scan on stay (cost=0.00..4,926.18 rows=217,618 width=40) (actual time=0.031..83.753 rows=217,619 loops=1)

5. 0.000 2,829.047 ↓ 0.0 0 217,619

Subquery Scan on anon_2 (cost=4.06..2,880.87 rows=100 width=50) (actual time=0.013..0.013 rows=0 loops=217,619)

6. 0.000 2,829.047 ↓ 0.0 0 217,619

Limit (cost=4.06..2,879.87 rows=100 width=42) (actual time=0.013..0.013 rows=0 loops=217,619)

7. 51.743 2,829.047 ↓ 0.0 0 217,619

Result (cost=4.06..59,216.93 rows=2,059 width=42) (actual time=0.013..0.013 rows=0 loops=217,619)

  • One-Time Filter: ((NOT stay.disable) AND (stay.origin = ANY ('{parkster,paybyphone,easypark,parknow}'::origin[])) AND (stay.area_id = 3))
8. 0.453 2,777.304 ↓ 0.0 0 291

Nested Loop Anti Join (cost=4.06..59,216.93 rows=2,059 width=22) (actual time=9.544..9.544 rows=0 loops=291)

9. 0.189 2,771.193 ↓ 0.0 0 291

Nested Loop Anti Join (cost=3.64..58,242.47 rows=2,098 width=22) (actual time=8.956..9.523 rows=0 loops=291)

10. 18.915 2,765.082 ↓ 0.0 0 291

Nested Loop (cost=3.22..57,269.51 rows=2,140 width=22) (actual time=8.235..9.502 rows=0 loops=291)

  • Join Filter: CASE WHEN stay.plate_ambiguous THEN (translate((plate.plate)::text, '- '::text, ''::text) = translate((COALESCE(observation_1.manual, match.match))::text, '- '::text, ''::text))
  • Rows Removed by Join Filter: 86
11. 2.037 20.952 ↑ 1.0 1 291

Nested Loop (cost=0.29..9.96 rows=1 width=14) (actual time=0.068..0.072 rows=1 loops=291)

12. 16.296 16.296 ↑ 1.0 1 291

Index Scan using plate_pkey on plate (cost=0.29..8.31 rows=1 width=10) (actual time=0.055..0.056 rows=1 loops=291)

  • Index Cond: (id = stay.plate_id)
13. 2.619 2.619 ↑ 1.0 1 291

Seq Scan on area (cost=0.00..1.64 rows=1 width=4) (actual time=0.006..0.009 rows=1 loops=291)

  • Filter: (id = stay.area_id)
  • Rows Removed by Filter: 50
14. 25.440 2,725.215 ↑ 49.8 86 291

Nested Loop Left Join (cost=2.92..57,173.95 rows=4,280 width=34) (actual time=0.525..9.365 rows=86 loops=291)

15. 37.830 2,086.470 ↑ 54.9 78 291

Hash Join (cost=2.49..53,444.78 rows=4,280 width=25) (actual time=0.367..7.170 rows=78 loops=291)

  • Hash Cond: (observation_1.camera_id = camera.id)
16. 35.211 2,043.693 ↑ 301.0 583 291

Nested Loop (cost=0.44..52,958.24 rows=175,500 width=29) (actual time=0.141..7.023 rows=583 loops=291)

17. 16.296 22.407 ↑ 2.0 1 291

Nested Loop (cost=0.00..7.26 rows=2 width=20) (actual time=0.033..0.077 rows=1 loops=291)

  • Join Filter: (area_pricing_1.pricing_id = pricing.id)
  • Rows Removed by Join Filter: 80
18. 6.111 6.111 ↑ 1.0 81 291

Seq Scan on pricing (cost=0.00..2.81 rows=81 width=20) (actual time=0.003..0.021 rows=81 loops=291)

  • Filter: (grace IS NOT NULL)
19. 0.000 0.000 ↑ 2.0 1 23,571

Materialize (cost=0.00..2.04 rows=2 width=8) (actual time=0.000..0.000 rows=1 loops=23,571)

20. 2.619 2.619 ↑ 2.0 1 291

Seq Scan on area_pricing area_pricing_1 (cost=0.00..2.03 rows=2 width=8) (actual time=0.004..0.009 rows=1 loops=291)

  • Filter: (area_id = stay.area_id)
  • Rows Removed by Filter: 81
21. 1,986.075 1,986.075 ↑ 150.5 583 291

Index Scan using ix_observation_time on observation observation_1 (cost=0.44..25,597.99 rows=87,750 width=25) (actual time=0.103..6.825 rows=583 loops=291)

  • Index Cond: (("time" >= (stay.arrive - pricing.grace)) AND ("time" <= (stay.leave + pricing.grace)))
  • Filter: ((NOT disable) AND (stay_id IS NULL))
  • Rows Removed by Filter: 5,167
22. 1.746 4.947 ↓ 2.0 4 291

Hash (cost=2.03..2.03 rows=2 width=4) (actual time=0.017..0.017 rows=4 loops=291)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 3.201 3.201 ↓ 2.0 4 291

Seq Scan on camera (cost=0.00..2.03 rows=2 width=4) (actual time=0.006..0.011 rows=4 loops=291)

  • Filter: (area_id = stay.area_id)
  • Rows Removed by Filter: 78
24. 613.305 613.305 ↓ 0.0 0 22,715

Index Scan using ix_match_observation_id on match (cost=0.43..0.85 rows=2 width=13) (actual time=0.027..0.027 rows=0 loops=22,715)

  • Index Cond: (observation_id = observation_1.id)
25. 5.922 5.922 ↓ 0.0 0 94

Index Only Scan using ix_stay_arrive_id on stay stay_1 (cost=0.42..0.45 rows=1 width=4) (actual time=0.063..0.063 rows=0 loops=94)

  • Index Cond: (arrive_id = observation_1.id)
  • Heap Fetches: 25
26. 5.658 5.658 ↑ 1.0 1 69

Index Only Scan using ix_stay_leave_id on stay stay_2 (cost=0.42..0.45 rows=1 width=4) (actual time=0.082..0.082 rows=1 loops=69)

  • Index Cond: (leave_id = observation_1.id)
  • Heap Fetches: 69
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=187,509.40..187,509.40 rows=8,765,540 width=81) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on observation (cost=0.00..187,509.40 rows=8,765,540 width=81) (never executed)

Planning time : 17.638 ms
Execution time : 3,060.202 ms