explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q8hB

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 21,700.399 ↓ 20.0 20 1

Limit (cost=16,469.12..16,469.13 rows=1 width=1,302) (actual time=21,700.396..21,700.399 rows=20 loops=1)

2. 86.807 21,700.396 ↓ 20.0 20 1

Sort (cost=16,469.12..16,469.13 rows=1 width=1,302) (actual time=21,700.394..21,700.396 rows=20 loops=1)

  • Sort Key: (CASE WHEN (sub.latest_oc_cust_proposal_rating > sub.latest_event_cust_proposal_rating) THEN CASE WHEN (sub.latest_oc_cust_proposal_rating < '-1'::numeric) THEN '-1'::numeric WHEN (('-1'::numeric <= sub.latest_oc_cust_proposal_rating) AND (sub.latest_oc_cust_proposal_rating < '1'::numeric)) THEN '0'::numeric ELSE LEAST(floor(sub.latest_oc_cust_proposal_rating), '4'::numeric) END ELSE CASE WHEN (sub.latest_event_cust_proposal_rating < '-1'::numeric) THEN '-1'::numeric WHEN (('-1'::numeric <= sub.latest_event_cust_proposal_rating) AND (sub.latest_event_cust_proposal_rating < '1'::numeric)) THEN '0'::numeric ELSE LEAST(floor(sub.latest_event_cust_proposal_rating), '4'::numeric) END END) DESC, (CASE WHEN (sub.latest_oc_cust_proposal_rating > sub.latest_event_cust_proposal_rating) THEN sub.latest_oc_cust_rating ELSE sub.cust_rating END) DESC, sub.curr_veh_vin
  • Sort Method: top-N heapsort Memory: 70kB
3. 37.137 21,613.589 ↓ 14,481.0 14,481 1

Subquery Scan on sub (cost=16,342.59..16,469.11 rows=1 width=1,302) (actual time=1,982.138..21,613.589 rows=14,481 loops=1)

4. 727.761 21,576.452 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=16,342.59..16,469.07 rows=1 width=1,238) (actual time=1,982.133..21,576.452 rows=14,481 loops=1)

5. 29.026 20,476.567 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=16,342.31..16,418.08 rows=1 width=1,090) (actual time=1,981.420..20,476.567 rows=14,481 loops=1)

6. 126.790 20,447.541 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=16,342.02..16,417.76 rows=1 width=1,051) (actual time=1,981.414..20,447.541 rows=14,481 loops=1)

7. 3,719.227 20,175.941 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=16,341.59..16,409.31 rows=1 width=503) (actual time=1,981.382..20,175.941 rows=14,481 loops=1)

  • Join Filter: (("ContactHistory_3".vin = vwproposals.curr_veh_vin) AND ("ContactHistory_3".dealership_id = vwproposals.dealership_id))
  • Rows Removed by Join Filter: 31880054
8. 25.778 2,569.435 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=15,889.02..15,915.73 rows=1 width=495) (actual time=1,959.263..2,569.435 rows=14,481 loops=1)

9. 21.537 2,500.214 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=15,888.60..15,907.28 rows=1 width=388) (actual time=1,959.248..2,500.214 rows=14,481 loops=1)

10. 21.230 2,464.196 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=15,888.45..15,907.04 rows=1 width=388) (actual time=1,959.242..2,464.196 rows=14,481 loops=1)

11. 33.114 2,254.713 ↓ 14,481.0 14,481 1

Nested Loop Left Join (cost=15,888.32..15,906.71 rows=1 width=372) (actual time=1,959.208..2,254.713 rows=14,481 loops=1)

12. 37.444 2,134.713 ↓ 14,481.0 14,481 1

Nested Loop (cost=15,887.89..15,906.10 rows=1 width=234) (actual time=1,959.187..2,134.713 rows=14,481 loops=1)

13. 29.896 1,995.902 ↓ 14,481.0 14,481 1

Merge Left Join (cost=15,887.47..15,897.66 rows=1 width=214) (actual time=1,959.162..1,995.902 rows=14,481 loops=1)

  • Merge Cond: ((vwproposals.dealership_id = most_recent_contact_cust.dealership_id) AND (vwproposals.customer_id = most_recent_contact_cust.customer_id))
