explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3YiU

Settings
# exclusive inclusive rows x rows loops node
1. 49,358.088 49,358.088 ↓ 8.5 8,972 1

CTE Scan on data (cost=478,894.35..478,936.67 rows=1,058 width=827) (actual time=49,335.954..49,358.088 rows=8,972 loops=1)

2.          

CTE affected_terms

3. 0.026 7.846 ↓ 18.0 18 1

Unique (cost=517.49..517.50 rows=1 width=52) (actual time=7.800..7.846 rows=18 loops=1)

4. 0.058 7.820 ↓ 18.0 18 1

Sort (cost=517.49..517.49 rows=1 width=52) (actual time=7.799..7.820 rows=18 loops=1)

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

Nested Loop (cost=131.99..517.48 rows=1 width=52) (actual time=2.995..7.762 rows=18 loops=1)

6. 0.022 7.693 ↓ 18.0 18 1

Nested Loop (cost=131.71..509.17 rows=1 width=48) (actual time=2.976..7.693 rows=18 loops=1)

7. 0.023 7.599 ↓ 3.0 18 1

Nested Loop (cost=131.44..507.31 rows=6 width=52) (actual time=2.950..7.599 rows=18 loops=1)

8. 1.765 6.568 ↓ 3.0 18 1

Hash Join (cost=131.15..505.07 rows=6 width=42) (actual time=2.139..6.568 rows=18 loops=1)

  • Hash Cond: (term_utility_map.utility_id = utility.utility_id)
9. 2.899 2.899 ↓ 1.0 20,224 1

Seq Scan on term_utility_map (cost=0.00..298.08 rows=20,208 width=8) (actual time=0.009..2.899 rows=20,224 loops=1)

10. 0.005 1.904 ↑ 1.0 1 1

Hash (cost=131.14..131.14 rows=1 width=42) (actual time=1.904..1.904 rows=1 loops=1)

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

Seq Scan on utility (cost=0.00..131.14 rows=1 width=42) (actual time=0.034..1.899 rows=1 loops=1)

  • Filter: ((contract_number)::text = '10002378'::text)
  • Rows Removed by Filter: 3535
12. 1.008 1.008 ↑ 1.0 1 18

Index Scan using term_pkey on term (cost=0.29..0.36 rows=1 width=14) (actual time=0.056..0.056 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.054 0.054 ↑ 1.0 1 18

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

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

CTE transmission_base

16. 1,189.303 1,189.303 ↓ 2.0 47,623 1

Index Scan using transmission_base_by_parse_date on transmission_base (cost=0.57..40,050.68 rows=23,456 width=328) (actual time=1.156..1,189.303 rows=47,623 loops=1)

  • Index Cond: ((parse_date > '2018-12-09 00:00:00'::timestamp without time zone) AND (parse_date < '2018-12-13 00:00:00'::timestamp without time zone))
  • Filter: ((state)::text = 'GA'::text)
  • Rows Removed by Filter: 651039
17.          

CTE transmission_locate

18. 1,377.489 40,992.928 ↓ 8.5 8,972 1

Hash Join (cost=412,990.05..419,949.47 rows=1,058 width=2,438) (actual time=38,376.705..40,992.928 rows=8,972 loops=1)

  • Hash Cond: (tb.transmission_id = locate.transmission_id)
19. 1,252.128 1,252.128 ↓ 2.0 47,623 1

CTE Scan on transmission_base tb (cost=0.00..469.12 rows=23,456 width=1,841) (actual time=1.160..1,252.128 rows=47,623 loops=1)

20. 2,907.012 38,363.311 ↓ 39.7 4,518,224 1

Hash (cost=411,566.46..411,566.46 rows=113,887 width=601) (actual time=38,363.311..38,363.311 rows=4,518,224 loops=1)

  • Buckets: 4194304 (originally 131072) Batches: 4 (originally 1) Memory Usage: 229377kB
21. 1,196.935 35,456.299 ↓ 39.7 4,518,224 1

Nested Loop (cost=3,155.20..411,566.46 rows=113,887 width=601) (actual time=16.449..35,456.299 rows=4,518,224 loops=1)

22. 7.866 7.866 ↓ 18.0 18 1

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

23. 28,502.046 34,251.498 ↓ 2.2 251,012 18

Bitmap Heap Scan on locate (cost=3,155.20..410,427.57 rows=113,887 width=33) (actual time=359.545..1,902.861 rows=251,012 loops=18)

  • Recheck Cond: (client_id = affected_terms.client_id)
  • Heap Blocks: exact=1442048
24. 5,749.452 5,749.452 ↓ 2.2 254,654 18

Bitmap Index Scan on locate_by_term_closed_date (cost=0.00..3,126.73 rows=113,887 width=0) (actual time=319.414..319.414 rows=254,654 loops=18)

  • Index Cond: (client_id = affected_terms.client_id)
25.          

CTE data

26. 4.834 49,342.849 ↓ 8.5 8,972 1

Group (cost=18,310.57..18,376.69 rows=1,058 width=1,768) (actual time=49,335.920..49,342.849 rows=8,972 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
27. 20.443 49,338.015 ↓ 8.5 8,972 1

Sort (cost=18,310.57..18,313.21 rows=1,058 width=1,768) (actual time=49,335.917..49,338.015 rows=8,972 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
  • Sort Method: quicksort Memory: 3317kB
28. 10.754 49,317.572 ↓ 8.5 8,972 1

Hash Left Join (cost=22.62..18,257.42 rows=1,058 width=1,768) (actual time=38,379.787..49,317.572 rows=8,972 loops=1)

  • Hash Cond: (a.status_id = sc.status_id)
29. 9.524 49,306.541 ↓ 8.5 8,972 1

Nested Loop Left Join (cost=1.15..18,221.40 rows=1,058 width=1,741) (actual time=38,379.489..49,306.541 rows=8,972 loops=1)

30. 11.945 45,627.469 ↓ 8.5 8,972 1

Nested Loop (cost=0.57..9,119.96 rows=1,058 width=1,741) (actual time=38,378.491..45,627.469 rows=8,972 loops=1)

31. 41,012.888 41,012.888 ↓ 8.5 8,972 1

CTE Scan on transmission_locate tbl (cost=0.00..21.16 rows=1,058 width=1,707) (actual time=38,376.709..41,012.888 rows=8,972 loops=1)

32. 4,602.636 4,602.636 ↑ 1.0 1 8,972

Index Scan using transmission_detail_pkey on transmission_detail td (cost=0.57..8.59 rows=1 width=42) (actual time=0.512..0.513 rows=1 loops=8,972)

  • Index Cond: (transmission_id = tbl.transmission_id)
33. 3,669.548 3,669.548 ↑ 1.0 1 8,972

Index Scan using activity_pkey on activity a (cost=0.57..8.59 rows=1 width=8) (actual time=0.407..0.409 rows=1 loops=8,972)

  • Index Cond: (activity_id = tbl.latest_activity_id)
34. 0.113 0.277 ↑ 1.0 643 1

Hash (cost=13.43..13.43 rows=643 width=35) (actual time=0.277..0.277 rows=643 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
35. 0.164 0.164 ↑ 1.0 643 1

Seq Scan on status_code sc (cost=0.00..13.43 rows=643 width=35) (actual time=0.014..0.164 rows=643 loops=1)