explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HzQ2

Settings
# exclusive inclusive rows x rows loops node
1. 3.257 1,403.715 ↑ 1.4 142 1

Unique (cost=38,315.06..38,322.53 rows=200 width=281) (actual time=1,398.455..1,403.715 rows=142 loops=1)

2. 60.350 1,400.458 ↓ 46.5 46,387 1

Sort (cost=38,315.06..38,317.55 rows=997 width=281) (actual time=1,398.446..1,400.458 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. 20.205 1,340.108 ↓ 46.5 46,387 1

Hash Left Join (cost=26,160.93..38,265.40 rows=997 width=281) (actual time=1,034.741..1,340.108 rows=46,387 loops=1)

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

Nested Loop Left Join (cost=26,159.66..38,253.11 rows=997 width=326) (actual time=1,034.691..1,319.884 rows=46,387 loops=1)

5. 9.796 1,303.526 ↓ 46.5 46,387 1

Hash Left Join (cost=26,159.38..38,232.36 rows=997 width=322) (actual time=1,034.654..1,303.526 rows=46,387 loops=1)

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

Hash Left Join (cost=26,142.64..38,205.64 rows=997 width=311) (actual time=1,034.601..1,293.697 rows=46,387 loops=1)

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

Nested Loop Left Join (cost=26,134.19..38,189.70 rows=997 width=287) (actual time=1,034.544..1,282.776 rows=46,387 loops=1)

8. 0.144 1,125.017 ↑ 1.6 142 1

Hash Left Join (cost=26,132.64..26,253.24 rows=224 width=199) (actual time=1,034.432..1,125.017 rows=142 loops=1)

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

GroupAggregate (cost=25,912.41..26,030.18 rows=224 width=182) (actual time=1,032.412..1,122.889 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. 63.193 1,031.061 ↓ 104.9 143,331 1

Sort (cost=25,912.41..25,915.83 rows=1,367 width=141) (actual time=1,023.036..1,031.061 rows=143,331 loops=1)

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

Gather (cost=10,251.86..25,841.22 rows=1,367 width=141) (actual time=31.635..967.868 rows=143,331 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 7.538 947.515 ↓ 83.8 47,777 3 / 3

Nested Loop Left Join (cost=9,251.86..24,704.52 rows=570 width=141) (actual time=29.764..947.515 rows=47,777 loops=3)

13. 18.608 852.275 ↓ 51.3 29,234 3 / 3

Nested Loop Left Join (cost=9,251.29..20,063.24 rows=570 width=153) (actual time=29.760..852.275 rows=29,234 loops=3)

14. 6.510 804.494 ↓ 51.2 29,173 3 / 3

Hash Left Join (cost=9,250.86..16,300.48 rows=570 width=135) (actual time=29.754..804.494 rows=29,173 loops=3)

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

Nested Loop Left Join (cost=826.81..7,853.62 rows=570 width=130) (actual time=2.352..770.782 rows=29,173 loops=3)

16. 18.399 91.291 ↓ 51.1 29,124 3 / 3

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

17. 5.998 14.643 ↓ 51.1 29,124 3 / 3

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

  • Merge Cond: (hlcamp_1.campaign_id = c.campaign_id)
18. 6.447 6.447 ↑ 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.085..6.447 rows=29,124 loops=3)

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

Sort (cost=825.40..826.52 rows=449 width=66) (actual time=2.108..2.198 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.519 1.707 ↓ 1.0 450 3 / 3

Bitmap Heap Scan on "Campaign" c (cost=29.16..805.62 rows=449 width=66) (actual time=0.237..1.707 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.188 0.188 ↑ 1.0 450 3 / 3

Bitmap Index Scan on "Campaign_dealership_started" (cost=0.00..29.05 rows=464 width=0) (actual time=0.188..0.188 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. 669.860 669.860 ↓ 0.0 0 87,373 / 3

Index Scan using "ROI_lead_campaign_id" on "ROI" roi (cost=0.42..0.61 rows=1 width=56) (actual time=0.023..0.023 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.202 27.202 ↓ 3.8 528 3 / 3

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

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

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

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

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

27. 5.295 26.537 ↑ 2.3 1,218 3 / 3

Sort (cost=8,398.52..8,405.42 rows=2,760 width=48) (actual time=26.438..26.537 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.588 21.242 ↑ 2.3 1,218 3 / 3

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

29. 2.869 20.654 ↓ 1.0 2,816 3 / 3

Sort (cost=8,199.28..8,206.23 rows=2,779 width=48) (actual time=20.398..20.654 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. 16.931 17.785 ↓ 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.187..17.785 rows=2,816 loops=3)

  • Recheck Cond: (dealership_id = 452)
  • Heap Blocks: exact=1,496
31. 0.854 0.854 ↓ 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.854..0.854 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. 87.702 87.702 ↑ 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.003 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.759 1.984 ↓ 1.0 4,190 1

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

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

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

36. 5.185 149.810 ↓ 81.8 327 142

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

37. 10.752 43.594 ↓ 25.5 102 142

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

38. 3.976 3.976 ↓ 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.007..0.028 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.003..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.001 0.031 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
42. 0.030 0.030 ↓ 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.030..0.030 rows=0 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
44. 0.032 0.032 ↓ 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.032..0.032 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.027 0.027 ↑ 1.0 1 1

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

  • Index Cond: (dealership_id = 452)
47. 0.005 0.019 ↑ 1.0 12 1

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

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

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

Planning time : 16.646 ms
Execution time : 1,408.927 ms