explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ydqa

Settings
# exclusive inclusive rows x rows loops node
1. 12.568 12.568 ↓ 0.0 0 1

CTE Scan on data (cost=192,812.97..192,813.01 rows=1 width=1,121) (actual time=12.568..12.568 rows=0 loops=1)

2.          

CTE transmission_base

3. 15.760 15.771 ↓ 0.0 0 1

Gather (cost=1,000.57..178,271.29 rows=111,092 width=328) (actual time=12.461..15.771 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.011 0.011 ↓ 0.0 0 3

Parallel Index Scan using transmission_base_by_parse_date on transmission_base (cost=0.57..166,162.09 rows=46,288 width=328) (actual time=0.010..0.011 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)
5.          

CTE transmission_locate

6. 0.001 12.465 ↓ 0.0 0 1

Nested Loop (cost=13.52..14,488.86 rows=1 width=1,918) (actual time=12.465..12.465 rows=0 loops=1)

7. 0.000 12.464 ↓ 0.0 0 1

Nested Loop (cost=13.23..14,488.55 rows=1 width=1,916) (actual time=12.464..12.464 rows=0 loops=1)

  • Join Filter: (utility.customer_id = customer.customer_id)
8. 0.001 12.464 ↓ 0.0 0 1

Hash Join (cost=8.88..14,461.65 rows=24 width=1,912) (actual time=12.464..12.464 rows=0 loops=1)

  • Hash Cond: (locate.utility_id = utility.utility_id)
9. 0.001 12.463 ↓ 0.0 0 1

Nested Loop (cost=0.57..14,214.16 rows=91,017 width=1,878) (actual time=12.463..12.463 rows=0 loops=1)

10. 12.462 12.462 ↓ 0.0 0 1

CTE Scan on transmission_base tb (cost=0.00..2,499.57 rows=555 width=1,841) (actual time=12.462..12.462 rows=0 loops=1)

  • Filter: ((state)::text = 'GA'::text)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using locate_by_transmission on locate (cost=0.57..19.47 rows=164 width=41) (never executed)

  • Index Cond: (transmission_id = tb.transmission_id)
12. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=42) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using utility_by_contract on utility (cost=0.28..8.30 rows=1 width=42) (never executed)

  • Index Cond: ((contract_number)::text = '10002378'::text)
14. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.35..23.68 rows=9 width=12) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on customer (cost=4.35..23.63 rows=9 width=12) (never executed)

  • Recheck Cond: ((account_number)::text = '90001931'::text)
16. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on customer_by_account (cost=0.00..4.35 rows=9 width=0) (never executed)

  • Index Cond: ((account_number)::text = '90001931'::text)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using term_pkey on term (cost=0.29..0.31 rows=1 width=10) (never executed)

  • Index Cond: (term_id = locate.client_id)
18.          

CTE data

19. 0.001 12.568 ↓ 0.0 0 1

Group (cost=35.84..52.82 rows=1 width=1,814) (actual time=12.568..12.568 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
20. 0.093 12.567 ↓ 0.0 0 1

Sort (cost=35.84..35.85 rows=1 width=1,782) (actual time=12.567..12.567 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
21. 0.001 12.474 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.00..35.83 rows=1 width=1,782) (actual time=12.474..12.474 rows=0 loops=1)

22. 0.001 12.473 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.71..30.32 rows=1 width=1,771) (actual time=12.473..12.473 rows=0 loops=1)

23. 0.005 12.472 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.42..22.01 rows=1 width=1,771) (actual time=12.472..12.472 rows=0 loops=1)

24. 0.001 12.467 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.15..17.20 rows=1 width=1,744) (actual time=12.467..12.467 rows=0 loops=1)

25. 0.001 12.466 ↓ 0.0 0 1

Nested Loop (cost=0.57..8.61 rows=1 width=1,744) (actual time=12.466..12.466 rows=0 loops=1)

26. 12.465 12.465 ↓ 0.0 0 1

CTE Scan on transmission_locate tbl (cost=0.00..0.02 rows=1 width=1,711) (actual time=12.465..12.465 rows=0 loops=1)

27. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (transmission_id = tbl.transmission_id)
28. 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)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using status_code_pkey on status_code sc (cost=0.28..4.79 rows=1 width=35) (never executed)

  • Index Cond: (status_id = a.status_id)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using bucket_pkey on bucket (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: (tbl.bucket_id = bucket_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using usr_pkey on usr (cost=0.29..5.51 rows=1 width=19) (never executed)

  • Index Cond: (usr_id = bucket.user_id)
32.          

SubPlan (forGroup)

33. 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)
34. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: ((reason)::text ~~ 'Ongoing Project%'::text)
36. 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 : 15.180 ms
Execution time : 16.370 ms