explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uBWg

Settings
# exclusive inclusive rows x rows loops node
1. 0.183 1,888.337 ↑ 6.9 16 1

Hash Left Join (cost=212,904.30..225,046.33 rows=111 width=1,625) (actual time=1,781.080..1,888.337 rows=16 loops=1)

  • Hash Cond: (c.parent_unit_id = d.id)
2. 0.008 1,888.150 ↑ 6.9 16 1

Hash Left Join (cost=212,886.42..225,018.05 rows=111 width=1,149) (actual time=1,781.024..1,888.150 rows=16 loops=1)

  • Hash Cond: (b.parent_unit_id = c.id)
3. 0.033 1,888.138 ↑ 6.9 16 1

Hash Left Join (cost=212,868.55..224,998.93 rows=111 width=1,140) (actual time=1,781.016..1,888.138 rows=16 loops=1)

  • Hash Cond: (a.parent_unit_id = b.id)
4. 3.254 1,888.091 ↑ 6.9 16 1

Merge Left Join (cost=212,850.67..224,979.81 rows=111 width=1,131) (actual time=1,780.986..1,888.091 rows=16 loops=1)

  • Merge Cond: (stop_values.stop_id = tickets_crosstab.stop_id)
  • Join Filter: (tickets_crosstab.client_day = stop_values.client_day)
  • Rows Removed by Join Filter: 645
5. 0.007 64.180 ↑ 6.9 16 1

Nested Loop Left Join (cost=540.15..8,834.54 rows=111 width=1,067) (actual time=63.949..64.180 rows=16 loops=1)

  • Join Filter: ((targets.client_day = stop_values.client_day) AND (targets.item_id = lookup.production_id))
6. 0.109 5.149 ↑ 6.9 16 1

Merge Left Join (cost=540.15..547.08 rows=111 width=1,069) (actual time=4.930..5.149 rows=16 loops=1)

  • Merge Cond: (stop_values.stop_id = stop_header.stop_id)
7. 0.042 4.040 ↑ 6.9 16 1

Sort (cost=451.35..451.63 rows=111 width=1,061) (actual time=4.037..4.040 rows=16 loops=1)

  • Sort Key: stop_values.stop_id
  • Sort Method: quicksort Memory: 29kB
8. 0.020 3.998 ↑ 6.9 16 1

Hash Join (cost=261.30..447.58 rows=111 width=1,061) (actual time=2.505..3.998 rows=16 loops=1)

  • Hash Cond: (stops.route_id = routes.id)
9. 0.914 3.918 ↑ 6.9 16 1

Hash Right Join (cost=240.66..425.42 rows=111 width=1,032) (actual time=2.434..3.918 rows=16 loops=1)

  • Hash Cond: (items.id = lookup.well_id)
10. 0.621 0.621 ↑ 1.0 4,270 1

Seq Scan on items (cost=0.00..167.70 rows=4,270 width=175) (actual time=0.004..0.621 rows=4,270 loops=1)

11. 0.025 2.383 ↑ 6.9 16 1

