explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BKfe

Settings
# exclusive inclusive rows x rows loops node
1. 0.236 2,333.512 ↑ 4.9 17 1

Hash Left Join (cost=212,886.51..225,023.17 rows=83 width=1,676) (actual time=2,208.353..2,333.512 rows=17 loops=1)

  • Hash Cond: (c.parent_unit_id = d.id)
2. 0.015 2,333.269 ↑ 4.9 17 1

Hash Left Join (cost=212,868.64..224,997.52 rows=83 width=1,201) (actual time=2,208.260..2,333.269 rows=17 loops=1)

  • Hash Cond: (b.parent_unit_id = c.id)
3. 0.043 2,333.245 ↑ 4.9 17 1

Hash Left Join (cost=212,850.76..224,978.71 rows=83 width=1,192) (actual time=2,208.242..2,333.245 rows=17 loops=1)

  • Hash Cond: (a.parent_unit_id = b.id)
4. 3.993 2,333.183 ↑ 4.9 17 1

Merge Left Join (cost=212,832.89..224,959.91 rows=83 width=1,183) (actual time=2,208.197..2,333.183 rows=17 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: 969
5. 0.015 31.795 ↑ 4.9 17 1

Nested Loop Left Join (cost=522.36..8,815.84 rows=83 width=1,119) (actual time=31.464..31.795 rows=17 loops=1)

  • Join Filter: ((targets.client_day = stop_values.client_day) AND (targets.item_id = lookup.production_id))
6. 0.147 2.778 ↑ 4.9 17 1

Merge Left Join (cost=522.36..528.88 rows=83 width=1,121) (actual time=2.463..2.778 rows=17 loops=1)

  • Merge Cond: (stop_values.stop_id = stop_header.stop_id)
7. 0.028 1.937 ↑ 4.9 17 1

Sort (cost=433.56..433.77 rows=83 width=1,113) (actual time=1.932..1.937 rows=17 loops=1)

  • Sort Key: stop_values.stop_id
  • Sort Method: quicksort Memory: 30kB
8. 0.020 1.909 ↑ 4.9 17 1

Hash Join (cost=244.14..430.92 rows=83 width=1,113) (actual time=1.153..1.909 rows=17 loops=1)

  • Hash Cond: (stops.route_id = routes.id)
9. 0.013 1.849 ↑ 4.9 17 1

Hash Join (cost=223.51..409.14 rows=83 width=1,084) (actual time=1.105..1.849 rows=17 loops=1)

  • Hash Cond: (stop_values.stop_id = stops.id)
10. 0.444 1.195 ↑ 4.9 17 1

Hash Right Join (cost=134.12..318.61 rows=83 width=1,049) (actual time=0.456..1.195 rows=17 loops=1)

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

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

12. 0.015 0.425 ↑ 4.9 17 1

Hash (cost=133.08..133.08 rows=83 width=890) (actual time=0.425..0.425 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.023 0.410 ↑ 4.9 17 1

Hash Join (cost=34.55..133.08 rows=83 width=890) (actual time=0.392..0.410 rows=17 loops=1)

  • Hash Cond: (stop_values.stop_id = lookup.stop_id)
14. 0.034 0.034 ↑ 4.9 17 1

Index Scan using batch_id_daily_stop_values_backing on daily_stop_values_backing stop_values (cost=0.43..97.72 rows=83 width=842) (actual time=0.022..0.034 rows=17 loops=1)

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

Hash (cost=20.72..20.72 rows=1,072 width=48) (actual time=0.353..0.353 rows=1,072 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
16. 0.158 0.158 ↑ 1.0 1,072 1

Seq Scan on production_item lookup (cost=0.00..20.72 rows=1,072 width=48) (actual time=0.005..0.158 rows=1,072 loops=1)

17. 0.289 0.641 ↑ 1.0 1,049 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
18. 0.352 0.352 ↑ 1.0 1,049 1

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

  • Filter: ((deleted)::text <> 'true'::text)
  • Rows Removed by Filter: 22
19. 0.007 0.040 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.018 0.033 ↑ 1.0 21 1

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

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

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

22. 0.004 0.008 ↑ 87.5 4 1

Hash (cost=13.50..13.50 rows=350 width=41) (actual time=0.008..0.008 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.002..0.004 rows=4 loops=1)

24. 0.504 0.694 ↑ 1.0 1,014 1

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

  • Sort Key: stop_header.stop_id
  • Sort Method: quicksort Memory: 104kB
25. 0.190 0.190 ↓ 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.002..0.190 rows=1,071 loops=1)

26. 0.005 29.002 ↓ 0.0 0 17

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

27. 28.997 28.997 ↓ 0.0 0 1

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

  • Filter: (target_type_code = 1)
  • Rows Removed by Filter: 324525
28. 6.616 2,297.395 ↓ 16.3 32,453 1

Materialize (cost=212,310.52..216,136.38 rows=1,985 width=84) (actual time=2,174.457..2,297.395 rows=32,453 loops=1)

29. 30.391 2,290.779 ↓ 16.3 32,453 1

GroupAggregate (cost=212,310.52..216,111.57 rows=1,985 width=84) (actual time=2,174.446..2,290.779 rows=32,453 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day
30. 65.081 2,260.388 ↓ 2.1 42,080 1

GroupAggregate (cost=212,310.52..215,684.83 rows=19,848 width=116) (actual time=2,174.419..2,260.388 rows=42,080 loops=1)

  • Group Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
31. 145.327 2,195.307 ↑ 1.1 89,670 1

Sort (cost=212,310.52..212,558.64 rows=99,245 width=68) (actual time=2,174.374..2,195.307 rows=89,670 loops=1)

  • Sort Key: tickets_crosstab.stop_id, tickets_crosstab.client_day, ((forms.product)::text)
  • Sort Method: external merge Disk: 5024kB
32. 1,239.381 2,049.980 ↓ 1.0 99,899 1

Hash Join (cost=5,028.01..199,999.83 rows=99,245 width=68) (actual time=48.788..2,049.980 rows=99,899 loops=1)

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

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

34. 26.614 48.364 ↓ 1.0 99,899 1

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

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

Seq Scan on tickets_crosstab (cost=0.00..2,914.45 rows=99,245 width=48) (actual time=0.003..21.750 rows=99,899 loops=1)

36. 0.007 0.019 ↑ 87.5 4 1

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

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

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

38. 0.005 0.009 ↑ 87.5 4 1

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

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

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

40. 0.005 0.007 ↑ 87.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.002 0.002 ↑ 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.002 rows=4 loops=1)

Planning time : 2.816 ms