explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L089

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 3,890.491 ↓ 261.0 261 1

Sort (cost=16,053.16..16,053.17 rows=1 width=209) (actual time=3,890.472..3,890.491 rows=261 loops=1)

  • Sort Key: hlc.proposal_id, unfilteredroih.deal_number
  • Sort Method: quicksort Memory: 66kB
2. 13.643 3,890.119 ↓ 261.0 261 1

Nested Loop Left Join (cost=10,832.01..16,053.15 rows=1 width=209) (actual time=55.731..3,890.119 rows=261 loops=1)

  • Join Filter: ((unfilteredroih.dealership_id = roi.dealership_id) AND (unfilteredroih.roi_id = roi.roi_id))
  • Rows Removed by Join Filter: 133,115
3. 5.203 3,789.824 ↓ 261.0 261 1

Nested Loop Left Join (cost=5,490.45..10,672.60 rows=1 width=157) (actual time=50.213..3,789.824 rows=261 loops=1)

  • Join Filter: ((roih.dealership_id = roi.dealership_id) AND (roih.roi_id = roi.roi_id))
  • Rows Removed by Join Filter: 121,863
4. 2.492 3,739.990 ↓ 261.0 261 1

Nested Loop Left Join (cost=32.23..5,198.14 rows=1 width=156) (actual time=43.903..3,739.990 rows=261 loops=1)

  • Join Filter: ((lastcommunication.dealership_id = hlc.dealership_id) AND (lastcommunication.campaign_id = hlc.campaign_id) AND (lastcommunication.proposal_id = hlc.proposal_id))
  • Rows Removed by Join Filter: 32,620
5. 1.205 3,704.351 ↓ 261.0 261 1

Nested Loop Left Join (cost=0.86..5,166.74 rows=1 width=136) (actual time=32.928..3,704.351 rows=261 loops=1)

  • Join Filter: ((roi.lead_dealership_id = hlc.dealership_id) AND (roi.campaign_id = hlc.campaign_id) AND (roi.proposal_id = hlc.proposal_id))
  • Rows Removed by Join Filter: 8,415
6. 1.131 2.922 ↓ 256.0 256 1

Nested Loop (cost=0.86..16.91 rows=1 width=116) (actual time=0.061..2.922 rows=256 loops=1)

7. 0.255 0.255 ↓ 256.0 256 1

Index Scan using "HotLeadCampaign_campaign_id_idx" on "HotLeadCampaign" hlc (cost=0.43..8.45 rows=1 width=36) (actual time=0.035..0.255 rows=256 loops=1)

  • Index Cond: ((dealership_id = 418) AND (campaign_id = 'd1b18522-5fd5-4ec1-b8ac-77fdc18a35e5'::uuid) AND (removed_tstamp IS NULL))
8. 1.536 1.536 ↑ 1.0 1 256

Index Scan using "HotLead_pkey_2" on "HotLead" hl (cost=0.43..8.45 rows=1 width=100) (actual time=0.006..0.006 rows=1 loops=256)

  • Index Cond: ((dealership_id = 418) AND (proposal_id = hlc.proposal_id))
9. 3,700.224 3,700.224 ↓ 33.0 33 256

Seq Scan on "ROI" roi (cost=0.00..5,149.81 rows=1 width=56) (actual time=12.270..14.454 rows=33 loops=256)

  • Filter: ((lead_dealership_id = 418) AND (campaign_id = 'd1b18522-5fd5-4ec1-b8ac-77fdc18a35e5'::uuid))
  • Rows Removed by Filter: 181,221
10. 2.610 33.147 ↓ 126.0 126 261

Subquery Scan on lastcommunication (cost=31.36..31.38 rows=1 width=76) (actual time=0.042..0.127 rows=126 loops=261)

  • Filter: (lastcommunication.campaign_id = 'd1b18522-5fd5-4ec1-b8ac-77fdc18a35e5'::uuid)
11. 11.745 30.537 ↓ 126.0 126 261

Unique (cost=31.36..31.37 rows=1 width=80) (actual time=0.042..0.117 rows=126 loops=261)

12. 8.918 18.792 ↓ 599.0 599 261

