explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rg9P

Settings
# exclusive inclusive rows x rows loops node
1. 1.544 7,489.742 ↓ 400.0 400 1

Nested Loop (cost=323,220.47..324,794.51 rows=1 width=2,327) (actual time=525.661..7,489.742 rows=400 loops=1)

  • Join Filter: (((GREATEST(hla.added_tstamp, hlw.added_tstamp, c.started_tstamp)))::date > ((now())::date - sp.exclude_recent_contact_days))
  • Rows Removed by Join Filter: 52
2. 996.413 7,486.842 ↓ 452.0 452 1

Nested Loop (cost=323,220.20..324,786.19 rows=1 width=1,879) (actual time=525.609..7,486.842 rows=452 loops=1)

  • Join Filter: ((hl.curr_veh_vin = hl_1.curr_veh_vin) OR (hl.primary_customer_id = hl_1.primary_customer_id))
  • Rows Removed by Join Filter: 9,805,148
3. 1.867 6.029 ↓ 400.0 400 1

Nested Loop (cost=0.99..54.75 rows=1 width=1,797) (actual time=0.179..6.029 rows=400 loops=1)

4. 0.562 0.562 ↓ 100.0 400 1

Index Scan using "HotLeadCampaign_campaign_id_idx" on "HotLeadCampaign" hlc (cost=0.43..20.44 rows=4 width=101) (actual time=0.072..0.562 rows=400 loops=1)

  • Index Cond: ((dealership_id = 452) AND (campaign_id = '56f7de8c-fd6e-4177-bc26-f9546559536f'::uuid) AND (removed_tstamp IS NULL))
5. 3.600 3.600 ↑ 1.0 1 400

Index Scan using "HotLead_pkey_2" on "HotLead" hl (cost=0.56..8.58 rows=1 width=1,696) (actual time=0.009..0.009 rows=1 loops=400)

  • Index Cond: ((dealership_id = 452) AND (proposal_id = hlc.proposal_id))
6. 3,811.200 6,484.400 ↑ 1.9 24,514 400

Unique (cost=323,219.21..323,568.19 rows=46,530 width=82) (actual time=1.292..16.211 rows=24,514 loops=400)

7. 2,267.451 2,673.200 ↓ 2.0 93,122 400

Sort (cost=323,219.21..323,335.54 rows=46,530 width=82) (actual time=1.292..6.683 rows=93,122 loops=400)

  • Sort Key: hl_1.primary_customer_id, hl_1.curr_veh_vin, (GREATEST(hla.added_tstamp, hlw.added_tstamp, c.started_tstamp)) DESC
  • Sort Method: quicksort Memory: 16,168kB
8. 23.607 405.749 ↓ 2.0 93,122 1

Hash Left Join (cost=15,896.78..319,611.77 rows=46,530 width=82) (actual time=91.189..405.749 rows=93,122 loops=1)

  • Hash Cond: ((hlc_1.dealership_id = c.dealership_id) AND (hlc_1.campaign_id = c.campaign_id))
  • Filter: (COALESCE(hla.added_tstamp, hlw.added_tstamp, c.started_tstamp) IS NOT NULL)
  • Rows Removed by Filter: 801
9. 16.749 381.792 ↓ 2.0 93,923 1

Hash Left Join (cost=15,855.98..319,209.11 rows=46,764 width=86) (actual time=90.815..381.792 rows=93,923 loops=1)

  • Hash Cond: ((hl_1.dealership_id = hlw.dealership_id) AND (hl_1.proposal_id = hlw.proposal_id))
10. 17.907 363.954 ↓ 2.0 93,923 1

Hash Left Join (cost=15,773.50..318,881.12 rows=46,764 width=94) (actual time=89.719..363.954 rows=93,923 loops=1)

  • Hash Cond: ((hl_1.dealership_id = hla.dealership_id) AND (hl_1.proposal_id = hla.proposal_id))
11. 33.401 328.659 ↓ 2.0 93,923 1

Hash Left Join (cost=9,813.09..312,675.19 rows=46,764 width=80) (actual time=72.314..328.659 rows=93,923 loops=1)

  • Hash Cond: ((hl_1.dealership_id = hlc_1.dealership_id) AND (hl_1.proposal_id = hlc_1.proposal_id))
12. 110.895 252.074 ↓ 2.0 93,830 1

