explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LYHc

Settings
# exclusive inclusive rows x rows loops node
1. 3.243 1,335.362 ↑ 1.4 142 1

Unique (cost=38,306.56..38,314.04 rows=200 width=281) (actual time=1,330.026..1,335.362 rows=142 loops=1)

2. 57.800 1,332.119 ↓ 46.5 46,387 1

Sort (cost=38,306.56..38,309.05 rows=997 width=281) (actual time=1,330.024..1,332.119 rows=46,387 loops=1)

  • Sort Key: c.started_tstamp DESC, c.campaign_id, hlcomm.correspondence_tstamp DESC NULLS LAST
  • Sort Method: quicksort Memory: 15,513kB
3. 21.235 1,274.319 ↓ 46.5 46,387 1

Hash Left Join (cost=26,152.43..38,256.90 rows=997 width=281) (actual time=969.465..1,274.319 rows=46,387 loops=1)

  • Hash Cond: (u.role_id = r.role_id)
4. 15.682 1,253.075 ↓ 46.5 46,387 1

Nested Loop Left Join (cost=26,151.16..38,244.62 rows=997 width=326) (actual time=969.446..1,253.075 rows=46,387 loops=1)

5. 10.024 1,237.393 ↓ 46.5 46,387 1

Hash Left Join (cost=26,150.89..38,223.86 rows=997 width=322) (actual time=969.432..1,237.393 rows=46,387 loops=1)

  • Hash Cond: ((hlcomm.dealership_id = phone.dealership_id) AND (hlcomm.communication_id = phone.communication_id))
6. 10.858 1,227.362 ↓ 46.5 46,387 1

Hash Left Join (cost=26,134.15..38,197.14 rows=997 width=311) (actual time=969.415..1,227.362 rows=46,387 loops=1)

  • Hash Cond: ((hlcomm.dealership_id = dmq.dealership_id) AND (hlcomm.communication_id = dmq.communication_id))
7. 7.883 1,216.496 ↓ 46.5 46,387 1

Nested Loop Left Join (cost=26,125.70..38,181.20 rows=997 width=287) (actual time=969.399..1,216.496 rows=46,387 loops=1)

8. 0.129 1,061.217 ↑ 1.6 142 1

Hash Left Join (cost=26,124.15..26,244.75 rows=224 width=199) (actual time=969.337..1,061.217 rows=142 loops=1)

  • Hash Cond: (c.started_by = u.user_name)
9. 91.147 1,059.425 ↑ 1.6 142 1

GroupAggregate (cost=25,903.92..26,021.69 rows=224 width=182) (actual time=967.657..1,059.425 rows=142 loops=1)

  • Group Key: c.campaign_id, c.dealership_id
  • Filter: bool_or((((c.started_tstamp)::date > '2020-03-30'::date) OR ((hlcomm_1.correspondence_tstamp IS NOT NULL) AND (hlcomm_1.correspondence_tstamp > '2020-03-30'::date))))
  • Rows Removed by Filter: 308
10. 54.405 968.278 ↓ 104.9 143,331 1

Sort (cost=25,903.92..25,907.34 rows=1,367 width=141) (actual time=959.161..968.278 rows=143,331 loops=1)

  • Sort Key: c.campaign_id
  • Sort Method: quicksort Memory: 26,505kB
11. 20.449 913.873 ↓ 104.9 143,331 1

