explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G6U2L

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 12,250.131 ↑ 2.7 37 1

Limit (cost=2,314,500.40..2,314,500.65 rows=100 width=568) (actual time=12,250.128..12,250.131 rows=37 loops=1)

2. 0.067 12,250.127 ↑ 17.7 37 1

Sort (cost=2,314,500.40..2,314,502.04 rows=654 width=568) (actual time=12,250.126..12,250.127 rows=37 loops=1)

  • Sort Key: sub.percentage_match DESC, sub.latest_lead DESC NULLS LAST, sub.proposal_id
  • Sort Method: quicksort Memory: 38kB
3. 0.122 12,250.060 ↑ 17.7 37 1

Subquery Scan on sub (cost=2,314,357.62..2,314,475.41 rows=654 width=568) (actual time=12,249.882..12,250.060 rows=37 loops=1)

  • Filter: ((sub.latest_lead >= (CURRENT_DATE - '84 days'::interval)) OR (sub.latest_contact >= (CURRENT_DATE - '84 days'::interval)))
  • Rows Removed by Filter: 174
4. 0.069 12,249.938 ↑ 5.6 211 1

Unique (cost=2,314,357.62..2,314,445.96 rows=1,178 width=568) (actual time=12,249.853..12,249.938 rows=211 loops=1)

5. 0.695 12,249.869 ↑ 23.1 511 1

Sort (cost=2,314,357.62..2,314,387.06 rows=11,779 width=568) (actual time=12,249.850..12,249.869 rows=511 loops=1)

  • Sort Key: hl.dealership_id, hl.primary_cust_no, hlcomm.correspondence_tstamp DESC NULLS LAST, hla.added_tstamp DESC NULLS LAST
  • Sort Method: quicksort Memory: 197kB
6. 19.238 12,249.174 ↑ 23.1 511 1

Nested Loop Left Join (cost=2,247,495.01..2,313,561.12 rows=11,779 width=568) (actual time=12,227.706..12,249.174 rows=511 loops=1)

7. 1.814 12,227.521 ↑ 24.4 483 1

HashAggregate (cost=2,247,494.45..2,247,612.24 rows=11,779 width=524) (actual time=12,227.136..12,227.521 rows=483 loops=1)

  • Group Key: hl.dealership_id, hl.proposal_id, hl.primary_customer_id, hl.proposal_type, hl.curr_deal_new_used, hl.curr_deal_deal_type, hl.prop_deal_new_used, hl.prop_deal_term, hl.primary_cust_source, hl.primary_cust_no, hl.first_name, hl.last_name, hl.email, hl.address1, hl.address2, hl.city, hl.state, hl.zip_code, hl.home_phone, hl.work_phone, hl.cell_phone, ('Appointment'::text), hla.added_tstamp
8.          

CTE hl

9. 1.747 10,106.564 ↑ 2,093.3 783 1

Hash Join (cost=74.60..1,219,838.52 rows=1,639,063 width=420) (actual time=23.978..10,106.564 rows=783 loops=1)

  • Hash Cond: (hl_3.dealership_id = dealer.dealership_id)
10. 10,104.232 10,104.232 ↑ 3,372.6 486 1

Seq Scan on "HotLead" hl_3 (cost=0.00..1,098,883.02 rows=1,639,063 width=277) (actual time=23.365..10,104.232 rows=486 loops=1)

  • Filter: (((CASE WHEN ((primary_customer_id = latest_oc_customer_id) AND (latest_oc_customer_id IS NOT NULL) AND (latest_oc_customer_id <> customer_id)) THEN latest_oc_customer_source ELSE customer_source END = 1) AND (strpos(upper(primary_cust_no), 'GARLAND'::text) > 0)) OR (strpos(upper(COALESCE(CASE WHEN ((primary_customer_id = latest_oc_customer_id) AND (latest_oc_customer_id IS NOT NULL) AND (latest_oc_customer_id <> customer_id)) THEN latest_oc_cust_last_name ELSE cust_last_name END, ''::text)), 'GARLAND'::text) > 0))
  • Rows Removed by Filter: 4913363
11. 0.135 0.585 ↓ 1.4 625 1

