explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fpNL

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 6,515.826 ↑ 1.4 86 1

Sort (cost=318,725.98..318,726.28 rows=118 width=132) (actual time=6,515.822..6,515.826 rows=86 loops=1)

  • Sort Key: ((producer_policies.premium_written)::numeric(14,2)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 37kB
2.          

CTE annualpremiums

3. 122.122 604.548 ↓ 2.1 742,875 1

Unique (cost=91,642.28..97,276.44 rows=357,248 width=40) (actual time=423.067..604.548 rows=742,875 loops=1)

4. 396.185 482.426 ↓ 1.0 751,226 1

Sort (cost=91,642.28..93,520.33 rows=751,222 width=40) (actual time=423.066..482.426 rows=751,226 loops=1)

  • Sort Key: tblriskpremium.idpolicyversion, tblriskpremium.riskname, tblriskpremium.validfrom
  • Sort Method: quicksort Memory: 89099kB
5. 86.241 86.241 ↓ 1.0 751,226 1

Seq Scan on tblriskpremium (cost=0.00..18,327.22 rows=751,222 width=40) (actual time=0.010..86.241 rows=751,226 loops=1)

6.          

CTE aggregatedpremiums

7. 451.625 517.146 ↓ 1.3 463,762 1

HashAggregate (cost=22,083.33..26,548.93 rows=357,248 width=40) (actual time=325.838..517.146 rows=463,762 loops=1)

  • Group Key: tblriskpremium_1.idpolicyversion
8. 65.521 65.521 ↓ 1.0 751,226 1

Seq Scan on tblriskpremium tblriskpremium_1 (cost=0.00..18,327.22 rows=751,222 width=13) (actual time=0.012..65.521 rows=751,226 loops=1)

9.          

CTE aggregatedannualpremiums

10. 415.409 1,119.892 ↓ 2,318.8 463,762 1

HashAggregate (cost=8,931.20..8,933.70 rows=200 width=40) (actual time=892.084..1,119.892 rows=463,762 loops=1)

  • Group Key: annualpremiums.idpolicyversion
11. 704.483 704.483 ↓ 2.1 742,875 1

CTE Scan on annualpremiums (cost=0.00..7,144.96 rows=357,248 width=26) (actual time=423.071..704.483 rows=742,875 loops=1)

12.          

CTE current_prod_policies

13. 51.900 4,573.392 ↑ 3.5 10,133 1

Hash Join (cost=2,040.74..10,703.41 rows=35,577 width=64) (actual time=4,246.054..4,573.392 rows=10,133 loops=1)

  • Hash Cond: (ap.idpolicyversion = pv.id)
14. 601.307 601.307 ↓ 1.3 463,762 1

CTE Scan on aggregatedpremiums ap (cost=0.00..7,144.96 rows=357,248 width=40) (actual time=325.840..601.307 rows=463,762 loops=1)

15. 4.540 3,920.185 ↓ 506.6 10,133 1

Hash (cost=2,040.49..2,040.49 rows=20 width=87) (actual time=3,920.185..3,920.185 rows=10,133 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1158kB
16. 0.000 3,915.645 ↓ 506.6 10,133 1

Nested Loop Left Join (cost=1.69..2,040.49 rows=20 width=87) (actual time=892.264..3,915.645 rows=10,133 loops=1)

  • Filter: ((COALESCE(p_1.createdtime, p.createdtime) >= '2019-08-04 00:00:00+00'::timestamp with time zone) AND (COALESCE(p_1.createdtime, p.createdtime) <= '2019-09-04 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 405007
17. 327.563 3,534.654 ↓ 2,319.2 415,140 1

Nested Loop (cost=0.84..1,782.96 rows=179 width=87) (actual time=892.118..3,534.654 rows=415,140 loops=1)

18. 188.229 2,323.987 ↓ 2,311.8 441,552 1

Nested Loop (cost=0.42..1,632.50 rows=191 width=56) (actual time=892.108..2,323.987 rows=441,552 loops=1)

19. 1,208.234 1,208.234 ↓ 2,318.8 463,762 1

CTE Scan on aggregatedannualpremiums aap (cost=0.00..4.00 rows=200 width=40) (actual time=892.085..1,208.234 rows=463,762 loops=1)

20. 927.524 927.524 ↑ 1.0 1 463,762

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..8.14 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=463,762)

  • Index Cond: (id = aap.idpolicyversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
21. 883.104 883.104 ↑ 1.0 1 441,552

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.79 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=441,552)

  • Index Cond: (id = pv.idpolicy)
  • Filter: ((test IS FALSE) AND (canceldate IS NULL))
  • Rows Removed by Filter: 0
22. 314.706 415.140 ↓ 0.0 0 415,140

Nested Loop (cost=0.84..1.42 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=415,140)

23. 0.000 0.000 ↓ 0.0 0 415,140

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_1 (cost=0.42..0.64 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=415,140)

  • Index Cond: (p.idofferversion = id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
24. 100.434 100.434 ↑ 1.0 1 50,217

Index Scan using pk_tblinsurance on tblpolicy p_1 (cost=0.42..0.79 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=50,217)

  • Index Cond: (id = pv_1.idpolicy)
25.          

CTE producer_quotes

26. 1.222 4.893 ↓ 1.5 172 1

GroupAggregate (cost=807.14..810.39 rows=118 width=48) (actual time=3.390..4.893 rows=172 loops=1)

  • Group Key: q.idproduceremployee
27. 1.534 3.671 ↓ 53.0 9,430 1

Sort (cost=807.14..807.58 rows=178 width=26) (actual time=3.297..3.671 rows=9,430 loops=1)

  • Sort Key: q.idproduceremployee
  • Sort Method: quicksort Memory: 827kB
28. 2.137 2.137 ↓ 53.0 9,430 1

CTE Scan on current_prod_policies q (cost=0.00..800.48 rows=178 width=26) (actual time=0.001..2.137 rows=9,430 loops=1)

  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 703
29.          

CTE producer_policies

30. 0.115 4,581.506 ↑ 1.4 86 1

GroupAggregate (cost=2,344.72..2,347.89 rows=118 width=48) (actual time=4,581.372..4,581.506 rows=86 loops=1)

  • Group Key: p_2.idproduceremployee
31. 0.220 4,581.391 ↓ 4.1 703 1

Sort (cost=2,344.72..2,345.14 rows=170 width=26) (actual time=4,581.354..4,581.391 rows=703 loops=1)

  • Sort Key: p_2.idproduceremployee
  • Sort Method: quicksort Memory: 57kB
32. 0.343 4,581.171 ↓ 4.1 703 1

Nested Loop (cost=0.84..2,338.42 rows=170 width=26) (actual time=4,246.242..4,581.171 rows=703 loops=1)

33. 0.654 4,579.422 ↓ 4.1 703 1

Nested Loop (cost=0.42..2,259.25 rows=170 width=34) (actual time=4,246.210..4,579.422 rows=703 loops=1)

34. 4,576.659 4,576.659 ↓ 3.9 703 1

CTE Scan on current_prod_policies p_2 (cost=0.00..800.48 rows=178 width=34) (actual time=4,246.202..4,576.659 rows=703 loops=1)

  • Filter: (policytype = 'POLICY'::text)
  • Rows Removed by Filter: 9430
35. 2.109 2.109 ↑ 1.0 1 703

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_2 (cost=0.42..8.20 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=703)

  • Index Cond: (id = p_2.idofferversion)
  • Filter: (policyversionstatus = 'CURRENT'::text)
36. 1.406 1.406 ↑ 1.0 1 703

Index Only Scan using pk_tblinsurance on tblpolicy p_3 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=703)

  • Index Cond: (id = pv_2.idpolicy)
  • Heap Fetches: 0
37.          

CTE clients

38. 4.724 1,840.621 ↑ 18.2 4,405 1

HashAggregate (cost=160,635.19..161,435.85 rows=80,066 width=48) (actual time=1,839.768..1,840.621 rows=4,405 loops=1)

  • Group Key: p_4.idproduceremployee, p_4.policytype, mc.id
39. 5.705 1,835.897 ↑ 3.8 20,938 1

Hash Join (cost=158,344.61..160,034.69 rows=80,066 width=48) (actual time=1,828.907..1,835.897 rows=20,938 loops=1)

  • Hash Cond: (p_4.id = pv_3.idpolicy)
40. 2.010 2.010 ↑ 3.5 10,133 1

CTE Scan on current_prod_policies p_4 (cost=0.00..711.54 rows=35,577 width=48) (actual time=0.001..2.010 rows=10,133 loops=1)

41. 137.773 1,828.182 ↓ 1.0 927,524 1

Hash (cost=146,750.61..146,750.61 rows=927,520 width=24) (actual time=1,828.182..1,828.182 rows=927,524 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 58916kB
42. 202.029 1,690.409 ↓ 1.0 927,524 1

Hash Join (cost=113,891.62..146,750.61 rows=927,520 width=24) (actual time=1,173.985..1,690.409 rows=927,524 loops=1)

  • Hash Cond: (pv_3.idpolicy = p_5.id)
43. 247.055 1,382.208 ↓ 1.0 927,524 1

Hash Join (cost=90,356.88..120,781.10 rows=927,520 width=16) (actual time=1,067.432..1,382.208 rows=927,524 loops=1)

  • Hash Cond: (pcr.idpolicyclient = pc.id)
44. 68.118 68.118 ↓ 1.0 927,524 1

Seq Scan on tblpolicyclientrole pcr (cost=0.00..17,944.20 rows=927,520 width=8) (actual time=0.011..68.118 rows=927,524 loops=1)

45. 84.467 1,067.035 ↓ 1.0 463,762 1

Hash (cost=84,559.88..84,559.88 rows=463,760 width=24) (actual time=1,067.035..1,067.035 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 29458kB
46. 147.403 982.568 ↓ 1.0 463,762 1

Hash Join (cost=67,425.71..84,559.88 rows=463,760 width=24) (actual time=417.085..982.568 rows=463,762 loops=1)

  • Hash Cond: (pc.idpolicyversion = pv_3.id)
47. 96.606 708.390 ↓ 1.0 463,762 1

Hash Join (cost=38,226.11..54,142.90 rows=463,760 width=24) (actual time=289.918..708.390 rows=463,762 loops=1)

  • Hash Cond: (c.idmetaclient = mc.id)
48. 97.427 562.861 ↓ 1.0 463,762 1

Hash Join (cost=32,189.97..46,889.36 rows=463,760 width=24) (actual time=240.806..562.861 rows=463,762 loops=1)

  • Hash Cond: (pc.idclient = c.id)
49. 179.635 412.678 ↓ 1.0 463,762 1

Hash Right Join (cost=24,531.60..38,013.58 rows=463,760 width=24) (actual time=187.851..412.678 rows=463,762 loops=1)

  • Hash Cond: (pa.idpolicyclient = pc.id)
50. 45.589 45.589 ↓ 1.0 463,762 1

Seq Scan on tblpolicyaddress pa (cost=0.00..12,264.60 rows=463,760 width=16) (actual time=0.008..45.589 rows=463,762 loops=1)

51. 81.256 187.454 ↓ 1.0 463,762 1

Hash (cost=18,734.60..18,734.60 rows=463,760 width=24) (actual time=187.454..187.454 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 29458kB
52. 106.198 106.198 ↓ 1.0 463,762 1

Seq Scan on tblpolicyclient pc (cost=0.00..18,734.60 rows=463,760 width=24) (actual time=0.010..106.198 rows=463,762 loops=1)

53. 19.544 52.756 ↓ 1.0 140,462 1

Hash (cost=5,902.61..5,902.61 rows=140,461 width=16) (actual time=52.756..52.756 rows=140,462 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8633kB
54. 33.212 33.212 ↓ 1.0 140,462 1

Seq Scan on tblclient c (cost=0.00..5,902.61 rows=140,461 width=16) (actual time=0.010..33.212 rows=140,462 loops=1)

55. 22.641 48.923 ↓ 1.0 135,696 1

Hash (cost=4,339.95..4,339.95 rows=135,695 width=8) (actual time=48.923..48.923 rows=135,696 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7349kB
56. 26.282 26.282 ↓ 1.0 135,696 1

Seq Scan on tblmetaclient mc (cost=0.00..4,339.95 rows=135,695 width=8) (actual time=0.009..26.282 rows=135,696 loops=1)

57. 60.271 126.775 ↓ 1.0 463,762 1

Hash (cost=23,402.60..23,402.60 rows=463,760 width=16) (actual time=126.775..126.775 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 25835kB
58. 66.504 66.504 ↓ 1.0 463,762 1

Seq Scan on tblpolicyversion pv_3 (cost=0.00..23,402.60 rows=463,760 width=16) (actual time=0.008..66.504 rows=463,762 loops=1)

59. 55.341 106.172 ↓ 1.0 441,542 1

Hash (cost=18,015.51..18,015.51 rows=441,539 width=8) (actual time=106.172..106.172 rows=441,542 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 21344kB
60. 50.831 50.831 ↓ 1.0 441,542 1

Index Only Scan using pk_tblinsurance on tblpolicy p_5 (cost=0.42..18,015.51 rows=441,539 width=8) (actual time=0.012..50.831 rows=441,542 loops=1)

  • Heap Fetches: 0
61.          

CTE quotes_clients

62. 0.442 0.850 ↑ 1.0 172 1

HashAggregate (cost=1,803.48..1,805.21 rows=173 width=16) (actual time=0.832..0.850 rows=172 loops=1)

  • Group Key: clients.idproducer
63. 0.408 0.408 ↓ 9.3 3,724 1

CTE Scan on clients (cost=0.00..1,801.48 rows=400 width=8) (actual time=0.001..0.408 rows=3,724 loops=1)

  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 681
64.          

CTE policies_clients

65. 0.101 1,841.562 ↑ 2.0 86 1

HashAggregate (cost=1,803.48..1,805.21 rows=173 width=16) (actual time=1,841.552..1,841.562 rows=86 loops=1)

  • Group Key: clients_1.idproducer
66. 1,841.461 1,841.461 ↓ 1.7 681 1

CTE Scan on clients clients_1 (cost=0.00..1,801.48 rows=400 width=8) (actual time=1,839.775..1,841.461 rows=681 loops=1)

  • Filter: (policytype = 'POLICY'::text)
  • Rows Removed by Filter: 3724
67. 0.922 6,515.751 ↑ 1.4 86 1

Nested Loop Left Join (cost=4,719.76..7,054.88 rows=118 width=132) (actual time=6,495.208..6,515.751 rows=86 loops=1)

  • Join Filter: (((producer_quotes.idproducer IS NULL) AND (e.id IS NULL)) OR (producer_quotes.idproducer = e.id))
  • Rows Removed by Join Filter: 14707
68. 0.873 6,509.067 ↑ 1.4 86 1

Nested Loop Left Join (cost=4,719.76..6,739.52 rows=118 width=91) (actual time=6,490.555..6,509.067 rows=86 loops=1)

  • Join Filter: (((quotes_clients.idproducer IS NULL) AND (e.id IS NULL)) OR (quotes_clients.idproducer = e.id))
  • Rows Removed by Join Filter: 14707
69. 0.406 6,506.474 ↑ 1.4 86 1

Nested Loop Left Join (cost=4,719.76..6,278.48 rows=118 width=83) (actual time=6,489.682..6,506.474 rows=86 loops=1)

  • Join Filter: (((policies_clients.idproducer IS NULL) AND (e.id IS NULL)) OR (policies_clients.idproducer = e.id))
  • Rows Removed by Join Filter: 7310
70. 0.038 4,664.034 ↑ 1.4 86 1

Hash Left Join (cost=4,719.76..5,817.43 rows=118 width=75) (actual time=4,648.105..4,664.034 rows=86 loops=1)

  • Hash Cond: (e.idoperatorunit = ou.id)
71. 2.511 4,663.972 ↑ 1.4 86 1

Hash Right Join (cost=4,716.25..5,813.61 rows=118 width=69) (actual time=4,648.071..4,663.972 rows=86 loops=1)

  • Hash Cond: (tbluser.id = e.iduser)
72. 28.458 79.754 ↑ 1.0 46,155 1

HashAggregate (cost=3,912.13..4,373.68 rows=46,155 width=194) (actual time=66.184..79.754 rows=46,155 loops=1)

  • Group Key: tbluser.id
73. 7.458 51.296 ↑ 1.0 50,153 1

Hash Join (cost=2,635.50..3,786.75 rows=50,153 width=130) (actual time=22.041..51.296 rows=50,153 loops=1)

  • Hash Cond: (tblusergroup.idgroup = tblgroup.id)
74. 17.911 43.822 ↑ 1.0 50,153 1

Hash Join (cost=2,633.49..3,640.68 rows=50,153 width=138) (actual time=22.014..43.822 rows=50,153 loops=1)

  • Hash Cond: (tblusergroup.iduser = tbluser.id)
75. 3.957 3.957 ↑ 1.0 50,153 1

Seq Scan on tblusergroup (cost=0.00..875.53 rows=50,153 width=16) (actual time=0.004..3.957 rows=50,153 loops=1)

76. 11.485 21.954 ↑ 1.0 46,155 1

Hash (cost=2,056.55..2,056.55 rows=46,155 width=130) (actual time=21.954..21.954 rows=46,155 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 7935kB
77. 10.469 10.469 ↑ 1.0 46,155 1

Seq Scan on tbluser (cost=0.00..2,056.55 rows=46,155 width=130) (actual time=0.006..10.469 rows=46,155 loops=1)

78. 0.004 0.016 ↑ 1.0 45 1

Hash (cost=1.45..1.45 rows=45 width=8) (actual time=0.016..0.016 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
79. 0.012 0.012 ↑ 1.0 45 1

Seq Scan on tblgroup (cost=0.00..1.45 rows=45 width=8) (actual time=0.008..0.012 rows=45 loops=1)

80. 0.023 4,581.707 ↑ 1.4 86 1

Hash (cost=802.65..802.65 rows=118 width=77) (actual time=4,581.707..4,581.707 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
81. 0.000 4,581.684 ↑ 1.4 86 1

Nested Loop Left Join (cost=0.29..802.65 rows=118 width=77) (actual time=4,581.383..4,581.684 rows=86 loops=1)

82. 4,581.525 4,581.525 ↑ 1.4 86 1

CTE Scan on producer_policies (cost=0.00..2.36 rows=118 width=48) (actual time=4,581.373..4,581.525 rows=86 loops=1)

83. 0.172 0.172 ↑ 1.0 1 86

Index Scan using tblemployee_pkey on tblemployee e (cost=0.29..6.78 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=86)

  • Index Cond: (producer_policies.idproducer = id)
84. 0.008 0.024 ↑ 1.0 67 1

Hash (cost=2.67..2.67 rows=67 width=22) (actual time=0.024..0.024 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
85. 0.016 0.016 ↑ 1.0 67 1

Seq Scan on tbloperatorunit ou (cost=0.00..2.67 rows=67 width=22) (actual time=0.008..0.016 rows=67 loops=1)

86. 1,842.034 1,842.034 ↑ 2.0 86 86

CTE Scan on policies_clients (cost=0.00..3.46 rows=173 width=16) (actual time=21.414..21.419 rows=86 loops=86)

87. 1.720 1.720 ↑ 1.0 172 86

CTE Scan on quotes_clients (cost=0.00..3.46 rows=173 width=16) (actual time=0.010..0.020 rows=172 loops=86)

88. 5.762 5.762 ↓ 1.5 172 86

CTE Scan on producer_quotes (cost=0.00..2.36 rows=118 width=48) (actual time=0.040..0.067 rows=172 loops=86)

Planning time : 5.409 ms
Execution time : 6,531.542 ms