explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GI1X

Settings
# exclusive inclusive rows x rows loops node
1. 223.201 3,174,509.115 ↓ 0.0 0 1

Update on observation (cost=364,384.22..538,060,495.15 rows=20,000,100 width=138) (actual time=3,174,509.115..3,174,509.115 rows=0 loops=1)

2. 1,569.645 3,174,285.914 ↑ 13,642.6 1,466 1

Hash Join (cost=364,384.22..538,060,495.15 rows=20,000,100 width=138) (actual time=3,172,793.651..3,174,285.914 rows=1,466 loops=1)

  • Hash Cond: (anon_2.observation_id = observation.id)
3. 0.000 3,164,453.059 ↑ 13,642.6 1,466 1

Nested Loop (cost=3.94..537,109,017.57 rows=20,000,100 width=60) (actual time=5,329.726..3,164,453.059 rows=1,466 loops=1)

4. 84.333 84.333 ↑ 1.0 200,001 1

Seq Scan on stay (cost=0.00..4,524.01 rows=200,001 width=40) (actual time=0.012..84.333 rows=200,001 loops=1)

5. 200.001 3,164,415.822 ↓ 0.0 0 200,001

Subquery Scan on anon_2 (cost=3.94..2,684.51 rows=100 width=50) (actual time=5.024..15.822 rows=0 loops=200,001)

6. 0.000 3,164,215.821 ↓ 0.0 0 200,001

Limit (cost=3.94..2,683.51 rows=100 width=42) (actual time=5.023..15.821 rows=0 loops=200,001)

7. 0.000 3,164,215.821 ↓ 0.0 0 200,001

Result (cost=3.94..27,898.21 rows=1,041 width=42) (actual time=5.023..15.821 rows=0 loops=200,001)

  • One-Time Filter: ((NOT stay.disable) AND (stay.origin = ANY ('{parkster,paybyphone,easypark,pp}'::origin[])))
8. 2.715 3,164,228.469 ↑ 1,041.0 1 1,593

Nested Loop Anti Join (cost=3.94..27,898.21 rows=1,041 width=22) (actual time=630.660..1,986.333 rows=1 loops=1,593)

9. 2.550 3,164,198.202 ↑ 1,062.0 1 1,593

Nested Loop Anti Join (cost=3.52..27,404.61 rows=1,062 width=22) (actual time=619.930..1,986.314 rows=1 loops=1,593)

10. 1,223.424 3,164,159.970 ↑ 1,084.0 1 1,593

Nested Loop (cost=3.10..26,911.43 rows=1,084 width=22) (actual time=617.288..1,986.290 rows=1 loops=1,593)

  • Join Filter: CASE WHEN stay.plate_ambiguous THEN (translate((plate.plate)::text, '- '::text, ''::text) = translate((COALESCE(observation_1.manual, match.match))::text, '- '::text, ''::text)) ELSE ((plate.plate)::text = (COALESCE(observation_1.manual, match.match))::text) END
  • Rows Removed by Join Filter: 1,659
11. 129.033 129.033 ↑ 1.0 1 1,593

Index Scan using plate_pkey on plate (cost=0.29..8.31 rows=1 width=10) (actual time=0.081..0.081 rows=1 loops=1,593)

  • Index Cond: (id = stay.plate_id)
12. 1,487.006 3,162,807.513 ↑ 1.3 1,660 1,593

Nested Loop Left Join (cost=2.81..26,859.76 rows=2,168 width=31) (actual time=6.299..1,985.441 rows=1,660 loops=1,593)

13. 9,696.606 3,146,405.985 ↑ 1.6 1,338 1,593

Hash Join (cost=2.38..25,030.31 rows=2,168 width=22) (actual time=6.232..1,975.145 rows=1,338 loops=1,593)

  • Hash Cond: (observation_1.camera_id = camera.id)
14. 11,992.599 3,136,685.499 ↑ 1.6 49,112 1,593

Nested Loop (cost=0.44..24,808.60 rows=79,130 width=26) (actual time=0.091..1,969.043 rows=49,112 loops=1,593)

15. 26.523 78.057 ↓ 2.0 2 1,593

Nested Loop (cost=0.00..5.83 rows=1 width=16) (actual time=0.025..0.049 rows=2 loops=1,593)

  • Join Filter: (area_pricing_1.pricing_id = pricing.id)
  • Rows Removed by Join Filter: 74
16. 9.558 31.860 ↓ 2.0 2 1,593

Nested Loop (cost=0.00..3.44 rows=1 width=4) (actual time=0.012..0.020 rows=2 loops=1,593)

17. 9.558 9.558 ↑ 1.0 1 1,593

Seq Scan on area (cost=0.00..1.64 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1,593)

  • Filter: (id = stay.area_id)
  • Rows Removed by Filter: 50
18. 12.744 12.744 ↓ 2.0 2 1,593

Seq Scan on area_pricing area_pricing_1 (cost=0.00..1.79 rows=1 width=8) (actual time=0.004..0.008 rows=2 loops=1,593)

  • Filter: (area_id = stay.area_id)
  • Rows Removed by Filter: 61
19. 19.674 19.674 ↑ 1.7 37 3,279

Seq Scan on pricing (cost=0.00..1.62 rows=62 width=20) (actual time=0.003..0.006 rows=37 loops=3,279)

  • Filter: (grace IS NOT NULL)
20. 3,124,614.843 3,124,614.843 ↑ 3.3 23,860 3,279

Index Scan using ix_observation_time on observation observation_1 (cost=0.44..24,011.47 rows=79,130 width=26) (actual time=0.044..952.917 rows=23,860 loops=3,279)

  • 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: 145,870
21. 9.552 23.880 ↓ 1.5 3 1,592

Hash (cost=1.91..1.91 rows=2 width=4) (actual time=0.015..0.015 rows=3 loops=1,592)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 14.328 14.328 ↓ 1.5 3 1,592

Seq Scan on camera (cost=0.00..1.91 rows=2 width=4) (actual time=0.006..0.009 rows=3 loops=1,592)

  • Filter: (area_id = stay.area_id)
  • Rows Removed by Filter: 70
23. 14,914.522 14,914.522 ↑ 2.0 1 2,130,646

Index Scan using ix_match_observation_id on match (cost=0.43..0.82 rows=2 width=13) (actual time=0.007..0.007 rows=1 loops=2,130,646)

  • Index Cond: (observation_id = observation_1.id)
24. 35.682 35.682 ↓ 0.0 0 1,878

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

  • Index Cond: (arrive_id = observation_1.id)
  • Heap Fetches: 156
25. 27.552 27.552 ↓ 0.0 0 1,722

Index Only Scan using ix_stay_leave_id on stay stay_2 (cost=0.42..0.45 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1,722)

  • Index Cond: (leave_id = observation_1.id)
  • Heap Fetches: 256
26. 6,252.714 8,263.210 ↓ 1.0 7,674,868 1

Hash (cost=163,558.68..163,558.68 rows=7,673,168 width=82) (actual time=8,263.210..8,263.210 rows=7,674,868 loops=1)

  • Buckets: 32,768 Batches: 256 Memory Usage: 3,157kB
27. 2,010.496 2,010.496 ↓ 1.0 7,674,868 1

Seq Scan on observation (cost=0.00..163,558.68 rows=7,673,168 width=82) (actual time=0.013..2,010.496 rows=7,674,868 loops=1)

Planning time : 34.486 ms
Execution time : 3,174,510.051 ms