Hash (cost=68.94..68.94 rows=453 width=4) (actual time=0.585..0.585 rows=625 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.147 0.450 ↓ 1.4 625 1

Hash Left Join (cost=22.19..68.94 rows=453 width=4) (actual time=0.181..0.450 rows=625 loops=1)

  • Hash Cond: (dealer.dealership_association_id = assoc.dealership_association_id)
13. 0.142 0.142 ↓ 1.0 455 1

Seq Scan on "Dealership" dealer (cost=0.00..16.53 rows=453 width=20) (actual time=0.009..0.142 rows=455 loops=1)

14. 0.053 0.161 ↑ 5.9 77 1

Hash (cost=16.53..16.53 rows=453 width=16) (actual time=0.161..0.161 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
15. 0.108 0.108 ↓ 1.0 455 1

Seq Scan on "Dealership" assoc (cost=0.00..16.53 rows=453 width=16) (actual time=0.003..0.108 rows=455 loops=1)

16. 0.084 12,225.707 ↑ 15.1 779 1

Append (cost=72,598.35..1,026,978.64 rows=11,779 width=524) (actual time=624.622..12,225.707 rows=779 loops=1)

17. 86.238 10,741.635 ↑ 19.4 212 1

Hash Join (cost=72,598.35..328,088.69 rows=4,118 width=524) (actual time=624.620..10,741.635 rows=212 loops=1)

  • Hash Cond: ((hl.dealership_id = hla.dealership_id) AND (hl.proposal_id = hla.proposal_id))
18. 10,107.799 10,107.799 ↑ 2,093.3 783 1

CTE Scan on hl (cost=0.00..32,781.26 rows=1,639,063 width=484) (actual time=23.983..10,107.799 rows=783 loops=1)

19. 281.331 547.598 ↓ 1.0 1,349,235 1

Hash (cost=43,147.94..43,147.94 rows=1,348,694 width=28) (actual time=547.598..547.598 rows=1,349,235 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 47666kB
20. 266.267 266.267 ↓ 1.0 1,349,235 1

Seq Scan on "HotLeadAppointment" hla (cost=0.00..43,147.94 rows=1,348,694 width=28) (actual time=0.010..266.267 rows=1,349,235 loops=1)

21. 59.720 410.330 ↑ 27.1 151 1

Hash Join (cost=49,811.10..303,146.44 rows=4,098 width=524) (actual time=352.803..410.330 rows=151 loops=1)

  • Hash Cond: ((hl_1.dealership_id = hlw.dealership_id) AND (hl_1.proposal_id = hlw.proposal_id))
22. 0.132 0.132 ↑ 2,093.3 783 1

CTE Scan on hl hl_1 (cost=0.00..32,781.26 rows=1,639,063 width=484) (actual time=0.002..0.132 rows=783 loops=1)

23. 198.479 350.478 ↓ 1.0 1,034,247 1

Hash (cost=27,244.44..27,244.44 rows=1,033,444 width=28) (actual time=350.478..350.478 rows=1,034,247 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 38474kB
24. 151.999 151.999 ↓ 1.0 1,034,247 1

Seq Scan on "HotLeadWalkin" hlw (cost=0.00..27,244.44 rows=1,033,444 width=28) (actual time=0.018..151.999 rows=1,034,247 loops=1)

25. 0.147 1,073.658 ↑ 8.6 416 1

Nested Loop (cost=120,736.06..395,566.82 rows=3,563 width=524) (actual time=851.056..1,073.658 rows=416 loops=1)

  • Join Filter: (hl_2.dealership_id = c.dealership_id)
26. 224.063 1,072.263 ↑ 9.2 416 1

Hash Join (cost=120,735.65..393,857.78 rows=3,824 width=504) (actual time=851.003..1,072.263 rows=416 loops=1)

  • Hash Cond: ((hl_2.dealership_id = hlc.dealership_id) AND (hl_2.proposal_id = hlc.proposal_id))
27. 0.119 0.119 ↑ 2,093.3 783 1

CTE Scan on hl hl_2 (cost=0.00..32,781.26 rows=1,639,063 width=484) (actual time=0.001..0.119 rows=783 loops=1)

28. 449.979 848.081 ↓ 1.0 2,439,671 1

Hash (cost=65,175.42..65,175.42 rows=2,435,482 width=36) (actual time=848.081..848.081 rows=2,439,671 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 48744kB
29. 398.102 398.102 ↓ 1.0 2,439,671 1

Seq Scan on "HotLeadCampaign" hlc (cost=0.00..65,175.42 rows=2,435,482 width=36) (actual time=0.019..398.102 rows=2,439,671 loops=1)

  • Filter: (removed_tstamp IS NULL)
  • Rows Removed by Filter: 110577
30. 1.248 1.248 ↑ 1.0 1 416

Index Scan using "Campaign_pkey_2" on "Campaign" c (cost=0.41..0.43 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=416)

  • Index Cond: ((dealership_id = hlc.dealership_id) AND (campaign_id = hlc.campaign_id))
  • Filter: ((started_tstamp IS NOT NULL) AND (removed_on IS NULL))
31. 2.415 2.415 ↑ 1.0 1 483

Index Only Scan using "HotLeadCommunication_correspondence_tstamp_idx" on "HotLeadCommunication" hlcomm (cost=0.56..3.91 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=483)

  • Index Cond: ((dealership_id = hl.dealership_id) AND (proposal_id = hl.proposal_id))
  • Heap Fetches: 16
Planning time : 4.604 ms
Execution time : 12,251.773 ms