explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KyL5

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 27,063.303 ↑ 1,489.2 29 1

Sort (cost=3,806,709.87..3,806,817.83 rows=43,186 width=103) (actual time=27,063.301..27,063.303 rows=29 loops=1)

  • Sort Key: ccs.id, sadef_1.id
  • Sort Method: quicksort Memory: 27kB
2. 0.042 27,063.283 ↑ 1,489.2 29 1

Nested Loop (cost=2,500,394.48..3,803,384.92 rows=43,186 width=103) (actual time=27,025.671..27,063.283 rows=29 loops=1)

3. 0.021 27,063.096 ↑ 1,489.2 29 1

Hash Join (cost=2,500,394.48..3,799,887.29 rows=43,186 width=186) (actual time=27,025.652..27,063.096 rows=29 loops=1)

  • Hash Cond: (ccs.crmstatusid = salink_5.crmstatusid)
4. 0.035 27,062.943 ↑ 1,489.2 29 1

Hash Join (cost=2,500,381.04..3,799,753.98 rows=43,186 width=202) (actual time=27,025.512..27,062.943 rows=29 loops=1)

  • Hash Cond: ((ccs.crmstatusid = salink.crmstatusid) AND (sadef.id = salink.crmstatusactiondefid))
5. 0.084 27,062.862 ↑ 9,683.0 87 1

Nested Loop (cost=2,500,372.84..3,795,320.95 rows=842,420 width=202) (actual time=27,025.450..27,062.862 rows=87 loops=1)

  • Join Filter: ((c_1.id = ccs.customerid) AND ((CURRENT_TIMESTAMP - (ccs.assignmentdate)::timestamp with time zone) >= (((sadef.period * 1440))::double precision * '00:01:00'::interval)))
  • Rows Removed by Join Filter: 10
6. 0.027 27,062.547 ↑ 15,858.1 21 1

Hash Join (cost=2,500,372.40..3,363,822.20 rows=333,020 width=136) (actual time=27,025.426..27,062.547 rows=21 loops=1)

  • Hash Cond: (sadef_1.id = sadef.id)
7. 36.911 27,062.479 ↑ 36,879.2 21 1

Merge Join (cost=2,500,366.89..3,361,740.65 rows=774,464 width=24) (actual time=27,025.377..27,062.479 rows=21 loops=1)

  • Merge Cond: (ccs_1.customerid = c_1.id)
8. 1,321.139 26,449.117 ↑ 92,971.2 21 1

Finalize GroupAggregate (cost=2,500,366.46..3,240,689.40 rows=1,952,395 width=24) (actual time=26,418.539..26,449.117 rows=21 loops=1)

  • Group Key: ccs_1.customerid, sadef_1.id, sacount.count
  • Filter: (count(*) >= sacount.count)
  • Rows Removed by Filter: 8553088
9. 2,415.278 25,127.978 ↓ 2.2 10,697,999 1