Sort (cost=31.36..31.37 rows=1 width=80) (actual time=0.041..0.072 rows=599 loops=261)

  • Sort Key: hlcom.proposal_id, hlcom.correspondence_tstamp DESC
  • Sort Method: quicksort Memory: 195kB
13. 0.264 9.874 ↓ 1,045.0 1,045 1

Nested Loop Left Join (cost=2.42..31.35 rows=1 width=80) (actual time=0.120..9.874 rows=1,045 loops=1)

  • Filter: ((dmq.communication_id IS NOT NULL) OR (eq.communication_id IS NOT NULL) OR (pcts.communication_id IS NOT NULL) OR (tq.communication_id IS NOT NULL))
14. 0.533 8.565 ↓ 1,045.0 1,045 1

Nested Loop Left Join (cost=2.27..31.12 rows=1 width=108) (actual time=0.112..8.565 rows=1,045 loops=1)

15. 0.665 5.942 ↓ 1,045.0 1,045 1

Nested Loop Left Join (cost=1.84..28.35 rows=1 width=92) (actual time=0.099..5.942 rows=1,045 loops=1)

  • Join Filter: (eq.dealership_id = hlc_1.dealership_id)
  • Filter: CASE WHEN (eq.email_id IS NOT NULL) THEN (array_position('{Success,"Bad Address","Bad Domain","Opt Out"}'::text[], CASE WHEN (COALESCE(eq.status, ''::text) <> ''::text) THEN eq.status ELSE eq.smtp_status END) >= 0) ELSE true END
16. 0.108 2.007 ↓ 218.0 218 1

Nested Loop Left Join (cost=1.28..19.99 rows=1 width=80) (actual time=0.064..2.007 rows=218 loops=1)

17. 0.062 1.463 ↓ 218.0 218 1

Nested Loop (cost=0.86..16.91 rows=1 width=64) (actual time=0.048..1.463 rows=218 loops=1)

18. 0.121 0.121 ↓ 256.0 256 1

Index Scan using "HotLeadCampaign_campaign_id_idx" on "HotLeadCampaign" hlc_1 (cost=0.43..8.45 rows=1 width=36) (actual time=0.022..0.121 rows=256 loops=1)

  • Index Cond: ((dealership_id = 418) AND (campaign_id = 'd1b18522-5fd5-4ec1-b8ac-77fdc18a35e5'::uuid))
19. 1.280 1.280 ↑ 1.0 1 256

Index Scan using "HotLeadCommunication_unique_source" on "HotLeadCommunication" hlcom (cost=0.43..8.45 rows=1 width=44) (actual time=0.004..0.005 rows=1 loops=256)

  • Index Cond: ((dealership_id = 418) AND (proposal_id = hlc_1.proposal_id))
  • Filter: (correspondence_tstamp IS NOT NULL)
  • Rows Removed by Filter: 0
20. 0.436 0.436 ↓ 0.0 0 218

Index Scan using "DirectMailQueue_pkey_2" on "DirectMailQueue" dmq (cost=0.42..3.07 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=218)

  • Index Cond: ((dealership_id = hlcom.dealership_id) AND (dealership_id = 418))
  • Filter: ((communication_id = hlcom.communication_id) AND (lower(type) = 'campaign'::text) AND (lower(status) = 'sent'::text))
21. 3.270 3.270 ↓ 5.0 5 218

Index Scan using "EmailQueue_communication_id" on "EmailQueue" eq (cost=0.56..8.34 rows=1 width=49) (actual time=0.007..0.015 rows=5 loops=218)

  • Index Cond: ((dealership_id = 418) AND (communication_id = hlcom.communication_id))
  • Filter: ((upper(type) = 'CAMPAIGN'::text) AND (upper(smtp_status) = 'SENT'::text))
22. 2.090 2.090 ↓ 0.0 0 1,045

Index Only Scan using "PhoneCallTaskSummary_comm_id_added_tstamp" on "PhoneCallTaskSummary" pcts (cost=0.42..2.76 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1,045)

  • Index Cond: ((dealership_id = hlcom.dealership_id) AND (dealership_id = 418) AND (communication_id = hlcom.communication_id))
  • Heap Fetches: 0
