explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hwoh

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

Subquery Scan on fnGetRecentContactHotLead (cost=104,447.24..104,447.25 rows=1 width=20) (actual time=83.631..83.631 rows=0 loops=1)

2. 0.001 83.630 ↓ 0.0 0 1

Limit (cost=104,447.24..104,447.24 rows=1 width=3,523) (actual time=83.630..83.630 rows=0 loops=1)

3. 0.006 83.629 ↓ 0.0 0 1

Sort (cost=104,447.24..104,447.24 rows=1 width=3,523) (actual time=83.628..83.629 rows=0 loops=1)

  • Sort Key: (GREATEST((email.sent_on)::date, (email.status_received_on)::date, (email.added_on)::date, (directmail.mms_sent_tstamp)::date, (directmail.added_on)::date, (camp.started_tstamp)::date, (hla.added_tstamp)::date, (hlw.added_tstamp)::date, (phone.added_tstamp)::date))
  • Sort Method: quicksort Memory: 25kB
4. 0.024 83.623 ↓ 0.0 0 1

Nested Loop (cost=2,102.24..104,447.23 rows=1 width=3,523) (actual time=83.623..83.623 rows=0 loops=1)

  • Join Filter: (GREATEST((email.sent_on)::date, (email.status_received_on)::date, (email.added_on)::date, (directmail.mms_sent_tstamp)::date, (directmail.added_on)::date, (camp.started_tstamp)::date, (hla.added_tstamp)::date, (hlw.added_tstamp)::date, (phone.added_tstamp)::date) > (CURRENT_DATE - ((sp.exclude_recent_contact_days)::double precision * '1 day'::interval)))
  • Rows Removed by Join Filter: 4
5. 0.186 83.583 ↓ 4.0 4 1

Nested Loop Anti Join (cost=2,101.97..104,438.87 rows=1 width=92) (actual time=51.604..83.583 rows=4 loops=1)

  • Join Filter: ((roih.dealership_id = roi.dealership_id) AND (roih.roi_id = roi.roi_id) AND (roih.deal_date >= GREATEST((email.sent_on)::date, (email.status_received_on)::date, (email.added_on)::date, (directmail.mms_sent_tstamp)::date, (directmail.added_on)::date, (camp.started_tstamp)::date, (hla.added_tstamp)::date, (hlw.added_tstamp)::date, (phone.added_tstamp)::date)))
  • Rows Removed by Join Filter: 1,604
6. 0.006 80.501 ↓ 4.0 4 1

Nested Loop Left Join (cost=682.90..103,005.72 rows=1 width=112) (actual time=49.411..80.501 rows=4 loops=1)

7. 0.011 79.327 ↓ 4.0 4 1

Nested Loop Left Join (cost=682.48..102,249.82 rows=1 width=92) (actual time=49.012..79.327 rows=4 loops=1)

  • Filter: ((hl.customer_id = 'f6113a23-f992-4d67-a6c0-d405ab7355bc'::uuid) OR (GREATEST((email.sent_on)::date, (email.status_received_on)::date, (email.added_on)::date, (directmail.mms_sent_tstamp)::date, (directmail.added_on)::date, (camp.started_tstamp)::date, (hla.added_tstamp)::date, (hlw.added_tstamp)::date, (phone.added_tstamp)::date) > '2016-08-01'::date))
8. 0.005 79.300 ↓ 4.0 4 1

Nested Loop Left Join (cost=682.06..102,247.99 rows=1 width=120) (actual time=48.997..79.300 rows=4 loops=1)

9. 0.006 79.279 ↓ 4.0 4 1

Nested Loop Left Join (cost=681.64..102,246.20 rows=1 width=104) (actual time=48.988..79.279 rows=4 loops=1)

10. 0.003 79.254 ↑ 1.0 1 1

Nested Loop Left Join (cost=681.08..102,238.08 rows=1 width=80) (actual time=48.973..79.254 rows=1 loops=1)

11. 0.002 79.249 ↑ 1.0 1 1

Nested Loop Left Join (cost=680.66..102,237.60 rows=1 width=92) (actual time=48.968..79.249 rows=1 loops=1)

12. 0.003 79.237 ↑ 1.0 1 1

Nested Loop Left Join (cost=680.23..102,229.13 rows=1 width=72) (actual time=48.955..79.237 rows=1 loops=1)

13. 0.003 79.224 ↑ 1.0 1 1

Nested Loop Left Join (cost=679.81..102,224.68 rows=1 width=64) (actual time=48.943..79.224 rows=1 loops=1)

14. 0.009 79.210 ↑ 1.0 1 1

Nested Loop (cost=679.38..102,220.22 rows=1 width=56) (actual time=48.929..79.210 rows=1 loops=1)

15. 74.025 79.165 ↓ 3.0 3 1

Bitmap Heap Scan on "HotLead" hl (cost=678.95..102,211.76 rows=1 width=36) (actual time=22.310..79.165 rows=3 loops=1)

  • Recheck Cond: (dealership_id = 406)
  • Filter: ((curr_veh_vin = '4T1BF1FK3GU214856'::text) OR (customer_id = 'f6113a23-f992-4d67-a6c0-d405ab7355bc'::uuid))
  • Rows Removed by Filter: 30,637
  • Heap Blocks: exact=9,096