14. 114.957 1,958.939 ↓ 14,481.0 14,481 1

Sort (cost=15,343.78..15,343.78 rows=1 width=206) (actual time=1,952.526..1,958.939 rows=14,481 loops=1)

  • Sort Key: vwproposals.dealership_id, vwproposals.customer_id
  • Sort Method: quicksort Memory: 4818kB
15. 5.775 1,843.982 ↓ 14,481.0 14,481 1

Subquery Scan on vwproposals (cost=15,241.06..15,343.77 rows=1 width=206) (actual time=1,519.234..1,843.982 rows=14,481 loops=1)

16. 56.507 1,838.207 ↓ 14,481.0 14,481 1

WindowAgg (cost=15,241.06..15,343.76 rows=1 width=210) (actual time=1,519.233..1,838.207 rows=14,481 loops=1)

17. 20.296 1,506.561 ↓ 14,481.0 14,481 1

GroupAggregate (cost=15,241.06..15,241.10 rows=1 width=197) (actual time=1,483.755..1,506.561 rows=14,481 loops=1)

  • Group Key: proposalbase.proposal_base_id, custdms.dms_cust_no, proposalbase.dealership_id, vehiclevalue.estimated_odometer
18. 33.758 1,486.265 ↓ 16,646.0 16,646 1

Sort (cost=15,241.06..15,241.07 rows=1 width=173) (actual time=1,483.736..1,486.265 rows=16,646 loops=1)

  • Sort Key: proposalbase.proposal_base_id, custdms.dms_cust_no, vehiclevalue.estimated_odometer
  • Sort Method: quicksort Memory: 5190kB
19. 14.902 1,452.507 ↓ 16,646.0 16,646 1

Hash Left Join (cost=3,085.97..15,241.05 rows=1 width=173) (actual time=30.002..1,452.507 rows=16,646 loops=1)

  • Hash Cond: ((proposalbase.dealership_id = appt_event.dealership_id) AND (proposalbase.customer_id = appt_event.customer_id))
  • Filter: (next_appt.appt_date IS NULL)
20. 21.800 1,436.468 ↓ 164.8 16,646 1

Nested Loop Left Join (cost=2,403.33..14,556.87 rows=101 width=173) (actual time=28.850..1,436.468 rows=16,646 loops=1)

  • Join Filter: (custdms.fed_contact IS FALSE)
21. 17.312 924.648 ↓ 161.7 16,334 1

Nested Loop Anti Join (cost=2,402.07..14,202.63 rows=101 width=194) (actual time=28.781..924.648 rows=16,334 loops=1)

22. 14.019 776.664 ↓ 155.6 16,334 1

Nested Loop Anti Join (cost=2,401.66..14,130.83 rows=105 width=194) (actual time=28.754..776.664 rows=16,334 loops=1)

23. 9.101 696.309 ↓ 152.1 16,584 1

Hash Left Join (cost=2,401.37..14,072.31 rows=109 width=194) (actual time=28.729..696.309 rows=16,584 loops=1)

  • Hash Cond: (proposalbase.dealership_id = directmail_1.dealership_id)
  • Join Filter: ((custdms.address_1_standardized ~~* directmail_1.address_1_standardized) AND CASE WHEN ((directmail_1.address_2 IS NOT NULL) AND (directmail_1.address_2 <> ''::text)) THEN (custdms.address_2 = directmail_1.address_2) ELSE ((custdms.address_2 IS NULL) OR (custdms.address_2 = ''::text)) END)
24. 174.825 687.203 ↓ 152.1 16,584 1

Hash Left Join (cost=2,390.06..14,054.04 rows=109 width=221) (actual time=28.718..687.203 rows=16,584 loops=1)

  • Hash Cond: (proposalbase.dealership_id = email_1.dealership_id)
  • Join Filter: (custdms.email_address ~~* email_1.email)
  • Rows Removed by Join Filter: 149229
25. 21.111 512.359 ↓ 152.1 16,584 1