Gather (cost=10,251.86..25,832.72 rows=1,367 width=141) (actual time=19.803..913.873 rows=143,331 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 25.050 893.424 ↓ 83.8 47,777 3 / 3

Nested Loop Left Join (cost=9,251.86..24,696.02 rows=570 width=141) (actual time=28.327..893.424 rows=47,777 loops=3)

13. 13.530 809.906 ↓ 51.3 29,234 3 / 3

Nested Loop Left Join (cost=9,251.29..20,054.74 rows=570 width=153) (actual time=28.323..809.906 rows=29,234 loops=3)

14. 6.366 767.203 ↓ 51.2 29,173 3 / 3

Hash Left Join (cost=9,250.86..16,291.99 rows=570 width=135) (actual time=28.316..767.203 rows=29,173 loops=3)

  • Hash Cond: ((roi.dealership_id = roih.dealership_id) AND (roi.roi_id = roih.roi_id))
15. 7.593 734.649 ↓ 51.2 29,173 3 / 3

Nested Loop Left Join (cost=826.81..7,845.13 rows=570 width=130) (actual time=1.910..734.649 rows=29,173 loops=3)

16. 14.778 86.321 ↓ 51.1 29,124 3 / 3

Nested Loop Left Join (cost=826.39..7,501.62 rows=570 width=130) (actual time=1.862..86.321 rows=29,124 loops=3)

17. 5.787 13.294 ↓ 51.1 29,124 3 / 3

Merge Join (cost=825.96..5,214.46 rows=570 width=102) (actual time=1.795..13.294 rows=29,124 loops=3)

  • Merge Cond: (hlcamp_1.campaign_id = c.campaign_id)
18. 5.706 5.706 ↑ 1.2 29,124 3 / 3

Parallel Index Only Scan using "HotLeadCampaign_unique_key_2" on "HotLeadCampaign" hlcamp_1 (cost=0.56..4,290.27 rows=36,347 width=36) (actual time=0.050..5.706 rows=29,124 loops=3)

  • Index Cond: (dealership_id = 452)
  • Heap Fetches: 937
19. 0.453 1.801 ↑ 1.0 449 3 / 3

Sort (cost=825.40..826.52 rows=449 width=66) (actual time=1.731..1.801 rows=449 loops=3)

  • Sort Key: c.campaign_id
  • Sort Method: quicksort Memory: 74kB
  • Worker 0: Sort Method: quicksort Memory: 74kB
  • Worker 1: Sort Method: quicksort Memory: 74kB
20. 1.183 1.348 ↓ 1.0 450 3 / 3

Bitmap Heap Scan on "Campaign" c (cost=29.16..805.62 rows=449 width=66) (actual time=0.213..1.348 rows=450 loops=3)

  • Recheck Cond: ((dealership_id = 452) AND (started_tstamp IS NOT NULL))
  • Filter: (removed_on IS NULL)
  • Heap Blocks: exact=150
21. 0.165 0.165 ↑ 1.0 450 3 / 3

Bitmap Index Scan on "Campaign_dealership_started" (cost=0.00..29.05 rows=464 width=0) (actual time=0.165..0.165 rows=450 loops=3)

  • Index Cond: ((dealership_id = 452) AND (started_tstamp IS NOT NULL))
22. 58.249 58.249 ↓ 0.0 0 87,373 / 3

Index Scan using "HotLeadCommunication_proposal_id_idx" on "HotLeadCommunication" hlcomm_1 (cost=0.43..4.00 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=87,373)

  • Index Cond: ((dealership_id = hlcamp_1.dealership_id) AND (dealership_id = 452) AND (proposal_id = hlcamp_1.proposal_id))
23. 640.735 640.735 ↓ 0.0 0 87,373 / 3

Index Scan using "ROI_lead_campaign_id" on "ROI" roi (cost=0.42..0.59 rows=1 width=56) (actual time=0.022..0.022 rows=0 loops=87,373)

  • Index Cond: ((lead_dealership_id = hlcamp_1.dealership_id) AND (lead_dealership_id = 452) AND (campaign_id = hlcamp_1.campaign_id))
  • Filter: (proposal_id = hlcamp_1.proposal_id)
  • Rows Removed by Filter: 71
24. 0.200 26.188 ↓ 3.8 528 3 / 3

Hash (cost=8,421.98..8,421.98 rows=138 width=29) (actual time=26.188..26.188 rows=528 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
25. 0.161 25.988 ↓ 3.8 528 3 / 3

Subquery Scan on roih (cost=8,398.52..8,421.98 rows=138 width=29) (actual time=25.418..25.988 rows=528 loops=3)

  • Filter: (roih.is_roi IS TRUE)
  • Rows Removed by Filter: 65
26. 0.315 25.827 ↓ 2.1 593 3 / 3

Unique (cost=8,398.52..8,419.22 rows=276 width=48) (actual time=25.413..25.827 rows=593 loops=3)

27. 5.557 25.512 ↑ 2.3 1,218 3 / 3

Sort (cost=8,398.52..8,405.42 rows=2,760 width=48) (actual time=25.412..25.512 rows=1,218 loops=3)

  • Sort Key: roih_1.dealership_id, roih_1.deal_number, roih_1.lead_date DESC, roih_1.decided_tstamp DESC
  • Sort Method: quicksort Memory: 220kB
  • Worker 0: Sort Method: quicksort Memory: 220kB
  • Worker 1: Sort Method: quicksort Memory: 220kB
28. 0.602 19.955 ↑ 2.3 1,218 3 / 3

Unique (cost=8,199.28..8,213.18 rows=2,760 width=48) (actual time=19.091..19.955 rows=1,218 loops=3)

29. 2.828 19.353 ↓ 1.0 2,816 3 / 3

Sort (cost=8,199.28..8,206.23 rows=2,779 width=48) (actual time=19.090..19.353 rows=2,816 loops=3)

  • Sort Key: roih_1.roi_id, roih_1.decided_tstamp DESC
  • Sort Method: quicksort Memory: 493kB
  • Worker 0: Sort Method: quicksort Memory: 493kB
  • Worker 1: Sort Method: quicksort Memory: 493kB
30. 15.703 16.525 ↓ 1.0 2,816 3 / 3

Bitmap Heap Scan on "ROIHistory" roih_1 (cost=97.96..8,040.32 rows=2,779 width=48) (actual time=1.168..16.525 rows=2,816 loops=3)

  • Recheck Cond: (dealership_id = 452)
  • Heap Blocks: exact=1,496
31. 0.822 0.822 ↓ 1.0 2,816 3 / 3

Bitmap Index Scan on "ROIHistory_ro_id" (cost=0.00..97.27 rows=2,779 width=0) (actual time=0.822..0.822 rows=2,816 loops=3)

  • Index Cond: (dealership_id = 452)
32. 29.173 29.173 ↓ 0.0 0 87,520 / 3

Index Scan using "MMSScheduleQueue_communication_id" on "MMSScheduleQueue" mmssq (cost=0.43..6.59 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=87,520)

  • Index Cond: ((dealership_id = hlcomm_1.dealership_id) AND (dealership_id = 452) AND (communication_id = hlcomm_1.communication_id))
33. 58.468 58.468 ↑ 1.0 1 87,702 / 3

Index Scan using "EmailQueue_communication_id" on "EmailQueue" eq_1 (cost=0.56..8.13 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=87,702)

  • Index Cond: ((dealership_id = hlcomm_1.dealership_id) AND (dealership_id = 452) AND (communication_id = hlcomm_1.communication_id))
  • Filter: (upper(type) = 'CAMPAIGN'::text)
34. 0.752 1.663 ↓ 1.0 4,190 1

Hash (cost=167.88..167.88 rows=4,188 width=33) (actual time=1.663..1.663 rows=4,190 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
35. 0.911 0.911 ↓ 1.0 4,190 1

Seq Scan on "User" u (cost=0.00..167.88 rows=4,188 width=33) (actual time=0.013..0.911 rows=4,190 loops=1)

36. 5.043 147.396 ↓ 81.8 327 142

Nested Loop Left Join (cost=1.55..53.25 rows=4 width=104) (actual time=0.016..1.038 rows=327 loops=142)

37. 8.906 41.322 ↓ 25.5 102 142

Nested Loop Left Join (cost=0.99..20.66 rows=4 width=44) (actual time=0.010..0.291 rows=102 loops=142)

38. 3.550 3.550 ↓ 25.5 102 142

Index Only Scan using "HotLeadCampaign_unique_key_2" on "HotLeadCampaign" hlcamp (cost=0.56..4.62 rows=4 width=32) (actual time=0.006..0.025 rows=102 loops=142)

  • Index Cond: ((dealership_id = 452) AND (campaign_id = c.campaign_id))
  • Heap Fetches: 937
39. 28.866 28.866 ↑ 1.0 1 14,433

Index Scan using "HotLeadCommunication_proposal_id_idx" on "HotLeadCommunication" hlcomm (cost=0.43..4.00 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=14,433)

  • Index Cond: ((dealership_id = 452) AND (proposal_id = hlcamp.proposal_id))
40. 101.031 101.031 ↓ 3.0 3 14,433

Index Scan using "EmailQueue_communication_id" on "EmailQueue" eq (cost=0.56..8.14 rows=1 width=64) (actual time=0.004..0.007 rows=3 loops=14,433)

  • Index Cond: ((dealership_id = hlcomm.dealership_id) AND (dealership_id = 452) AND (communication_id = hlcomm.communication_id))
  • Filter: ((upper(type) = 'CAMPAIGN'::text) AND (upper(smtp_status) = 'SENT'::text))
  • Rows Removed by Filter: 0
41. 0.000 0.008 ↓ 0.0 0 1

Hash (cost=8.43..8.43 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
42. 0.008 0.008 ↓ 0.0 0 1

Index Scan using "DirectMailQueue_communication_id" on "DirectMailQueue" dmq (cost=0.42..8.43 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (dealership_id = 452)
  • Filter: ((upper(type) = 'CAMPAIGN'::text) AND (upper(status) = 'SENT'::text))
43. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=14.80..14.80 rows=129 width=35) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
44. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using "PhoneCallTaskSummary_comm_id_outcome2" on "PhoneCallTaskSummary" phone (cost=0.55..14.80 rows=129 width=35) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (dealership_id = 452)
  • Heap Fetches: 0
45. 0.000 0.000 ↑ 1.0 1 46,387

Materialize (cost=0.27..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=46,387)

46. 0.010 0.010 ↑ 1.0 1 1

Index Scan using "SettingsCampaign_pkey" on "SettingsCampaign" sc (cost=0.27..8.29 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (dealership_id = 452)
47. 0.003 0.009 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=13) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.006 0.006 ↑ 1.0 12 1

Seq Scan on "Role" r (cost=0.00..1.12 rows=12 width=13) (actual time=0.004..0.006 rows=12 loops=1)

Planning time : 9.293 ms
Execution time : 1,338.855 ms