explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t63I

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 266.544 ↑ 1.0 5 1

Limit (cost=93,240.37..93,243.05 rows=5 width=168) (actual time=266.522..266.544 rows=5 loops=1)

2. 0.109 266.532 ↑ 530.2 5 1

Result (cost=93,240.37..94,665.28 rows=2,651 width=168) (actual time=266.520..266.532 rows=5 loops=1)

3. 0.009 266.423 ↑ 530.2 5 1

Sort (cost=93,240.37..93,246.99 rows=2,651 width=128) (actual time=266.423..266.423 rows=5 loops=1)

  • Sort Key: (("*SELECT* 1".started_tstamp)::timestamp with time zone) DESC, "*SELECT* 1".name
  • Sort Method: quicksort Memory: 26kB
4. 0.020 266.414 ↑ 294.6 9 1

WindowAgg (cost=93,057.16..93,196.33 rows=2,651 width=128) (actual time=266.409..266.414 rows=9 loops=1)

5. 0.029 266.394 ↑ 294.6 9 1

HashAggregate (cost=93,057.16..93,083.67 rows=2,651 width=92) (actual time=266.381..266.394 rows=9 loops=1)

  • Group Key: "*SELECT* 1".dealership_id, "*SELECT* 1".campaign_id, "*SELECT* 1".name, (("*SELECT* 1".started_tstamp)::timestamp with time zone), "*SELECT* 1".unscheduled_tasks, "*SELECT* 1".follow_up_tasks, "*SELECT* 1".follow_up_today_tasks, "*SELECT* 1".completed_tasks
6. 0.003 266.365 ↑ 294.6 9 1

Append (cost=71,398.10..93,004.14 rows=2,651 width=92) (actual time=172.500..266.365 rows=9 loops=1)

7. 0.003 172.849 ↑ 530.0 5 1

Subquery Scan on *SELECT* 1 (cost=71,398.10..71,875.18 rows=2,650 width=92) (actual time=172.499..172.849 rows=5 loops=1)

8. 0.014 172.846 ↑ 530.0 5 1

Merge Join (cost=71,398.10..71,842.05 rows=2,650 width=88) (actual time=172.497..172.846 rows=5 loops=1)

  • Merge Cond: ((CASE WHEN (hlw.ro_added_tstamp IS NOT NULL) THEN "Xtream"."fnPreviousMonday"(hlw.ro_added_tstamp) ELSE "Xtream"."fnPreviousMonday"((hla.appt_date)::timestamp with time zone) END) = ((generate_series.generate_series)::date))
9. 0.973 172.753 ↑ 106.0 5 1

GroupAggregate (cost=71,338.26..71,727.89 rows=530 width=88) (actual time=172.406..172.753 rows=5 loops=1)

  • Group Key: hlcomm.dealership_id, (CASE WHEN (hlw.ro_added_tstamp IS NOT NULL) THEN "Xtream"."fnPreviousMonday"(hlw.ro_added_tstamp) ELSE "Xtream"."fnPreviousMonday"((hla.appt_date)::timestamp with time zone) END), ''::text, '00000000-0000-0000-0000-000000000000'::uuid
  • Filter: (count(CASE WHEN (CASE WHEN (("PhoneCallTaskSummary".communication_id IS NOT NULL) AND ("PhoneCallTaskSummary".outcome = ANY ('{"Appt Set-Completed","Call Task Completed","Do Not Call","Invalid Number","No Call Made","No Longer Owns","Not Interested","Sales Match"}'::text[]))) THEN 1 ELSE 0 END = 0) THEN true ELSE NULL::boolean END) > 0)
  • Rows Removed by Filter: 16
10. 0.628 171.780 ↓ 1.0 1,667 1

Sort (cost=71,338.26..71,342.24 rows=1,591 width=91) (actual time=171.714..171.780 rows=1,667 loops=1)

  • Sort Key: (CASE WHEN (hlw.ro_added_tstamp IS NOT NULL) THEN "Xtream"."fnPreviousMonday"(hlw.ro_added_tstamp) ELSE "Xtream"."fnPreviousMonday"((hla.appt_date)::timestamp with time zone) END)
  • Sort Method: quicksort Memory: 283kB