Nested Loop Left Join (cost=2,388.83..14,039.05 rows=109 width=242) (actual time=28.686..512.359 rows=16,584 loops=1)

26. 20.494 308.824 ↓ 152.1 16,584 1

Nested Loop Left Join (cost=2,388.41..13,722.42 rows=109 width=238) (actual time=28.665..308.824 rows=16,584 loops=1)

27. 20.450 188.826 ↓ 152.1 16,584 1

Nested Loop Left Join (cost=2,387.99..12,947.13 rows=109 width=161) (actual time=28.651..188.826 rows=16,584 loops=1)

  • Filter: (((dealdms_2.down_payment > '$0.00'::money) AND (dealdms_2.down_payment IS NOT NULL)) OR (proposal_1.prop_deal_down_payment <= '$0.00'::money) OR (proposal_1.prop_deal_down_payment IS NULL))
28. 21.323 118.624 ↓ 148.1 16,584 1

Hash Join (cost=2,387.57..12,631.48 rows=112 width=169) (actual time=28.611..118.624 rows=16,584 loops=1)

  • Hash Cond: (proposal_1.proposal_base_id = proposalbase.proposal_base_id)
29. 69.563 71.950 ↓ 13.6 16,662 1

Bitmap Heap Scan on "Proposal" proposal_1 (cost=515.62..10,744.02 rows=1,228 width=78) (actual time=3.247..71.950 rows=16,662 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (((satisfies_lse_filter IS NOT FALSE) OR (highly_rated IS TRUE)) AND ((prop_deal_down_payment = '$0.00'::money) OR (prop_deal_down_payment IS NULL)) AND ('-$500.00'::money <= payment_variance) AND (payment_variance <= '$500.00'::money) AND ('99'::numeric >= latest_oc_cust_proposal_rating) AND ('99'::numeric >= latest_event_cust_proposal_rating) AND ('M'::bpchar = prop_veh_trim_type) AND ('S'::bpchar = prop_deal_term_type) AND (generated_by = 'SYSTEM'::text) AND (GREATEST(floor(latest_oc_cust_proposal_rating), floor(latest_event_cust_proposal_rating)) >= '-99'::numeric))
  • Rows Removed by Filter: 4431
  • Heap Blocks: exact=4806
30. 2.387 2.387 ↑ 1.0 21,093 1

Bitmap Index Scan on "Proposal_dealership_pricelist_idx" (cost=0.00..515.31 rows=21,186 width=0) (actual time=2.387..2.387 rows=21,093 loops=1)

  • Index Cond: (dealership_id = 2)
31. 8.804 25.351 ↓ 4.0 14,608 1

Hash (cost=1,826.03..1,826.03 rows=3,674 width=145) (actual time=25.351..25.351 rows=14,608 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2653kB
32. 15.271 16.547 ↓ 4.0 14,608 1

Bitmap Heap Scan on "ProposalBase" proposalbase (cost=401.85..1,826.03 rows=3,674 width=145) (actual time=1.499..16.547 rows=14,608 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: ((NOT (hashed SubPlan 4)) AND (NOT (hashed SubPlan 5)))
  • Rows Removed by Filter: 57
  • Heap Blocks: exact=1037
33. 1.169 1.169 ↑ 1.0 14,665 1

Bitmap Index Scan on "ProposalBase_dealership_deal_id_idx" (cost=0.00..382.64 rows=14,696 width=0) (actual time=1.169..1.169 rows=14,665 loops=1)

  • Index Cond: (dealership_id = 2)
34.          

SubPlan (forBitmap Heap Scan)

35. 0.067 0.067 ↑ 1.0 104 1

Seq Scan on "ContactPending" contactpending (cost=0.00..8.89 rows=104 width=16) (actual time=0.012..0.067 rows=104 loops=1)

  • Filter: (dealership_id = 2)
  • Rows Removed by Filter: 207
36. 0.040 0.040 ↑ 1.0 104 1

Seq Scan on "ContactPending" contactpending_1 (cost=0.00..8.89 rows=104 width=18) (actual time=0.005..0.040 rows=104 loops=1)

  • Filter: (dealership_id = 2)
  • Rows Removed by Filter: 207
37. 49.752 49.752 ↓ 0.0 0 16,584

Index Scan using "DealDMS_Lookup_idx" on "DealDMS" dealdms_2 (cost=0.41..2.80 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=16,584)

  • Index Cond: ((dealership_id = proposalbase.dealership_id) AND (dealership_id = 2) AND (deal_id = proposalbase.curr_deal_id))
38. 99.504 99.504 ↑ 1.0 1 16,584

Index Scan using "idx_CustomerDMS_Lookup" on "CustomerDMS" custdms (cost=0.42..7.11 rows=1 width=77) (actual time=0.006..0.006 rows=1 loops=16,584)

  • Index Cond: ((dealership_id = proposalbase.dealership_id) AND (dealership_id = 2) AND (customer_id = proposalbase.customer_id))
39. 182.424 182.424 ↑ 1.0 1 16,584

Index Scan using "VehicleValue_pkey" on "VehicleValue" vehiclevalue (cost=0.42..2.90 rows=1 width=26) (actual time=0.011..0.011 rows=1 loops=16,584)

  • Index Cond: ((dealership_id = proposalbase.dealership_id) AND (dealership_id = 2) AND (vin = proposalbase.curr_veh_vin))
40. 0.008 0.019 ↑ 1.0 9 1

Hash (cost=1.11..1.11 rows=9 width=28) (actual time=0.019..0.019 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.011 0.011 ↑ 1.0 9 1

Seq Scan on "UnsubscribeEmail" email_1 (cost=0.00..1.11 rows=9 width=28) (actual time=0.009..0.011 rows=9 loops=1)

  • Filter: (dealership_id = 2)
42. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=11.28..11.28 rows=3 width=68) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.001 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on "UnsubscribeDirectMail" directmail_1 (cost=4.17..11.28 rows=3 width=68) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (dealership_id = 2)
44. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on "UnsubscribeDirectMail_pkey" (cost=0.00..4.17 rows=3 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (dealership_id = 2)
45. 66.336 66.336 ↓ 0.0 0 16,584

Index Scan using "ContactHistory_Lookup_cust_id_idx" on "ContactHistory" "ContactHistory_1" (cost=0.29..0.54 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=16,584)

  • Index Cond: ((proposalbase.dealership_id = dealership_id) AND (dealership_id = 2) AND (proposalbase.customer_id = customer_id))
  • Filter: (((now())::date - (contacted_on)::date) < 30)
  • Rows Removed by Filter: 0
46. 130.672 130.672 ↓ 0.0 0 16,334

Index Scan using "ContactHistory_Lookup_vin_idx" on "ContactHistory" (cost=0.41..0.68 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=16,334)

  • Index Cond: ((proposalbase.dealership_id = dealership_id) AND (dealership_id = 2) AND (proposalbase.curr_veh_vin = vin))
  • Filter: (((now())::date - (contacted_on)::date) < 30)
  • Rows Removed by Filter: 0
47. 7.526 490.020 ↓ 0.0 0 16,334

Nested Loop (cost=1.26..3.50 rows=1 width=20) (actual time=0.029..0.030 rows=0 loops=16,334)

48. 95.146 473.686 ↓ 0.0 0 16,334

Nested Loop Left Join (cost=0.84..2.28 rows=1 width=20) (actual time=0.027..0.029 rows=0 loops=16,334)

  • Filter: ((now())::date < CASE WHEN ((event.event_type = 'Deal'::text) OR (event.event_type = 'Service'::text)) THEN (((event.event_tstamp)::date + '1 year 6 mons'::interval))::date WHEN (event.event_type = 'Appointment'::text) THEN ((appt.appt_date + '90 days'::interval))::date ELSE NULL::date END)
  • Rows Removed by Filter: 5
49. 147.006 147.006 ↓ 1.7 5 16,334

Index Scan using "Event_customer_id_idx" on "Event" event (cost=0.42..0.81 rows=3 width=54) (actual time=0.005..0.009 rows=5 loops=16,334)

  • Index Cond: ((custdms.dealership_id = dealership_id) AND (dealership_id = 2) AND (custdms.customer_id = customer_id))
50. 231.534 231.534 ↓ 0.0 0 77,178

Index Scan using "Appointment_to_Event_fkey_idx" on "Appointment" appt (cost=0.41..0.45 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=77,178)

  • Index Cond: ((event.dealership_id = dealership_id) AND (dealership_id = 2) AND (event.event_id = event_id))
51. 8.808 8.808 ↓ 0.0 0 2,202

Index Scan using "idx_CustomerDMS_Lookup" on "CustomerDMS" custdms_1 (cost=0.42..1.22 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=2,202)

  • Index Cond: ((dealership_id = 2) AND (customer_id = event.customer_id))
  • Filter: (NOT fed_contact)
  • Rows Removed by Filter: 1
52. 0.000 1.137 ↓ 0.0 0 1

Hash (cost=682.63..682.63 rows=1 width=24) (actual time=1.137..1.137 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
53. 0.001 1.137 ↓ 0.0 0 1

Unique (cost=682.61..682.62 rows=1 width=24) (actual time=1.137..1.137 rows=0 loops=1)

54. 0.009 1.136 ↓ 0.0 0 1

Sort (cost=682.61..682.61 rows=1 width=24) (actual time=1.136..1.136 rows=0 loops=1)

  • Sort Key: appt_event.customer_id
  • Sort Method: quicksort Memory: 25kB
55. 0.000 1.127 ↓ 0.0 0 1

Nested Loop (cost=36.29..682.60 rows=1 width=24) (actual time=1.127..1.127 rows=0 loops=1)

56. 0.993 1.127 ↓ 0.0 0 1

Bitmap Heap Scan on "Appointment" next_appt (cost=35.86..674.15 rows=1 width=24) (actual time=1.127..1.127 rows=0 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: ((appt_date >= CURRENT_DATE) AND (appt_date <= (CURRENT_DATE + ('30 day'::cstring)::interval)))
  • Rows Removed by Filter: 1552
  • Heap Blocks: exact=299
57. 0.134 0.134 ↓ 1.0 1,552 1

Bitmap Index Scan on "Appointment_dealership_appt_number_idx" (cost=0.00..35.86 rows=1,543 width=0) (actual time=0.134..0.134 rows=1,552 loops=1)

  • Index Cond: (dealership_id = 2)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using "Event_pkey_2" on "Event" appt_event (cost=0.42..8.45 rows=1 width=36) (never executed)

  • Index Cond: ((dealership_id = 2) AND (event_id = next_appt.event_id))
59.          

SubPlan (forWindowAgg)

60. 86.886 173.772 ↑ 1.0 1 14,481

Aggregate (cost=51.32..51.33 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=14,481)

61. 86.886 86.886 ↑ 2.0 1 14,481

Index Scan using "Proposal_pkey_2" on "Proposal" bestproposal (cost=0.42..51.30 rows=2 width=20) (actual time=0.005..0.006 rows=1 loops=14,481)

  • Index Cond: ((dealership_id = 2) AND (proposal_id = ANY ((array_agg(proposal_1.proposal_id)))))
62. 28.962 101.367 ↑ 1.0 1 14,481

Aggregate (cost=51.31..51.32 rows=1 width=1) (actual time=0.007..0.007 rows=1 loops=14,481)

63. 72.405 72.405 ↑ 2.0 1 14,481

Index Scan using "Proposal_pkey_2" on "Proposal" bestproposal_1 (cost=0.42..51.30 rows=2 width=7) (actual time=0.004..0.005 rows=1 loops=14,481)

  • Index Cond: ((dealership_id = 2) AND (proposal_id = ANY ((array_agg(proposal_1.proposal_id)))))
64. 0.974 7.067 ↓ 1.6 2,199 1

Sort (cost=543.69..547.08 rows=1,356 width=28) (actual time=6.628..7.067 rows=2,199 loops=1)

  • Sort Key: most_recent_contact_cust.dealership_id, most_recent_contact_cust.customer_id
  • Sort Method: quicksort Memory: 268kB
65. 0.348 6.093 ↓ 1.6 2,199 1

Subquery Scan on most_recent_contact_cust (cost=452.58..473.15 rows=1,356 width=28) (actual time=4.809..6.093 rows=2,199 loops=1)

66. 0.613 5.745 ↓ 1.6 2,199 1

Unique (cost=452.58..459.59 rows=1,356 width=28) (actual time=4.807..5.745 rows=2,199 loops=1)

67. 2.265 5.132 ↓ 2.9 4,033 1

Sort (cost=452.58..456.08 rows=1,402 width=28) (actual time=4.805..5.132 rows=4,033 loops=1)

  • Sort Key: "ContactHistory_2".customer_id, "ContactHistory_2".contacted_on DESC
  • Sort Method: quicksort Memory: 412kB
68. 2.553 2.867 ↓ 2.9 4,033 1

Bitmap Heap Scan on "ContactHistory" "ContactHistory_2" (cost=124.17..379.30 rows=1,402 width=28) (actual time=0.342..2.867 rows=4,033 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (((now())::date - (contacted_on)::date) >= 30)
  • Rows Removed by Filter: 172
  • Heap Blocks: exact=99
69. 0.314 0.314 ↑ 1.0 4,205 1

Bitmap Index Scan on "ContactHistory_Lookup_cust_id_idx" (cost=0.00..123.82 rows=4,205 width=0) (actual time=0.314..0.314 rows=4,205 loops=1)

  • Index Cond: (dealership_id = 2)
70. 101.367 101.367 ↑ 1.0 1 14,481

Index Only Scan using "Customer_pkey" on "Customer" cust (cost=0.42..8.45 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=14,481)

  • Index Cond: ((dealership_id = vwproposals.dealership_id) AND (customer_id = vwproposals.customer_id))
  • Heap Fetches: 14481
71. 86.886 86.886 ↑ 1.0 1 14,481

Index Scan using "idx_CustomerDMS_Lookup" on "CustomerDMS" customerdms (cost=0.42..0.61 rows=1 width=158) (actual time=0.006..0.006 rows=1 loops=14,481)

  • Index Cond: ((dealership_id = cust.dealership_id) AND (customer_id = cust.customer_id))
72. 188.253 188.253 ↓ 0.0 0 14,481

Index Scan using "UnsubscribeEmail_pkey" on "UnsubscribeEmail" email (cost=0.14..0.32 rows=1 width=44) (actual time=0.013..0.013 rows=0 loops=14,481)

  • Index Cond: (customerdms.dealership_id = dealership_id)
  • Filter: (customerdms.email_address ~~* email)
  • Rows Removed by Filter: 9
73. 14.481 14.481 ↓ 0.0 0 14,481

Index Scan using "UnsubscribeDirectMail_pkey" on "UnsubscribeDirectMail" directmail (cost=0.15..0.23 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=14,481)

  • Index Cond: (customerdms.dealership_id = dealership_id)
  • Filter: ((customerdms.address_1_standardized ~~* address_1_standardized) AND CASE WHEN ((address_2 IS NOT NULL) AND (address_2 <> ''::text)) THEN (customerdms.address_2 = address_2) ELSE ((customerdms.address_2 IS NULL) OR (customerdms.address_2 = ''::text)) END)
74. 43.443 43.443 ↓ 0.0 0 14,481

Index Scan using "DealDMS_Lookup_idx" on "DealDMS" dealdms (cost=0.41..8.43 rows=1 width=127) (actual time=0.002..0.003 rows=0 loops=14,481)

  • Index Cond: ((dealership_id = vwproposals.dealership_id) AND (deal_id = vwproposals.curr_deal_id))
75. 9,731.232 13,887.279 ↓ 1.6 2,202 14,481

Unique (cost=452.58..459.59 rows=1,360 width=44) (actual time=0.002..0.959 rows=2,202 loops=14,481)

76. 4,153.493 4,156.047 ↓ 2.8 3,862 14,481

Sort (cost=452.58..456.08 rows=1,402 width=44) (actual time=0.002..0.287 rows=3,862 loops=14,481)

  • Sort Key: "ContactHistory_3".vin, "ContactHistory_3".contacted_on DESC
  • Sort Method: quicksort Memory: 412kB
77. 2.309 2.554 ↓ 2.9 4,033 1

Bitmap Heap Scan on "ContactHistory" "ContactHistory_3" (cost=124.17..379.30 rows=1,402 width=44) (actual time=0.271..2.554 rows=4,033 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (((now())::date - (contacted_on)::date) >= 30)
  • Rows Removed by Filter: 172
  • Heap Blocks: exact=99
78. 0.245 0.245 ↑ 1.0 4,205 1

Bitmap Index Scan on "ContactHistory_Lookup_cust_id_idx" (cost=0.00..123.82 rows=4,205 width=0) (actual time=0.245..0.245 rows=4,205 loops=1)

  • Index Cond: (dealership_id = 2)
79. 144.810 144.810 ↑ 1.0 1 14,481

Index Scan using "Proposal_pkey_2" on "Proposal" proposal (cost=0.43..8.45 rows=1 width=564) (actual time=0.010..0.010 rows=1 loops=14,481)

  • Index Cond: ((dealership_id = vwproposals.dealership_id) AND (proposal_id = (CASE WHEN (vwproposals.proposal_ids ~~* '%|%'::text) THEN "substring"(vwproposals.proposal_ids, 1, 36) ELSE vwproposals.proposal_ids END)::uuid))
80. 0.000 0.000 ↓ 0.0 0 14,481

Index Scan using "InventoryVehicle_pkey" on "InventoryVehicle" inventoryvehicle (cost=0.29..0.32 rows=1 width=83) (actual time=0.000..0.000 rows=0 loops=14,481)

  • Index Cond: ((proposal.dealership_id = dealership_id) AND (proposal.prop_veh_vin = vin))
81. 14.481 14.481 ↓ 0.0 0 14,481

Index Scan using "PriceList_pkey" on "PriceList" pricelist (cost=0.28..0.30 rows=1 width=65) (actual time=0.001..0.001 rows=0 loops=14,481)

  • Index Cond: (proposal.prop_veh_inkind_pricelist_id = pricelist_id)
82.          

SubPlan (forNested Loop Left Join)

83. 8.723 357.643 ↑ 1.0 1 8,723

Aggregate (cost=49.78..49.79 rows=1 width=4) (actual time=0.041..0.041 rows=1 loops=8,723)

84. 5.723 348.920 ↓ 0.0 0 8,723

Nested Loop (cost=1.26..49.75 rows=1 width=22) (actual time=0.038..0.040 rows=0 loops=8,723)

85. 93.304 340.197 ↓ 0.0 0 8,723

Nested Loop Left Join (cost=0.84..41.30 rows=1 width=42) (actual time=0.037..0.039 rows=0 loops=8,723)

  • Filter: ((now())::date < CASE WHEN ((event_1.event_type = 'Deal'::text) OR (event_1.event_type = 'Service'::text)) THEN (((event_1.event_tstamp)::date + '1 year 6 mons'::interval))::date WHEN (event_1.event_type = 'Appointment'::text) THEN ((appt_1.appt_date + '90 days'::interval))::date ELSE NULL::date END)
  • Rows Removed by Filter: 5
86. 113.399 113.399 ↓ 1.7 5 8,723

Index Scan using "Event_customer_id_idx" on "Event" event_1 (cost=0.42..15.87 rows=3 width=54) (actual time=0.008..0.013 rows=5 loops=8,723)

  • Index Cond: ((dealership_id = customerdms.dealership_id) AND (customer_id = customerdms.customer_id))
87. 133.494 133.494 ↓ 0.0 0 44,498

Index Scan using "Appointment_to_Event_fkey_idx" on "Appointment" appt_1 (cost=0.41..8.44 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=44,498)

  • Index Cond: ((event_1.dealership_id = dealership_id) AND (dealership_id = customerdms.dealership_id) AND (event_1.event_id = event_id))
88. 3.000 3.000 ↑ 1.0 1 600

Index Scan using "idx_CustomerDMS_Lookup" on "CustomerDMS" custdms_2 (cost=0.42..8.45 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=600)

  • Index Cond: ((dealership_id = customerdms.dealership_id) AND (customer_id = customerdms.customer_id))
  • Filter: (NOT fed_contact)