Hash Left Join (cost=3,705.46..306,208.63 rows=46,764 width=60) (actual time=28.609..252.074 rows=93,830 loops=1)

  • Hash Cond: (hl_1.dealership_id = hlpre.dealership_id)
  • Join Filter: ((hl_1.proposal_id = hlpre.proposal_id) OR (hl_1.proposal_id = hlpre.new_proposal_id))
  • Filter: CASE WHEN ((hlpre.proposal_id IS NOT NULL) AND (hlpre.new_proposal_id IS NOT NULL)) THEN (hlpre.new_proposal_id = hl_1.proposal_id) WHEN (hlpre.proposal_id IS NOT NULL) THEN (hlpre.proposal_id = hl_1.proposal_id) ELSE true END
13. 119.541 141.170 ↓ 1.0 93,830 1

Bitmap Heap Scan on "HotLead" hl_1 (cost=3,697.28..303,979.13 rows=93,529 width=60) (actual time=28.551..141.170 rows=93,830 loops=1)

  • Recheck Cond: (dealership_id = 452)
  • Heap Blocks: exact=28,907
14. 21.629 21.629 ↓ 1.0 93,831 1

Bitmap Index Scan on "HotLead_primary_dms_cust_no_idx" (cost=0.00..3,673.90 rows=93,529 width=0) (actual time=21.629..21.629 rows=93,831 loops=1)

  • Index Cond: (dealership_id = 452)
15. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=8.17..8.17 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)

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

Index Scan using "HotLeadPrescreen_pkey" on "HotLeadPrescreen" hlpre (cost=0.15..8.17 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (dealership_id = 452)
17. 21.401 43.184 ↓ 1.0 87,373 1

Hash (cost=4,799.13..4,799.13 rows=87,233 width=36) (actual time=43.184..43.184 rows=87,373 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,827kB
18. 21.783 21.783 ↓ 1.0 87,373 1

Index Only Scan using "HotLeadCampaign_unique_key_2" on "HotLeadCampaign" hlc_1 (cost=0.56..4,799.13 rows=87,233 width=36) (actual time=0.077..21.783 rows=87,373 loops=1)

  • Index Cond: (dealership_id = 452)
  • Heap Fetches: 937
19. 1.065 17.388 ↓ 2.1 3,935 1

Hash (cost=5,932.77..5,932.77 rows=1,843 width=34) (actual time=17.383..17.388 rows=3,935 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 294kB
20. 15.372 16.323 ↓ 2.1 3,935 1

Bitmap Heap Scan on "HotLeadAppointment" hla (cost=74.71..5,932.77 rows=1,843 width=34) (actual time=1.386..16.323 rows=3,935 loops=1)

  • Recheck Cond: (dealership_id = 452)
  • Heap Blocks: exact=2,904
21. 0.951 0.951 ↓ 2.1 3,943 1

Bitmap Index Scan on "HotLeadAppointment_pkey_2" (cost=0.00..74.25 rows=1,843 width=0) (actual time=0.951..0.951 rows=3,943 loops=1)

  • Index Cond: (dealership_id = 452)
22. 0.375 1.089 ↓ 1.5 1,814 1

Hash (cost=64.92..64.92 rows=1,171 width=28) (actual time=1.089..1.089 rows=1,814 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
23. 0.714 0.714 ↓ 1.5 1,814 1

Index Only Scan using "idx_Walkin_fnGetHotLeadAnalysis" on "HotLeadWalkin" hlw (cost=0.42..64.92 rows=1,171 width=28) (actual time=0.055..0.714 rows=1,814 loops=1)

  • Index Cond: (dealership_id = 452)
  • Heap Fetches: 55
24. 0.095 0.350 ↑ 1.0 450 1

Hash (cost=33.84..33.84 rows=464 width=28) (actual time=0.350..0.350 rows=450 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
25. 0.255 0.255 ↑ 1.0 450 1

Index Only Scan using "Campaign_campaign_id" on "Campaign" c (cost=0.41..33.84 rows=464 width=28) (actual time=0.032..0.255 rows=450 loops=1)

  • Index Cond: ((dealership_id = 452) AND (started_tstamp IS NOT NULL))
  • Heap Fetches: 60
26. 1.356 1.356 ↑ 1.0 1 452

Index Scan using "SettingsProposal_pkey" on "SettingsProposal" sp (cost=0.27..8.29 rows=1 width=448) (actual time=0.003..0.003 rows=1 loops=452)

  • Index Cond: (dealership_id = 452)
Planning time : 11.460 ms
Execution time : 7,493.154 ms