11. 13.966 171.152 ↓ 1.0 1,667 1

Hash Left Join (cost=46,638.31..71,253.66 rows=1,591 width=91) (actual time=140.649..171.152 rows=1,667 loops=1)

  • Hash Cond: ((hlcomm.dealership_id = hlw.dealership_id) AND (hlcomm.proposal_id = hlw.proposal_id))
  • Filter: ((("PhoneCallTaskSummary".communication_id IS NOT NULL) OR ((hla.added_tstamp IS NOT NULL) AND ((hla.added_tstamp + '02:00:00'::interval) < now())) OR ((hlw.added_tstamp IS NOT NULL) AND ((hlw.added_tstamp + '02:00:00'::interval) < now()))) AND (((hlw.ro_added_tstamp IS NOT NULL) AND ((((now())::date - '84 days'::interval) <= hlw.ro_added_tstamp) OR (((now())::date - '84 days'::interval) <= hlcomm.correspondence_tstamp))) OR ((hla.appt_date IS NOT NULL) AND ((((now())::date - '84 days'::interval) <= hla.appt_date) OR (((now())::date - '84 days'::interval) <= hlcomm.correspondence_tstamp)))))
  • Rows Removed by Filter: 24,214
12. 8.155 150.413 ↓ 4.5 25,881 1

Hash Right Join (cost=37,662.81..61,448.25 rows=5,798 width=75) (actual time=133.768..150.413 rows=25,881 loops=1)

  • Hash Cond: ((hla.dealership_id = hlcomm.dealership_id) AND (hla.proposal_id = hlcomm.proposal_id))
13. 9.420 11.374 ↓ 1.1 14,380 1

Bitmap Heap Scan on "HotLeadAppointment" hla (cost=501.63..24,188.90 rows=13,059 width=32) (actual time=2.865..11.374 rows=14,380 loops=1)

  • Recheck Cond: (dealership_id = 94)
  • Heap Blocks: exact=6,599
14. 1.954 1.954 ↓ 1.1 14,380 1

Bitmap Index Scan on "HotLeadAppointment_pkey_2" (cost=0.00..498.37 rows=13,059 width=0) (actual time=1.954..1.954 rows=14,380 loops=1)

  • Index Cond: (dealership_id = 94)
15. 7.281 130.884 ↓ 4.5 25,881 1