Gather Merge (cost=2,500,366.46..3,118,664.70 rows=4,880,988 width=32) (actual time=20,581.823..25,127.978 rows=10,697,999 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 1,163.282 22,712.700 ↓ 1.5 3,566,000 3 / 3

Partial GroupAggregate (cost=2,499,366.43..2,554,277.55 rows=2,440,494 width=32) (actual time=20,570.485..22,712.700 rows=3,566,000 loops=3)

  • Group Key: ccs_1.customerid, sadef_1.id, sacount.count
11. 3,026.901 21,549.418 ↓ 1.6 3,969,603 3 / 3

Sort (cost=2,499,366.43..2,505,467.67 rows=2,440,494 width=24) (actual time=20,570.472..21,549.418 rows=3,969,603 loops=3)

  • Sort Key: ccs_1.customerid, sadef_1.id, sacount.count
  • Sort Method: external merge Disk: 130184kB
  • Worker 0: Sort Method: external merge Disk: 134392kB
  • Worker 1: Sort Method: external merge Disk: 132200kB
12. 3,168.671 18,522.517 ↓ 1.6 3,969,603 3 / 3

Parallel Hash Anti Join (cost=1,473,233.96..2,190,395.37 rows=2,440,494 width=24) (actual time=16,401.154..18,522.517 rows=3,969,603 loops=3)

  • Hash Cond: ((ccs_1.id = csalink.crmcustomerstatusid) AND (sadef_1.id = csa.crmstatusactiondefid))
13. 2,021.150 5,226.296 ↓ 1.6 5,037,333 3 / 3

Hash Join (cost=70,437.42..506,416.32 rows=3,201,605 width=32) (actual time=333.949..5,226.296 rows=5,037,333 loops=3)

  • Hash Cond: (salink_1.crmstatusactiondefid = sadef_1.id)
  • Join Filter: ((CURRENT_TIMESTAMP - (ccs_1.assignmentdate)::timestamp with time zone) >= (((sadef_1.period * 1440))::double precision * '00:01:00'::interval))
  • Rows Removed by Join Filter: 20706
14. 672.154 3,205.101 ↑ 3.2 5,058,039 3 / 3

Hash Join (cost=70,431.90..463,128.65 rows=16,082,480 width=48) (actual time=333.882..3,205.101 rows=5,058,039 loops=3)

  • Hash Cond: (ccs_1.crmstatusid = salink_2.crmstatusid)
15. 1,193.819 2,532.617 ↑ 1.2 1,870,593 3 / 3

Parallel Hash Join (cost=70,393.53..376,461.96 rows=2,178,588 width=32) (actual time=333.544..2,532.617 rows=1,870,593 loops=3)

  • Hash Cond: (ccs_1.customerid = c_2.id)
16. 1,005.913 1,005.913 ↑ 1.2 4,409,864 3 / 3

Parallel Seq Scan on crm_customer_status ccs_1 (cost=0.00..291,651.53 rows=5,492,142 width=32) (actual time=0.008..1,005.913 rows=4,409,864 loops=3)

  • Filter: (unassignmentdate IS NULL)
  • Rows Removed by Filter: 3058086
17. 68.001 332.885 ↑ 1.1 293,826 3 / 3

Parallel Hash (cost=66,207.20..66,207.20 rows=334,906 width=8) (actual time=332.885..332.885 rows=293,826 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 42752kB
18. 264.884 264.884 ↑ 1.1 293,826 3 / 3

Parallel Index Only Scan using crm_customer_closingdate3_idx on crm_customer c_2 (cost=0.43..66,207.20 rows=334,906 width=8) (actual time=0.021..264.884 rows=293,826 loops=3)

  • Filter: ((closingdate IS NULL) OR (closingdate > (CURRENT_TIMESTAMP - '6 mons'::interval)))
  • Rows Removed by Filter: 381613
  • Heap Fetches: 102948
19. 0.023 0.330 ↑ 1.7 149 3 / 3

Hash (cost=35.27..35.27 rows=248 width=40) (actual time=0.330..0.330 rows=149 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
20. 0.024 0.307 ↑ 1.7 149 3 / 3

Hash Join (cost=29.43..35.27 rows=248 width=40) (actual time=0.240..0.307 rows=149 loops=3)

  • Hash Cond: (salink_1.crmstatusactiondefid = sacount.crmstatusactiondefid)
21. 0.039 0.159 ↑ 1.6 153 3 / 3

Hash Join (cost=13.44..18.61 rows=248 width=24) (actual time=0.111..0.159 rows=153 loops=3)

  • Hash Cond: (salink_1.crmstatusid = salink_2.crmstatusid)
22. 0.017 0.017 ↑ 1.0 248 3 / 3

Seq Scan on crm_status_action_link_def salink_1 (cost=0.00..4.48 rows=248 width=16) (actual time=0.004..0.017 rows=248 loops=3)

23. 0.004 0.103 ↑ 2.2 34 3 / 3

Hash (cost=12.52..12.52 rows=74 width=8) (actual time=0.103..0.103 rows=34 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.025 0.099 ↑ 2.2 34 3 / 3

HashAggregate (cost=11.04..11.78 rows=74 width=8) (actual time=0.096..0.099 rows=34 loops=3)

  • Group Key: salink_2.crmstatusid
25. 0.033 0.074 ↓ 1.0 149 3 / 3

Hash Join (cost=5.51..10.67 rows=148 width=8) (actual time=0.034..0.074 rows=149 loops=3)

  • Hash Cond: (salink_2.crmstatusactiondefid = sadef_2.id)
26. 0.014 0.014 ↑ 1.0 248 3 / 3

Seq Scan on crm_status_action_link_def salink_2 (cost=0.00..4.48 rows=248 width=16) (actual time=0.002..0.014 rows=248 loops=3)

27. 0.008 0.027 ↑ 1.0 86 3 / 3

Hash (cost=4.44..4.44 rows=86 width=8) (actual time=0.027..0.027 rows=86 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.019 0.019 ↑ 1.0 86 3 / 3

Seq Scan on crm_status_action_def sadef_2 (cost=0.00..4.44 rows=86 width=8) (actual time=0.002..0.019 rows=86 loops=3)

  • Filter: isactive
  • Rows Removed by Filter: 58
29. 0.008 0.124 ↑ 1.7 84 3 / 3

Hash (cost=14.23..14.23 rows=141 width=16) (actual time=0.124..0.124 rows=84 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
30. 0.007 0.116 ↑ 1.7 84 3 / 3

Subquery Scan on sacount (cost=11.41..14.23 rows=141 width=16) (actual time=0.102..0.116 rows=84 loops=3)

31. 0.040 0.109 ↑ 1.7 84 3 / 3

HashAggregate (cost=11.41..12.82 rows=141 width=16) (actual time=0.101..0.109 rows=84 loops=3)

  • Group Key: salink_3.crmstatusactiondefid
32. 0.028 0.069 ↓ 1.0 149 3 / 3

Hash Join (cost=5.51..10.67 rows=148 width=8) (actual time=0.033..0.069 rows=149 loops=3)

  • Hash Cond: (salink_3.crmstatusactiondefid = sadef_3.id)
33. 0.014 0.014 ↑ 1.0 248 3 / 3

Seq Scan on crm_status_action_link_def salink_3 (cost=0.00..4.48 rows=248 width=8) (actual time=0.002..0.014 rows=248 loops=3)

34. 0.009 0.027 ↑ 1.0 86 3 / 3

Hash (cost=4.44..4.44 rows=86 width=8) (actual time=0.026..0.027 rows=86 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
35. 0.018 0.018 ↑ 1.0 86 3 / 3

Seq Scan on crm_status_action_def sadef_3 (cost=0.00..4.44 rows=86 width=8) (actual time=0.002..0.018 rows=86 loops=3)

  • Filter: isactive
  • Rows Removed by Filter: 58
36. 0.010 0.045 ↑ 1.0 86 3 / 3

Hash (cost=4.44..4.44 rows=86 width=12) (actual time=0.045..0.045 rows=86 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
37. 0.035 0.035 ↑ 1.0 86 3 / 3

Seq Scan on crm_status_action_def sadef_1 (cost=0.00..4.44 rows=86 width=12) (actual time=0.011..0.035 rows=86 loops=3)

  • Filter: isactive
  • Rows Removed by Filter: 58
38. 170.667 10,127.550 ↑ 2.3 1,072,679 3 / 3

Parallel Hash (cost=1,354,673.67..1,354,673.67 rows=2,420,325 width=16) (actual time=10,127.550..10,127.550 rows=1,072,679 loops=3)

  • Buckets: 524288 Batches: 32 Memory Usage: 8864kB
39. 1,478.844 9,956.883 ↑ 2.3 1,072,679 3 / 3

Parallel Hash Join (cost=865,053.79..1,354,673.67 rows=2,420,325 width=16) (actual time=8,674.856..9,956.883 rows=1,072,679 loops=3)

  • Hash Cond: (csalink.crmcustomerstatusactionsid = csa.id)
40. 2,554.921 6,082.645 ↑ 2.3 1,074,777 3 / 3

Parallel Hash Join (cost=418,265.07..834,262.84 rows=2,457,183 width=16) (actual time=4,588.907..6,082.645 rows=1,074,777 loops=3)

  • Hash Cond: (csalink.crmcustomerstatusid = ccs_2.id)
41. 787.425 787.425 ↑ 1.2 8,421,146 3 / 3

Parallel Seq Scan on crm_customer_status_actions_link csalink (cost=0.00..241,824.01 rows=10,526,101 width=16) (actual time=0.011..787.425 rows=8,421,146 loops=3)

42. 151.436 2,740.299 ↑ 2.0 1,102,247 3 / 3

Parallel Hash (cost=382,521.72..382,521.72 rows=2,178,588 width=8) (actual time=2,740.299..2,740.299 rows=1,102,247 loops=3)

  • Buckets: 524288 Batches: 32 Memory Usage: 8160kB
43. 175.632 2,588.863 ↑ 2.0 1,102,247 3 / 3

Hash Join (cost=70,406.97..382,521.72 rows=2,178,588 width=8) (actual time=346.970..2,588.863 rows=1,102,247 loops=3)

  • Hash Cond: (ccs_2.crmstatusid = salink_4.crmstatusid)
44. 1,100.324 2,413.080 ↑ 1.2 1,870,593 3 / 3

Parallel Hash Join (cost=70,393.53..376,461.96 rows=2,178,588 width=16) (actual time=346.808..2,413.080 rows=1,870,593 loops=3)

  • Hash Cond: (ccs_2.customerid = c_3.id)
45. 967.465 967.465 ↑ 1.2 4,409,864 3 / 3

Parallel Seq Scan on crm_customer_status ccs_2 (cost=0.00..291,651.53 rows=5,492,142 width=24) (actual time=0.009..967.465 rows=4,409,864 loops=3)

  • Filter: (unassignmentdate IS NULL)
  • Rows Removed by Filter: 3058086
46. 75.651 345.291 ↑ 1.1 293,826 3 / 3

Parallel Hash (cost=66,207.20..66,207.20 rows=334,906 width=8) (actual time=345.290..345.291 rows=293,826 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 42752kB
47. 269.640 269.640 ↑ 1.1 293,826 3 / 3

Parallel Index Only Scan using crm_customer_closingdate3_idx on crm_customer c_3 (cost=0.43..66,207.20 rows=334,906 width=8) (actual time=0.053..269.640 rows=293,826 loops=3)

  • Filter: ((closingdate IS NULL) OR (closingdate > (CURRENT_TIMESTAMP - '6 mons'::interval)))
  • Rows Removed by Filter: 381613
  • Heap Fetches: 102918
48. 0.004 0.151 ↑ 2.2 34 3 / 3

Hash (cost=12.52..12.52 rows=74 width=8) (actual time=0.151..0.151 rows=34 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
49. 0.025 0.147 ↑ 2.2 34 3 / 3

HashAggregate (cost=11.04..11.78 rows=74 width=8) (actual time=0.143..0.147 rows=34 loops=3)

  • Group Key: salink_4.crmstatusid
50. 0.038 0.122 ↓ 1.0 149 3 / 3

Hash Join (cost=5.51..10.67 rows=148 width=8) (actual time=0.078..0.122 rows=149 loops=3)

  • Hash Cond: (salink_4.crmstatusactiondefid = sadef_4.id)
51. 0.029 0.029 ↑ 1.0 248 3 / 3

Seq Scan on crm_status_action_link_def salink_4 (cost=0.00..4.48 rows=248 width=16) (actual time=0.017..0.029 rows=248 loops=3)

52. 0.009 0.055 ↑ 1.0 86 3 / 3

Hash (cost=4.44..4.44 rows=86 width=8) (actual time=0.055..0.055 rows=86 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
53. 0.046 0.046 ↑ 1.0 86 3 / 3

Seq Scan on crm_status_action_def sadef_4 (cost=0.00..4.44 rows=86 width=8) (actual time=0.010..0.046 rows=86 loops=3)

  • Filter: isactive
  • Rows Removed by Filter: 58
54. 995.456 2,395.394 ↑ 1.3 7,073,456 3 / 3

Parallel Hash (cost=293,082.33..293,082.33 rows=8,842,432 width=16) (actual time=2,395.394..2,395.394 rows=7,073,456 loops=3)

  • Buckets: 524288 Batches: 128 Memory Usage: 11936kB
55. 1,399.938 1,399.938 ↑ 1.3 7,073,456 3 / 3

Parallel Seq Scan on crm_customer_status_actions csa (cost=0.00..293,082.33 rows=8,842,432 width=16) (actual time=0.015..1,399.938 rows=7,073,456 loops=3)

  • Filter: (executionstatus = ANY ('{0,1,5,6}'::integer[]))
  • Rows Removed by Filter: 108341
56. 576.451 576.451 ↓ 1.1 881,477 1

Index Only Scan using crm_customer_closingdate3_idx on crm_customer c_1 (cost=0.43..86,892.23 rows=803,775 width=8) (actual time=0.027..576.451 rows=881,477 loops=1)

  • Filter: ((closingdate IS NULL) OR (closingdate > (CURRENT_TIMESTAMP - '6 mons'::interval)))
  • Rows Removed by Filter: 1144839
  • Heap Fetches: 103007
57. 0.017 0.041 ↑ 1.0 86 1

Hash (cost=4.44..4.44 rows=86 width=112) (actual time=0.040..0.041 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
58. 0.024 0.024 ↑ 1.0 86 1

Seq Scan on crm_status_action_def sadef (cost=0.00..4.44 rows=86 width=112) (actual time=0.002..0.024 rows=86 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 58
59. 0.231 0.231 ↑ 1.6 5 21

Index Scan using crm_customer_status_unassignmentdate3_idx on crm_customer_status ccs (cost=0.43..1.06 rows=8 width=78) (actual time=0.007..0.011 rows=5 loops=21)

  • Index Cond: (customerid = ccs_1.customerid)
60. 0.026 0.046 ↑ 1.0 248 1

Hash (cost=4.48..4.48 rows=248 width=16) (actual time=0.046..0.046 rows=248 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
61. 0.020 0.020 ↑ 1.0 248 1

Seq Scan on crm_status_action_link_def salink (cost=0.00..4.48 rows=248 width=16) (actual time=0.002..0.020 rows=248 loops=1)

62. 0.003 0.132 ↑ 2.2 34 1

Hash (cost=12.52..12.52 rows=74 width=8) (actual time=0.132..0.132 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
63. 0.022 0.129 ↑ 2.2 34 1

HashAggregate (cost=11.04..11.78 rows=74 width=8) (actual time=0.125..0.129 rows=34 loops=1)

  • Group Key: salink_5.crmstatusid
64. 0.045 0.107 ↓ 1.0 149 1

Hash Join (cost=5.51..10.67 rows=148 width=8) (actual time=0.054..0.107 rows=149 loops=1)

  • Hash Cond: (salink_5.crmstatusactiondefid = sadef_5.id)
65. 0.019 0.019 ↑ 1.0 248 1

Seq Scan on crm_status_action_link_def salink_5 (cost=0.00..4.48 rows=248 width=16) (actual time=0.007..0.019 rows=248 loops=1)

66. 0.009 0.043 ↑ 1.0 86 1

Hash (cost=4.44..4.44 rows=86 width=8) (actual time=0.042..0.043 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
67. 0.034 0.034 ↑ 1.0 86 1

Seq Scan on crm_status_action_def sadef_5 (cost=0.00..4.44 rows=86 width=8) (actual time=0.005..0.034 rows=86 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 58
68. 0.145 0.145 ↑ 1.0 1 29

Index Scan using crm_customer_id_idx_h on crm_customer c (cost=0.00..0.04 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=29)

  • Index Cond: (id = ccs.customerid)