explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e9C

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 229.587 ↑ 1.4 7 1

Limit (cost=112,954.83..112,954.85 rows=10 width=128) (actual time=229.586..229.587 rows=7 loops=1)

2. 0.012 229.585 ↑ 1,058.3 7 1

Sort (cost=112,954.83..112,973.35 rows=7,408 width=128) (actual time=229.585..229.585 rows=7 loops=1)

  • Sort Key: "*SELECT* 1".follow_up_today_tasks DESC, (CASE WHEN ((''::text) = ''::text) THEN ('Service '::text || ("Xtream"."fnPreviousMonday"((("*SELECT* 1".started_tstamp)::timestamp with time zone)))::text) ELSE (''::text) END)
  • Sort Method: quicksort Memory: 26kB
3. 0.092 229.573 ↑ 1,058.3 7 1

WindowAgg (cost=110,479.74..112,794.74 rows=7,408 width=128) (actual time=229.560..229.573 rows=7 loops=1)

4. 0.053 229.481 ↑ 1,058.3 7 1

HashAggregate (cost=110,479.74..110,553.82 rows=7,408 width=92) (actual time=229.440..229.481 rows=7 loops=1)

  • Group Key: "*SELECT* 1".dealership_id, "*SELECT* 1".campaign_id, (''::text), (("*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
5. 0.002 229.428 ↑ 1,058.3 7 1

Append (cost=107,386.68..110,331.58 rows=7,408 width=92) (actual time=187.366..229.428 rows=7 loops=1)

6. 0.003 187.526 ↑ 1,851.8 4 1

Subquery Scan on *SELECT* 1 (cost=107,386.68..108,197.79 rows=7,407 width=92) (actual time=187.365..187.526 rows=4 loops=1)

7. 0.612 187.523 ↑ 1,851.8 4 1

HashAggregate (cost=107,386.68..108,105.20 rows=7,407 width=120) (actual time=187.364..187.523 rows=4 loops=1)

  • Group Key: results.dealership_id, results.campaign_id, results.name, (generate_series.generate_series)::date
  • Filter: ((count(CASE WHEN (results.completed = 0) THEN true ELSE NULL::boolean END) > 0) AND ((sum(results.unscheduled) > 0) OR (sum(results.follow_up) > 0)))
  • Rows Removed by Filter: 17
8. 69.881 186.911 ↑ 106.2 1,669 1

Nested Loop (cost=61,714.05..102,070.02 rows=177,222 width=72) (actual time=179.290..186.911 rows=1,669 loops=1)

  • Join Filter: ((results.started_tstamp >= (generate_series.generate_series)::date) AND ((results.started_tstamp - (generate_series.generate_series)::date) < 7))
  • Rows Removed by Join Filter: 262,033
9. 0.110 0.110 ↑ 6.3 158 1

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

10. 10.294 116.920 ↓ 1.0 1,669 158

Materialize (cost=61,714.05..61,745.95 rows=1,595 width=72) (actual time=0.667..0.740 rows=1,669 loops=158)

11. 0.252 106.626 ↓ 1.0 1,669 1

Subquery Scan on results (cost=61,714.05..61,737.97 rows=1,595 width=72) (actual time=105.383..106.626 rows=1,669 loops=1)

12. 0.745 106.374 ↓ 1.0 1,669 1

Unique (cost=61,714.05..61,722.02 rows=1,595 width=96) (actual time=105.381..106.374 rows=1,669 loops=1)

13. 4.379 105.629 ↓ 2.8 4,433 1

Sort (cost=61,714.05..61,718.03 rows=1,595 width=96) (actual time=105.381..105.629 rows=4,433 loops=1)

  • Sort Key: hlcomm.communication_id, phonetask.added_tstamp DESC
  • Sort Method: quicksort Memory: 816kB
14. 3.275 101.250 ↓ 2.8 4,433 1

Nested Loop Left Join (cost=27,654.54..61,629.20 rows=1,595 width=96) (actual time=49.269..101.250 rows=4,433 loops=1)

  • Filter: ((phonetask.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())))
15. 15.873 87.961 ↓ 1.0 1,669 1

Hash Left Join (cost=27,654.11..51,440.37 rows=1,595 width=48) (actual time=49.240..87.961 rows=1,669 loops=1)

  • Hash Cond: ((hlcomm.dealership_id = hlw.dealership_id) AND (hlcomm.proposal_id = hlw.proposal_id))
  • Filter: (((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,213
16. 10.374 63.080 ↓ 4.5 25,882 1

Hash Right Join (cost=18,678.62..42,434.44 rows=5,798 width=56) (actual time=40.217..63.080 rows=25,882 loops=1)

  • Hash Cond: ((hla.dealership_id = hlcomm.dealership_id) AND (hla.proposal_id = hlcomm.proposal_id))
17. 13.917 16.218 ↓ 1.1 14,380 1

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

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

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

  • Index Cond: (dealership_id = 94)
19. 9.245 36.488 ↓ 4.5 25,882 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,225kB
20. 19.992 27.243 ↓ 4.5 25,882 1

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

  • Recheck Cond: ((dealership_id = 94) AND (source = 'Xtream'::text))
  • Heap Blocks: exact=5,440
21. 7.251 7.251 ↓ 4.5 25,882 1

Bitmap Index Scan on "HotLeadCommunication_unique_source" (cost=0.00..1,902.91 rows=5,798 width=0) (actual time=7.251..7.251 rows=25,882 loops=1)

  • Index Cond: ((dealership_id = 94) AND (source = 'Xtream'::text))
22. 1.344 9.008 ↓ 1.1 4,148 1

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

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 356kB
23. 6.885 7.664 ↓ 1.1 4,148 1

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

  • Recheck Cond: (dealership_id = 94)
  • Heap Blocks: exact=3,428
24. 0.779 0.779 ↓ 1.1 4,148 1

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

  • Index Cond: (dealership_id = 94)
25. 10.014 10.014 ↓ 3.0 3 1,669

Index Scan using "PhoneCallTaskSummary_comm_id_added_tstamp" on "PhoneCallTaskSummary" phonetask (cost=0.42..6.33 rows=1 width=47) (actual time=0.004..0.006 rows=3 loops=1,669)

  • Index Cond: ((dealership_id = hlcomm.dealership_id) AND (dealership_id = 94) AND (hlcomm.communication_id = communication_id))
26. 0.128 41.900 ↓ 3.0 3 1

GroupAggregate (cost=2,096.70..2,096.75 rows=1 width=91) (actual time=41.764..41.900 rows=3 loops=1)

  • Group Key: campaignphonetasks.dealership_id, campaignphonetasks.campaign_id, campaignphonetasks.name, campaignphonetasks.started_tstamp
  • Filter: ((sum(campaignphonetasks.unscheduled_tasks) > 0) OR (sum(campaignphonetasks.follow_up_tasks) > 0))
  • Rows Removed by Filter: 15
27. 0.195 41.772 ↓ 807.0 807 1

Sort (cost=2,096.70..2,096.71 rows=1 width=75) (actual time=41.747..41.772 rows=807 loops=1)

  • Sort Key: campaignphonetasks.dealership_id, campaignphonetasks.campaign_id, campaignphonetasks.name, campaignphonetasks.started_tstamp
  • Sort Method: quicksort Memory: 138kB
28. 0.082 41.577 ↓ 807.0 807 1

Subquery Scan on campaignphonetasks (cost=2,096.67..2,096.69 rows=1 width=75) (actual time=41.169..41.577 rows=807 loops=1)

29. 0.252 41.495 ↓ 807.0 807 1

Unique (cost=2,096.67..2,096.68 rows=1 width=99) (actual time=41.167..41.495 rows=807 loops=1)

30. 2.384 41.243 ↓ 2,178.0 2,178 1

Sort (cost=2,096.67..2,096.68 rows=1 width=99) (actual time=41.167..41.243 rows=2,178 loops=1)

  • Sort Key: campaign.campaign_id, phonetask_1.communication_id, phonetask_1.added_tstamp DESC
  • Sort Method: quicksort Memory: 403kB
31. 1.337 38.859 ↓ 2,178.0 2,178 1

Nested Loop Left Join (cost=11.28..2,096.66 rows=1 width=99) (actual time=31.728..38.859 rows=2,178 loops=1)

32. 10.464 35.101 ↓ 807.0 807 1

Nested Loop (cost=10.85..2,090.29 rows=1 width=79) (actual time=31.716..35.101 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
33. 1.233 4.503 ↓ 197.4 10,067 1

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

34. 0.211 0.230 ↑ 1.0 152 1

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

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

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

  • Index Cond: ((dealership_id = 94) AND (started_tstamp IS NOT NULL))
36. 3.040 3.040 ↓ 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.020 rows=66 loops=152)

  • Index Cond: ((dealership_id = 94) AND (campaign_id = campaign.campaign_id) AND (removed_tstamp IS NULL))
37. 20.134 20.134 ↑ 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.002 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
38. 2.421 2.421 ↓ 3.0 3 807

Index Scan using "PhoneCallTaskSummary_comm_id_added_tstamp" on "PhoneCallTaskSummary" phonetask_1 (cost=0.42..6.33 rows=1 width=47) (actual time=0.002..0.003 rows=3 loops=807)

  • Index Cond: ((dealership_id = hlcomm_1.dealership_id) AND (dealership_id = 94) AND (hlcomm_1.communication_id = communication_id))
Planning time : 3.161 ms
Execution time : 230.417 ms