Hash (cost=37,074.21..37,074.21 rows=5,798 width=63) (actual time=130.884..130.884 rows=25,881 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,663kB
16. 9.872 123.603 ↓ 4.5 25,881 1

Hash Right Join (cost=36,221.57..37,074.21 rows=5,798 width=63) (actual time=104.052..123.603 rows=25,881 loops=1)

  • Hash Cond: ("PhoneCallTaskSummary".communication_id = hlcomm.communication_id)
17. 6.441 75.936 ↑ 1.8 25,848 1

Unique (cost=18,059.08..18,308.22 rows=47,802 width=43) (actual time=66.239..75.936 rows=25,848 loops=1)

18. 34.761 69.495 ↓ 1.0 50,604 1

Sort (cost=18,059.08..18,183.65 rows=49,827 width=43) (actual time=66.238..69.495 rows=50,604 loops=1)

  • Sort Key: "PhoneCallTaskSummary".communication_id, "PhoneCallTaskSummary".added_tstamp DESC
  • Sort Method: quicksort Memory: 5,490kB
19. 28.402 34.734 ↓ 1.0 50,604 1

Bitmap Heap Scan on "PhoneCallTaskSummary" (cost=1,770.58..14,171.42 rows=49,827 width=43) (actual time=7.537..34.734 rows=50,604 loops=1)

  • Recheck Cond: (dealership_id = 94)
  • Heap Blocks: exact=5,566
20. 6.332 6.332 ↓ 1.0 50,604 1

Bitmap Index Scan on "PhoneCallTaskSummary_pkey_2" (cost=0.00..1,758.13 rows=49,827 width=0) (actual time=6.332..6.332 rows=50,604 loops=1)

  • Index Cond: (dealership_id = 94)
21. 8.664 37.795 ↓ 4.5 25,881 1

Hash (cost=18,090.01..18,090.01 rows=5,798 width=44) (actual time=37.795..37.795 rows=25,881 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,225kB
22. 21.033 29.131 ↓ 4.5 25,881 1

Bitmap Heap Scan on "HotLeadCommunication" hlcomm (cost=1,904.36..18,090.01 rows=5,798 width=44) (actual time=9.559..29.131 rows=25,881 loops=1)

  • Recheck Cond: ((dealership_id = 94) AND (source = 'Xtream'::text))
  • Heap Blocks: exact=5,440
23. 8.098 8.098 ↓ 4.5 26,047 1

Bitmap Index Scan on "HotLeadCommunication_unique_source" (cost=0.00..1,902.91 rows=5,798 width=0) (actual time=8.098..8.098 rows=26,047 loops=1)

  • Index Cond: ((dealership_id = 94) AND (source = 'Xtream'::text))
24. 0.927 6.773 ↓ 1.1 4,147 1

Hash (cost=8,921.37..8,921.37 rows=3,608 width=36) (actual time=6.773..6.773 rows=4,147 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 356kB
25. 5.199 5.846 ↓ 1.1 4,147 1

Bitmap Heap Scan on "HotLeadWalkin" hlw (cost=128.39..8,921.37 rows=3,608 width=36) (actual time=1.069..5.846 rows=4,147 loops=1)

  • Recheck Cond: (dealership_id = 94)
  • Heap Blocks: exact=3,427
26. 0.647 0.647 ↓ 1.1 4,147 1

Bitmap Index Scan on "HotLeadWalkin_pkey_2" (cost=0.00..127.48 rows=3,608 width=0) (actual time=0.647..0.647 rows=4,147 loops=1)

  • Index Cond: (dealership_id = 94)
27. 0.022 0.079 ↑ 6.3 158 1

Sort (cost=59.84..62.34 rows=1,000 width=8) (actual time=0.074..0.079 rows=158 loops=1)

  • Sort Key: ((generate_series.generate_series)::date)
  • Sort Method: quicksort Memory: 32kB
28. 0.057 0.057 ↑ 6.3 158 1

Function Scan on generate_series (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.036..0.057 rows=158 loops=1)

29. 0.347 93.513 ↓ 4.0 4 1

GroupAggregate (cost=21,115.61..21,115.68 rows=1 width=91) (actual time=93.177..93.513 rows=4 loops=1)

  • Group Key: campaign.dealership_id, campaign.campaign_id
  • Filter: ((sum(CASE WHEN (("PhoneCallTaskSummary_1".communication_id IS NULL) OR (("PhoneCallTaskSummary_1".follow_up_date IS NULL) AND ("PhoneCallTaskSummary_1".outcome <> ALL ('{"Appt Set-Completed","Call Task Completed","Do Not Call","Invalid Number","No Call Made","No Longer Owns","Not Interested","Sales Match"}'::text[])))) THEN 1 ELSE 0 END) > 0) OR (sum(CASE WHEN (("PhoneCallTaskSummary_1".communication_id IS NOT NULL) AND ("PhoneCallTaskSummary_1".follow_up_date IS NOT NULL)) THEN 1 ELSE 0 END) > 0))
  • Rows Removed by Filter: 14
30. 0.244 93.166 ↓ 807.0 807 1

Sort (cost=21,115.61..21,115.61 rows=1 width=94) (actual time=93.137..93.166 rows=807 loops=1)

  • Sort Key: campaign.campaign_id
  • Sort Method: quicksort Memory: 138kB
31. 2.233 92.922 ↓ 807.0 807 1

Merge Left Join (cost=20,149.41..21,115.60 rows=1 width=94) (actual time=82.279..92.922 rows=807 loops=1)

  • Merge Cond: (hlcomm_1.communication_id = "PhoneCallTaskSummary_1".communication_id)
  • Join Filter: ("PhoneCallTaskSummary_1".dealership_id = hlcomm_1.dealership_id)
32. 0.411 37.600 ↓ 807.0 807 1

Sort (cost=2,090.33..2,090.33 rows=1 width=79) (actual time=37.516..37.600 rows=807 loops=1)

  • Sort Key: hlcomm_1.communication_id
  • Sort Method: quicksort Memory: 138kB
33. 2.351 37.189 ↓ 807.0 807 1

Nested Loop (cost=10.85..2,090.32 rows=1 width=79) (actual time=33.771..37.189 rows=807 loops=1)

  • Join Filter: ((((now())::date - '84 days'::interval) <= campaign.started_tstamp) OR ((hlcomm_1.correspondence_tstamp IS NOT NULL) AND (((now())::date - '84 days'::interval) <= hlcomm_1.correspondence_tstamp)))
  • Rows Removed by Join Filter: 8,912
34. 1.231 4.637 ↓ 197.4 10,067 1

Nested Loop (cost=10.42..1,714.49 rows=51 width=79) (actual time=0.048..4.637 rows=10,067 loops=1)

35. 0.193 0.214 ↑ 1.0 152 1

Bitmap Heap Scan on "Campaign" campaign (cost=9.99..411.26 rows=154 width=59) (actual time=0.034..0.214 rows=152 loops=1)

  • Recheck Cond: ((dealership_id = 94) AND (started_tstamp IS NOT NULL))
  • Heap Blocks: exact=149
36. 0.021 0.021 ↑ 1.0 152 1

Bitmap Index Scan on "Campaign_dealership_started" (cost=0.00..9.95 rows=154 width=0) (actual time=0.021..0.021 rows=152 loops=1)

  • Index Cond: ((dealership_id = 94) AND (started_tstamp IS NOT NULL))
37. 3.192 3.192 ↓ 66.0 66 152

Index Scan using "HotLeadCampaign_campaign_id_idx" on "HotLeadCampaign" hlcampaign (cost=0.43..8.45 rows=1 width=36) (actual time=0.006..0.021 rows=66 loops=152)

  • Index Cond: ((dealership_id = 94) AND (campaign_id = campaign.campaign_id) AND (removed_tstamp IS NULL))
38. 30.201 30.201 ↑ 1.0 1 10,067

Index Scan using "HotLeadCommunication_proposal_id_idx" on "HotLeadCommunication" hlcomm_1 (cost=0.43..7.34 rows=1 width=44) (actual time=0.002..0.003 rows=1 loops=10,067)

  • Index Cond: ((dealership_id = 94) AND (proposal_id = hlcampaign.proposal_id))
  • Filter: (source = 'Xtream'::text)
  • Rows Removed by Filter: 1
39. 1.641 53.089 ↑ 1.9 25,708 1

Materialize (cost=18,059.08..18,905.74 rows=47,802 width=39) (actual time=44.749..53.089 rows=25,708 loops=1)

40. 4.482 51.448 ↑ 1.9 25,708 1

Unique (cost=18,059.08..18,308.22 rows=47,802 width=47) (actual time=44.745..51.448 rows=25,708 loops=1)

41. 23.348 46.966 ↓ 1.0 50,333 1

Sort (cost=18,059.08..18,183.65 rows=49,827 width=47) (actual time=44.745..46.966 rows=50,333 loops=1)

  • Sort Key: "PhoneCallTaskSummary_1".communication_id, "PhoneCallTaskSummary_1".added_tstamp DESC
  • Sort Method: quicksort Memory: 5,491kB
42. 19.648 23.618 ↓ 1.0 50,604 1

Bitmap Heap Scan on "PhoneCallTaskSummary" "PhoneCallTaskSummary_1" (cost=1,770.58..14,171.42 rows=49,827 width=47) (actual time=4.540..23.618 rows=50,604 loops=1)

  • Recheck Cond: (dealership_id = 94)
  • Heap Blocks: exact=5,566
43. 3.970 3.970 ↓ 1.0 50,604 1

Bitmap Index Scan on "PhoneCallTaskSummary_pkey_2" (cost=0.00..1,758.13 rows=49,827 width=0) (actual time=3.970..3.970 rows=50,604 loops=1)

  • Index Cond: (dealership_id = 94)
Planning time : 3.485 ms
Execution time : 267.438 ms