explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3kEV

Settings
# exclusive inclusive rows x rows loops node
1. 176,885.369 176,885.369 ↓ 0.0 0 1

CTE Scan on data (cost=836,664.45..836,871.29 rows=5,171 width=1,121) (actual time=176,885.369..176,885.369 rows=0 loops=1)

2.          

CTE affected_terms

3. 0.032 5.909 ↓ 18.0 18 1

Unique (cost=386.12..386.14 rows=1 width=52) (actual time=5.865..5.909 rows=18 loops=1)

4. 0.061 5.877 ↓ 18.0 18 1

Sort (cost=386.12..386.13 rows=1 width=52) (actual time=5.864..5.877 rows=18 loops=1)

  • Sort Key: term_utility_map.term_id, term.code, utility.name
  • Sort Method: quicksort Memory: 26kB
5. 0.020 5.816 ↓ 18.0 18 1

Nested Loop (cost=9.15..386.11 rows=1 width=52) (actual time=0.449..5.816 rows=18 loops=1)

6. 0.012 5.688 ↓ 18.0 18 1

Nested Loop (cost=8.87..376.21 rows=1 width=48) (actual time=0.372..5.688 rows=18 loops=1)

7. 0.020 5.604 ↓ 3.6 18 1

Nested Loop (cost=8.60..374.71 rows=5 width=52) (actual time=0.347..5.604 rows=18 loops=1)

8. 2.744 5.494 ↓ 3.6 18 1

Hash Join (cost=8.31..372.89 rows=5 width=42) (actual time=0.318..5.494 rows=18 loops=1)

  • Hash Cond: (term_utility_map.utility_id = utility.utility_id)
9. 2.677 2.677 ↓ 1.0 21,234 1

Seq Scan on term_utility_map (cost=0.00..308.90 rows=21,190 width=8) (actual time=0.021..2.677 rows=21,234 loops=1)