23. 1.045 1.045 ↓ 0.0 0 1,045

Index Scan using "TextQueue_pkey" on "TextQueue" tq (cost=0.15..0.22 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1,045)

  • Index Cond: ((dealership_id = hlcom.dealership_id) AND (dealership_id = 418))
  • Filter: (communication_id = hlcom.communication_id)
24. 9.657 44.631 ↓ 4.7 467 261

Subquery Scan on roih (cost=5,458.23..5,472.97 rows=100 width=21) (actual time=0.023..0.171 rows=467 loops=261)

  • Filter: roih.is_roi
  • Rows Removed by Filter: 57
25. 22.707 34.974 ↓ 2.6 524 261

Unique (cost=5,458.23..5,470.97 rows=200 width=39) (actual time=0.023..0.134 rows=524 loops=261)

26. 8.537 12.267 ↑ 2.7 628 261

Sort (cost=5,458.23..5,462.47 rows=1,699 width=39) (actual time=0.023..0.047 rows=628 loops=261)

  • Sort Key: roih_1.dealership_id, roih_1.deal_number, roih_1.lead_date DESC, roih_1.decided_tstamp DESC
  • Sort Method: quicksort Memory: 74kB
27. 0.305 3.730 ↑ 2.7 628 1

Unique (cost=5,341.55..5,350.08 rows=1,699 width=39) (actual time=3.300..3.730 rows=628 loops=1)

28. 1.283 3.425 ↓ 1.1 1,865 1

Sort (cost=5,341.55..5,345.82 rows=1,705 width=39) (actual time=3.299..3.425 rows=1,865 loops=1)

  • Sort Key: roih_1.roi_id, roih_1.decided_tstamp DESC
  • Sort Method: quicksort Memory: 194kB
29. 1.874 2.142 ↓ 1.1 1,865 1

Bitmap Heap Scan on "ROIHistory" roih_1 (cost=57.64..5,250.03 rows=1,705 width=39) (actual time=0.387..2.142 rows=1,865 loops=1)

  • Recheck Cond: (dealership_id = 418)
  • Heap Blocks: exact=679
30. 0.268 0.268 ↓ 1.1 1,865 1

Bitmap Index Scan on "ROIHistory_ro_id" (cost=0.00..57.21 rows=1,705 width=0) (actual time=0.268..0.268 rows=1,865 loops=1)

  • Index Cond: (dealership_id = 418)
31. 10.440 85.608 ↑ 1.7 510 261

Subquery Scan on unfilteredroih (cost=5,341.55..5,367.07 rows=850 width=61) (actual time=0.017..0.328 rows=510 loops=261)

  • Filter: unfilteredroih.is_roi
  • Rows Removed by Filter: 77
32. 44.370 75.168 ↑ 2.9 587 261

Unique (cost=5,341.55..5,350.08 rows=1,699 width=101) (actual time=0.017..0.288 rows=587 loops=261)

33. 28.314 30.798 ↓ 1.0 1,742 261

Sort (cost=5,341.55..5,345.82 rows=1,705 width=101) (actual time=0.017..0.118 rows=1,742 loops=261)

  • Sort Key: roih_2.roi_id, roih_2.decided_tstamp DESC
  • Sort Method: quicksort Memory: 311kB
34. 2.214 2.484 ↓ 1.1 1,865 1

Bitmap Heap Scan on "ROIHistory" roih_2 (cost=57.64..5,250.03 rows=1,705 width=101) (actual time=0.386..2.484 rows=1,865 loops=1)

  • Recheck Cond: (dealership_id = 418)
  • Heap Blocks: exact=679
35. 0.270 0.270 ↓ 1.1 1,865 1

Bitmap Index Scan on "ROIHistory_ro_id" (cost=0.00..57.21 rows=1,705 width=0) (actual time=0.270..0.270 rows=1,865 loops=1)

  • Index Cond: (dealership_id = 418)
36.          

SubPlan (for Nested Loop Left Join)

37. 1.044 1.044 ↑ 1.0 1 261

Result (cost=0.00..0.16 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=261)

Planning time : 4.876 ms
Execution time : 3,891.500 ms