16. 5.140 5.140 ↓ 1.0 30,640 1

Bitmap Index Scan on "HotLead_primary_dms_cust_no_idx" (cost=0.00..678.95 rows=29,669 width=0) (actual time=5.140..5.140 rows=30,640 loops=1)

  • Index Cond: (dealership_id = 406)
17. 0.036 0.036 ↓ 0.0 0 3

Index Scan using "HotLeadCommunication_unique_source" on "HotLeadCommunication" hlcomm (cost=0.43..8.45 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=3)

  • Index Cond: ((dealership_id = 406) AND (proposal_id = hl.proposal_id))
18. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using "idx_Appt_fnGetHotLeadAnalysis" on "HotLeadAppointment" hla (cost=0.43..4.45 rows=1 width=28) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((dealership_id = hl.dealership_id) AND (dealership_id = 406) AND (proposal_id = hl.proposal_id))
  • Heap Fetches: 0
19. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using "idx_Walkin_fnGetHotLeadAnalysis" on "HotLeadWalkin" hlw (cost=0.42..4.45 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((dealership_id = hl.dealership_id) AND (dealership_id = 406) AND (proposal_id = hl.proposal_id))
  • Heap Fetches: 0
20. 0.010 0.010 ↓ 0.0 0 1

Index Scan using "HotLeadCampaign_proposal_id_removed_idx" on "HotLeadCampaign" hlc (cost=0.43..8.46 rows=1 width=36) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: ((dealership_id = hl.dealership_id) AND (dealership_id = 406) AND (hl.proposal_id = proposal_id) AND (removed_tstamp IS NULL))
21. 0.002 0.002 ↓ 0.0 0 1

Index Only Scan using "Campaign_campaign_id" on "Campaign" camp (cost=0.41..0.48 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((dealership_id = hlc.dealership_id) AND (dealership_id = 406) AND (campaign_id = hlc.campaign_id))
  • Heap Fetches: 0
22. 0.019 0.019 ↓ 4.0 4 1

Index Scan using "EmailQueue_communication_id" on "EmailQueue" email (cost=0.56..8.11 rows=1 width=44) (actual time=0.013..0.019 rows=4 loops=1)

  • Index Cond: ((hlcomm.dealership_id = dealership_id) AND (dealership_id = 406) AND (hlcomm.communication_id = communication_id))
23. 0.016 0.016 ↓ 0.0 0 4

Index Scan using "DirectMailQueue_pkey_2" on "DirectMailQueue" directmail (cost=0.42..1.78 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: ((hlcomm.dealership_id = dealership_id) AND (dealership_id = 406))
  • Filter: (hlcomm.communication_id = communication_id)
24. 0.016 0.016 ↓ 0.0 0 4

Index Only Scan using "PhoneCallTaskSummary_comm_id_added_tstamp" on "PhoneCallTaskSummary" phone (cost=0.42..1.79 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: ((dealership_id = hlcomm.dealership_id) AND (dealership_id = 406) AND (communication_id = hlcomm.communication_id))
  • Heap Fetches: 0
25. 1.168 1.168 ↓ 0.0 0 4

Index Scan using "ROI_pkey_2" on "ROI" roi (cost=0.42..755.89 rows=1 width=36) (actual time=0.292..0.292 rows=0 loops=4)

  • Index Cond: ((hl.dealership_id = dealership_id) AND (dealership_id = 406))
  • Filter: (hl.proposal_id = proposal_id)
  • Rows Removed by Filter: 448
26. 0.256 2.896 ↓ 2.1 401 4

Subquery Scan on roih (cost=1,419.06..1,424.90 rows=194 width=24) (actual time=0.473..0.724 rows=401 loops=4)

  • Filter: (roih.is_roi IS TRUE)
  • Rows Removed by Filter: 47
27. 0.544 2.640 ↓ 1.2 448 4

Unique (cost=1,419.06..1,421.01 rows=389 width=1,481) (actual time=0.472..0.660 rows=448 loops=4)

28. 0.926 2.096 ↓ 2.0 791 4

Sort (cost=1,419.06..1,420.04 rows=389 width=1,481) (actual time=0.472..0.524 rows=791 loops=4)

  • Sort Key: "ROIHistory".roi_id, "ROIHistory".decided_tstamp DESC
  • Sort Method: quicksort Memory: 86kB
29. 1.069 1.170 ↓ 2.0 791 1

Bitmap Heap Scan on "ROIHistory" (cost=11.44..1,402.33 rows=389 width=1,481) (actual time=0.157..1.170 rows=791 loops=1)

  • Recheck Cond: (dealership_id = 406)
  • Heap Blocks: exact=322
30. 0.101 0.101 ↓ 2.0 791 1

Bitmap Index Scan on "ROIHistory_ro_id" (cost=0.00..11.34 rows=389 width=0) (actual time=0.101..0.101 rows=791 loops=1)

  • Index Cond: (dealership_id = 406)
31. 0.016 0.016 ↑ 1.0 1 4

Index Scan using "SettingsProposal_pkey" on "SettingsProposal" sp (cost=0.27..8.29 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4)

  • Index Cond: (dealership_id = 406)
Planning time : 13.729 ms
Execution time : 84.188 ms