10. 0.009 0.073 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=42) (actual time=0.073..0.073 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.064 0.064 ↑ 1.0 1 1

Index Scan using utility_by_contract on utility (cost=0.28..8.30 rows=1 width=42) (actual time=0.063..0.064 rows=1 loops=1)

  • Index Cond: ((contract_number)::text = '10002378'::text)
12. 0.090 0.090 ↑ 1.0 1 18

Index Scan using term_pkey on term (cost=0.29..0.36 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=18)

  • Index Cond: (term_id = term_utility_map.term_id)
13. 0.072 0.072 ↑ 1.0 1 18

Index Scan using feed_pkey on feed (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=18)

  • Index Cond: (feed_id = term.feed_id)
  • Filter: ((feed_state)::text = 'GA'::text)
14. 0.108 0.108 ↑ 1.0 1 18

Index Scan using customer_pkey on customer (cost=0.28..8.30 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=18)

  • Index Cond: (customer_id = utility.customer_id)
  • Filter: ((account_number)::text = '90001931'::text)
15.          

CTE transmission_base

16. 18.692 18.700 ↓ 0.0 0 1

Gather (cost=1,000.57..178,270.97 rows=111,092 width=328) (actual time=16.497..18.700 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 0.008 0.008 ↓ 0.0 0 3

Parallel Index Scan using transmission_base_by_parse_date on transmission_base (cost=0.57..166,161.77 rows=46,288 width=328) (actual time=0.007..0.008 rows=0 loops=3)

  • Index Cond: ((parse_date > '2019-12-09 00:00:00'::timestamp without time zone) AND (parse_date < '2018-12-16 00:00:00'::timestamp without time zone))
  • Filter: ((state)::text = 'GA'::text)
18.          

CTE transmission_locate

19. 0.007 176,885.145 ↓ 0.0 0 1

Merge Join (cost=475,593.19..476,825.86 rows=5,171 width=2,442) (actual time=176,885.145..176,885.145 rows=0 loops=1)

  • Merge Cond: (locate.transmission_id = tb.transmission_id)
20. 5,112.492 176,868.619 ↑ 125,101.0 1 1

Sort (cost=464,061.06..464,373.82 rows=125,101 width=605) (actual time=176,868.619..176,868.619 rows=1 loops=1)

  • Sort Key: locate.transmission_id
  • Sort Method: external merge Disk: 410512kB
21. 2,799.150 171,756.127 ↓ 40.3 5,046,576 1

Nested Loop (cost=3,750.11..453,469.55 rows=125,101 width=605) (actual time=18.119..171,756.127 rows=5,046,576 loops=1)

22. 5.935 5.935 ↓ 18.0 18 1

CTE Scan on affected_terms (cost=0.00..0.02 rows=1 width=572) (actual time=5.866..5.935 rows=18 loops=1)

23. 161,086.410 168,951.042 ↓ 2.2 280,365 18

Bitmap Heap Scan on locate (cost=3,750.11..452,218.52 rows=125,101 width=37) (actual time=462.632..9,386.169 rows=280,365 loops=18)

  • Recheck Cond: (client_id = affected_terms.client_id)
  • Heap Blocks: exact=1652613
24. 7,864.632 7,864.632 ↓ 2.3 286,162 18

Bitmap Index Scan on locate_by_term_closed_date (cost=0.00..3,718.83 rows=125,101 width=0) (actual time=436.924..436.924 rows=286,162 loops=18)

  • Index Cond: (client_id = affected_terms.client_id)
25. 0.014 16.519 ↓ 0.0 0 1

Sort (cost=11,532.12..11,809.85 rows=111,092 width=1,841) (actual time=16.519..16.519 rows=0 loops=1)

  • Sort Key: tb.transmission_id
  • Sort Method: quicksort Memory: 25kB
26. 16.505 16.505 ↓ 0.0 0 1

CTE Scan on transmission_base tb (cost=0.00..2,221.84 rows=111,092 width=1,841) (actual time=16.505..16.505 rows=0 loops=1)

27.          

CTE data

28. 0.003 176,885.365 ↓ 0.0 0 1

Group (cost=93,390.83..181,181.48 rows=5,171 width=1,814) (actual time=176,885.365..176,885.365 rows=0 loops=1)

  • Group Key: tbl.ticket_id, tbl.locate_id, tbl.ticket_number, tbl.code, tbl.parse_date, tbl.our_due_date, tbl.closed_date, tbl.work_date, tbl.contract_number, tbl.name, tbl.street_number, tbl.streetname, tbl.city, tbl.county, tbl.state, tbl.open, tbl.tickettype, tbl.typeofwork, tbl.account_number, td.lat, td.lon, td.excavator, sc.category, sc.description, usr.displayname
29. 0.088 176,885.362 ↓ 0.0 0 1

Sort (cost=93,390.83..93,403.76 rows=5,171 width=1,782) (actual time=176,885.361..176,885.362 rows=0 loops=1)

  • Sort Key: tbl.ticket_id, tbl.locate_id, tbl.ticket_number, tbl.code, tbl.parse_date, tbl.our_due_date, tbl.closed_date, tbl.work_date, tbl.contract_number, tbl.name, tbl.street_number, tbl.streetname, tbl.city, tbl.county, tbl.state, tbl.open, tbl.tickettype, tbl.typeofwork, tbl.account_number, td.lat, td.lon, td.excavator, sc.category, sc.description, usr.displayname
  • Sort Method: quicksort Memory: 25kB
30. 0.003 176,885.274 ↓ 0.0 0 1

Hash Left Join (cost=1,599.29..93,071.88 rows=5,171 width=1,782) (actual time=176,885.274..176,885.274 rows=0 loops=1)

  • Hash Cond: (a.status_id = sc.status_id)
31. 0.002 176,885.271 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,576.10..93,035.03 rows=5,171 width=1,755) (actual time=176,885.271..176,885.271 rows=0 loops=1)

32. 0.001 176,885.269 ↓ 0.0 0 1

Nested Loop (cost=1,575.52..48,639.21 rows=5,171 width=1,755) (actual time=176,885.269..176,885.269 rows=0 loops=1)

33. 0.114 176,885.268 ↓ 0.0 0 1

Hash Right Join (cost=1,574.95..4,264.32 rows=5,171 width=1,722) (actual time=176,885.268..176,885.268 rows=0 loops=1)

  • Hash Cond: (usr.usr_id = bucket.user_id)
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on usr (cost=0.00..2,457.75 rows=47,975 width=19) (never executed)

35. 0.002 176,885.154 ↓ 0.0 0 1

Hash (cost=1,510.31..1,510.31 rows=5,171 width=1,711) (actual time=176,885.154..176,885.154 rows=0 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 64kB
36. 0.004 176,885.152 ↓ 0.0 0 1

Hash Left Join (cost=1,393.32..1,510.31 rows=5,171 width=1,711) (actual time=176,885.152..176,885.152 rows=0 loops=1)

  • Hash Cond: (tbl.bucket_id = bucket.bucket_id)
37. 176,885.148 176,885.148 ↓ 0.0 0 1

CTE Scan on transmission_locate tbl (cost=0.00..103.42 rows=5,171 width=1,711) (actual time=176,885.148..176,885.148 rows=0 loops=1)

38. 0.000 0.000 ↓ 0.0 0

Hash (cost=845.92..845.92 rows=43,792 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on bucket (cost=0.00..845.92 rows=43,792 width=8) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using transmission_detail_pkey on transmission_detail td (cost=0.57..8.58 rows=1 width=41) (never executed)

  • Index Cond: (transmission_id = tbl.transmission_id)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using activity_pkey on activity a (cost=0.57..8.59 rows=1 width=8) (never executed)

  • Index Cond: (activity_id = tbl.latest_activity_id)
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=14.75..14.75 rows=675 width=35) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on status_code sc (cost=0.00..14.75 rows=675 width=35) (never executed)

44.          

SubPlan (forGroup)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using due_date_extension_by_ticket on due_date_extension e (cost=0.44..8.46 rows=1 width=0) (never executed)

  • Index Cond: (ticket_id = tbl.ticket_id)
  • Filter: ((reason)::text ~~ 'Ongoing Project%'::text)
46. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..764,298.89 rows=1,085,674 width=4) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
47. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on due_date_extension e_1 (cost=0.00..654,731.49 rows=452,364 width=4) (never executed)

  • Filter: ((reason)::text ~~ 'Ongoing Project%'::text)
48. 0.000 0.000 ↓ 0.0 0

Index Only Scan using project_ticket_pkey on project_ticket pt (cost=0.44..8.46 rows=1 width=0) (never executed)

  • Index Cond: (ticket_id = tbl.ticket_id)
  • Heap Fetches: 0
Planning time : 6.366 ms