Hash (cost=239.28..239.28 rows=111 width=873) (actual time=2.383..2.383 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.027 2.358 ↑ 6.9 16 1

Hash Join (cost=123.89..239.28 rows=111 width=873) (actual time=2.212..2.358 rows=16 loops=1)

  • Hash Cond: (stop_values.stop_id = lookup.stop_id)
13. 0.040 1.331 ↑ 6.9 16 1

Hash Join (cost=89.82..203.54 rows=111 width=857) (actual time=1.194..1.331 rows=16 loops=1)

  • Hash Cond: (stop_values.stop_id = stops.id)
14. 0.175 0.175 ↑ 7.0 16 1

Index Scan using batch_id_daily_stop_values_backing on daily_stop_values_backing stop_values (cost=0.43..112.62 rows=112 width=790) (actual time=0.056..0.175 rows=16 loops=1)

  • Index Cond: (batch_id = '76f72369-e1eb-4454-83fe-3ebabdbff813'::uuid)
15. 0.481 1.116 ↑ 1.0 1,049 1

Hash (cost=76.06..76.06 rows=1,066 width=67) (actual time=1.116..1.116 rows=1,049 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
16. 0.635 0.635 ↑ 1.0 1,049 1

Seq Scan on stops (cost=0.00..76.06 rows=1,066 width=67) (actual time=0.012..0.635 rows=1,049 loops=1)

  • Filter: ((deleted)::text <> 'true'::text)
  • Rows Removed by Filter: 22
17. 0.653 1.000 ↓ 1.0 1,072 1

Hash (cost=20.70..20.70 rows=1,070 width=48) (actual time=1.000..1.000 rows=1,072 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
18. 0.347 0.347 ↓ 1.0 1,072 1

Seq Scan on production_item lookup (cost=0.00..20.70 rows=1,070 width=48) (actual time=0.012..0.347 rows=1,072 loops=1)

19. 0.016 0.060 ↑ 1.0 21 1

Hash (cost=20.37..20.37 rows=21 width=61) (actual time=0.060..0.060 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.022 0.044 ↑ 1.0 21 1

Hash Left Join (cost=17.88..20.37 rows=21 width=61) (actual time=0.032..0.044 rows=21 loops=1)

  • Hash Cond: (routes.organization_unit_id = a.id)
21. 0.010 0.010 ↑ 1.0 21 1

Seq Scan on routes (cost=0.00..2.21 rows=21 width=52) (actual time=0.006..0.010 rows=21 loops=1)

22. 0.008 0.012 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=41) (actual time=0.012..0.012 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↑ 87.5 4 1

Seq Scan on stg_organization_units a (cost=0.00..13.50 rows=350 width=41) (actual time=0.003..0.004 rows=4 loops=1)

24. 0.627 1.000 ↑ 1.0 1,014 1

Sort (cost=88.80..91.44 rows=1,059 width=24) (actual time=0.888..1.000 rows=1,014 loops=1)

  • Sort Key: stop_header.stop_id
  • Sort Method: quicksort Memory: 104kB
25. 0.373 0.373 ↓ 1.0 1,071 1

Seq Scan on stop_headers stop_header (cost=0.00..35.59 rows=1,059 width=24) (actual time=0.004..0.373 rows=1,071 loops=1)

26. 0.012 59.024 ↓ 0.0 0 16

Materialize (cost=0.00..8,285.52 rows=1 width=34) (actual time=3.689..3.689 rows=0 loops=16)

27. 59.012 59.012 ↓ 0.0 0 1

Seq Scan on targets (cost=0.00..8,285.51 rows=1 width=34) (actual time=59.012..59.012 rows=0 loops=1)

  • Filter: (target_type_code = 1)
  • Rows Removed by Filter: 324525
28. 5.462 1,820.657 ↓ 16.3 32,454 1

Materialize (cost=212,310.52..216,136.38 rows=1,985 width=84) (actual time=1,716.163..1,820.657 rows=32,454 loops=1)

29. 24.694 1,815.195 ↓ 16.3 32,454 1

GroupAggregate (cost=212,310.52..216,111.57 rows=1,985 width=84) (actual time=1,716.158..1,815.195 rows=32,454 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day
30. 56.362 1,790.501 ↓ 2.1 42,081 1

GroupAggregate (cost=212,310.52..215,684.83 rows=19,848 width=116) (actual time=1,716.138..1,790.501 rows=42,081 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
31. 106.876 1,734.139 ↑ 1.1 89,671 1

Sort (cost=212,310.52..212,558.64 rows=99,245 width=68) (actual time=1,716.106..1,734.139 rows=89,671 loops=1)

  • Sort Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
  • Sort Method: external merge Disk: 5024kB
32. 967.834 1,627.263 ↓ 1.0 99,900 1

Hash Join (cost=5,028.01..199,999.83 rows=99,245 width=68) (actual time=44.215..1,627.263 rows=99,900 loops=1)

  • Hash Cond: (forms.id = tickets_crosstab.form_id)
33. 615.628 615.628 ↓ 1.0 4,135,709 1

Seq Scan on forms_historical forms (cost=0.00..128,965.65 rows=4,114,265 width=20) (actual time=0.005..615.628 rows=4,135,709 loops=1)

34. 24.358 43.801 ↓ 1.0 99,900 1

Hash (cost=2,914.45..2,914.45 rows=99,245 width=48) (actual time=43.801..43.801 rows=99,900 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2549kB
35. 19.443 19.443 ↓ 1.0 99,900 1

Seq Scan on tickets_crosstab (cost=0.00..2,914.45 rows=99,245 width=48) (actual time=0.004..19.443 rows=99,900 loops=1)

36. 0.004 0.014 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=41) (actual time=0.014..0.014 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.010 0.010 ↑ 87.5 4 1

Seq Scan on stg_organization_units b (cost=0.00..13.50 rows=350 width=41) (actual time=0.009..0.010 rows=4 loops=1)

38. 0.002 0.004 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=41) (actual time=0.004..0.004 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.002 0.002 ↑ 87.5 4 1

Seq Scan on stg_organization_units c (cost=0.00..13.50 rows=350 width=41) (actual time=0.001..0.002 rows=4 loops=1)

40. 0.001 0.004 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=25) (actual time=0.004..0.004 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.003 0.003 ↑ 87.5 4 1

Seq Scan on stg_organization_units d (cost=0.00..13.50 rows=350 width=25) (actual time=0.002..0.003 rows=4 loops=1)

Planning time : 5.236 ms