explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4CJ5

Settings
# exclusive inclusive rows x rows loops node
1. 27.134 4,410.491 ↓ 12,503.0 12,503 1

Nested Loop (cost=4,030,772.96..4,030,987.24 rows=1 width=759) (actual time=4,163.262..4,410.491 rows=12,503 loops=1)

2.          

CTE offers

3. 71.099 585.895 ↓ 1.3 5,047 1

HashAggregate (cost=85,742.30..85,780.02 rows=3,772 width=32) (actual time=584.953..585.895 rows=5,047 loops=1)

  • Group Key: p.idemployee
4. 34.836 514.796 ↑ 1.0 196,481 1

Hash Join (cost=48,530.58..80,323.86 rows=197,034 width=17) (actual time=224.948..514.796 rows=196,481 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
5. 77.952 479.937 ↑ 1.0 196,481 1

Hash Join (cost=48,525.82..79,773.79 rows=197,034 width=27) (actual time=224.917..479.937 rows=196,481 loops=1)

  • Hash Cond: (pv.idpolicy = p.id)
6. 132.450 266.822 ↑ 1.0 205,514 1

Hash Join (cost=9,694.11..40,400.63 rows=206,261 width=17) (actual time=89.535..266.822 rows=205,514 loops=1)

  • Hash Cond: (pv.id = o.id)
7. 45.057 45.057 ↑ 1.0 349,981 1

Seq Scan on tblpolicyversion pv (cost=0.00..29,787.81 rows=349,981 width=16) (actual time=0.004..45.057 rows=349,981 loops=1)

8. 28.952 89.315 ↑ 1.0 205,514 1

Hash (cost=7,115.85..7,115.85 rows=206,261 width=17) (actual time=89.314..89.315 rows=205,514 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11897kB
9. 60.363 60.363 ↑ 1.0 205,514 1

Seq Scan on tblofferdata o (cost=0.00..7,115.85 rows=206,261 width=17) (actual time=0.019..60.363 rows=205,514 loops=1)

  • Filter: (NOT archive)
  • Rows Removed by Filter: 73874
10. 46.571 135.163 ↑ 1.0 259,426 1

Hash (cost=35,588.81..35,588.81 rows=259,432 width=26) (actual time=135.162..135.163 rows=259,426 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16810kB
11. 88.592 88.592 ↑ 1.0 259,426 1

Seq Scan on tblpolicy p (cost=0.00..35,588.81 rows=259,432 width=26) (actual time=0.012..88.592 rows=259,426 loops=1)

  • Filter: (NOT test)
  • Rows Removed by Filter: 12488
12. 0.008 0.023 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=11) (actual time=0.023..0.023 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.015 0.015 ↑ 1.5 53 1

Seq Scan on tblgeneralagreement ga (cost=0.00..3.78 rows=78 width=11) (actual time=0.010..0.015 rows=53 loops=1)

14.          

CTE offers_12

15. 23.725 390.814 ↑ 1.2 3,063 1

HashAggregate (cost=80,786.15..80,823.87 rows=3,772 width=32) (actual time=390.271..390.814 rows=3,063 loops=1)

  • Group Key: p_1.idemployee
16. 11.541 367.089 ↓ 1.2 65,359 1

Hash Join (cost=42,012.86..79,275.77 rows=54,923 width=17) (actual time=234.310..367.089 rows=65,359 loops=1)

  • Hash Cond: (p_1.idgeneralagreement = ga_1.id)
17. 38.157 355.514 ↓ 1.2 65,359 1

Hash Join (cost=42,008.10..79,119.01 rows=54,923 width=27) (actual time=234.264..355.514 rows=65,359 loops=1)

  • Hash Cond: (p_1.id = pv_1.idpolicy)
18. 83.363 83.363 ↑ 1.0 259,426 1

Seq Scan on tblpolicy p_1 (cost=0.00..35,588.81 rows=259,432 width=26) (actual time=0.009..83.363 rows=259,426 loops=1)

  • Filter: (NOT test)
  • Rows Removed by Filter: 12488
19. 13.137 233.994 ↓ 1.2 67,642 1

Hash (cost=41,289.41..41,289.41 rows=57,495 width=17) (actual time=233.994..233.994 rows=67,642 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4217kB
20. 38.479 220.857 ↓ 1.2 67,642 1

Hash Join (cost=33,632.12..41,289.41 rows=57,495 width=17) (actual time=139.601..220.857 rows=67,642 loops=1)

  • Hash Cond: (o_1.id = pv_1.id)
21. 43.304 43.304 ↑ 1.0 205,514 1

Seq Scan on tblofferdata o_1 (cost=0.00..7,115.85 rows=206,261 width=17) (actual time=0.010..43.304 rows=205,514 loops=1)

  • Filter: (NOT archive)
  • Rows Removed by Filter: 73874
22. 15.423 139.074 ↓ 1.0 98,511 1

Hash (cost=32,412.67..32,412.67 rows=97,556 width=16) (actual time=139.074..139.074 rows=98,511 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5642kB
23. 123.651 123.651 ↓ 1.0 98,511 1

Seq Scan on tblpolicyversion pv_1 (cost=0.00..32,412.67 rows=97,556 width=16) (actual time=0.014..123.651 rows=98,511 loops=1)

  • Filter: (createdtime >= (now() - '1 year'::interval))
  • Rows Removed by Filter: 251470
24. 0.008 0.034 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=11) (actual time=0.034..0.034 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.026 0.026 ↑ 1.5 53 1

Seq Scan on tblgeneralagreement ga_1 (cost=0.00..3.78 rows=78 width=11) (actual time=0.019..0.026 rows=53 loops=1)

26.          

CTE logins

27. 0.014 1,068.785 ↑ 6.0 1 1

GroupAggregate (cost=107,828.78..107,828.92 rows=6 width=25) (actual time=1,068.785..1,068.785 rows=1 loops=1)

  • Group Key: tblsecuritylog.identity
28. 0.026 1,068.771 ↓ 6.7 40 1

Sort (cost=107,828.78..107,828.80 rows=6 width=9) (actual time=1,068.770..1,068.771 rows=40 loops=1)

  • Sort Key: tblsecuritylog.identity
  • Sort Method: quicksort Memory: 26kB
29. 1,027.681 1,068.745 ↓ 6.7 40 1

Bitmap Heap Scan on tblsecuritylog (cost=11,371.19..107,828.71 rows=6 width=9) (actual time=1,068.618..1,068.745 rows=40 loops=1)

  • Recheck Cond: (eventtype = 'LOGIN'::text)
  • Filter: (comment ~* ' logged '::text)
  • Rows Removed by Filter: 598949
  • Heap Blocks: exact=85944
30. 41.064 41.064 ↑ 1.0 598,989 1

Bitmap Index Scan on tblsecuritylog_eventtype (cost=0.00..11,371.19 rows=612,101 width=0) (actual time=41.064..41.064 rows=598,989 loops=1)

  • Index Cond: (eventtype = 'LOGIN'::text)
31.          

CTE logins_12

32. 0.013 824.542 ↑ 2.0 1 1

GroupAggregate (cost=112,419.47..112,419.52 rows=2 width=25) (actual time=824.542..824.542 rows=1 loops=1)

  • Group Key: tblsecuritylog_1.identity
33. 0.015 824.529 ↓ 20.0 40 1

Sort (cost=112,419.47..112,419.48 rows=2 width=9) (actual time=824.528..824.529 rows=40 loops=1)

  • Sort Key: tblsecuritylog_1.identity
  • Sort Method: quicksort Memory: 26kB
34. 789.553 824.514 ↓ 20.0 40 1

Bitmap Heap Scan on tblsecuritylog tblsecuritylog_1 (cost=11,371.19..112,419.46 rows=2 width=9) (actual time=824.408..824.514 rows=40 loops=1)

  • Recheck Cond: (eventtype = 'LOGIN'::text)
  • Filter: ((comment ~* ' logged '::text) AND (eventtime >= (now() - '1 year'::interval)))
  • Rows Removed by Filter: 598949
  • Heap Blocks: exact=85944
35. 34.961 34.961 ↑ 1.0 598,989 1

Bitmap Index Scan on tblsecuritylog_eventtype (cost=0.00..11,371.19 rows=612,101 width=0) (actual time=34.961..34.961 rows=598,989 loops=1)

  • Index Cond: (eventtype = 'LOGIN'::text)
36.          

CTE policiesdatawindow

37. 8.485 214.086 ↑ 1.2 51,327 1

Hash Join (cost=37,015.78..68,557.62 rows=59,569 width=33) (actual time=96.410..214.086 rows=51,327 loops=1)

  • Hash Cond: (p_2.idgeneralagreement = ga_2.id)
38. 39.226 205.570 ↑ 1.2 51,327 1

Hash Join (cost=37,011.02..68,388.00 rows=59,569 width=43) (actual time=96.358..205.570 rows=51,327 loops=1)

  • Hash Cond: (pv_2.idpolicy = p_2.id)
39. 70.276 70.276 ↑ 1.0 271,963 1

Seq Scan on tblpolicyversion pv_2 (cost=0.00..30,662.76 rows=272,075 width=16) (actual time=0.007..70.276 rows=271,963 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 78018
40. 10.064 96.068 ↑ 1.2 51,327 1

Hash (cost=36,267.76..36,267.76 rows=59,461 width=35) (actual time=96.068..96.068 rows=51,327 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4232kB
41. 86.004 86.004 ↑ 1.2 51,327 1

Seq Scan on tblpolicy p_2 (cost=0.00..36,267.76 rows=59,461 width=35) (actual time=0.009..86.004 rows=51,327 loops=1)

  • Filter: ((canceldate IS NULL) AND (NOT test) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 220587
42. 0.012 0.031 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=11) (actual time=0.031..0.031 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
43. 0.019 0.019 ↑ 1.5 53 1

Seq Scan on tblgeneralagreement ga_2 (cost=0.00..3.78 rows=78 width=11) (actual time=0.013..0.019 rows=53 loops=1)

44.          

CTE premiums

45. 57.667 407.225 ↑ 1.2 51,327 1

GroupAggregate (cost=38,541.64..48,755.57 rows=59,569 width=136) (actual time=302.700..407.225 rows=51,327 loops=1)

  • Group Key: rp.idpolicyversion
46. 40.851 349.558 ↑ 2.5 73,410 1

WindowAgg (cost=38,541.64..42,601.79 rows=180,451 width=50) (actual time=302.684..349.558 rows=73,410 loops=1)

47. 39.198 308.707 ↑ 2.5 73,410 1

Sort (cost=38,541.64..38,992.77 rows=180,451 width=42) (actual time=302.666..308.707 rows=73,410 loops=1)

  • Sort Key: rp.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 10023kB
48. 33.849 269.509 ↑ 2.5 73,410 1

Hash Join (cost=19,418.95..22,787.14 rows=180,451 width=42) (actual time=233.554..269.509 rows=73,410 loops=1)

  • Hash Cond: (w.idpolicyversion = rp.idpolicyversion)
49. 5.971 5.971 ↑ 1.2 51,327 1

CTE Scan on policiesdatawindow w (cost=0.00..1,191.38 rows=59,569 width=8) (actual time=0.001..5.971 rows=51,327 loops=1)

50. 135.512 229.689 ↑ 1.0 536,931 1

Hash (cost=12,707.31..12,707.31 rows=536,931 width=42) (actual time=229.689..229.689 rows=536,931 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 49269kB
51. 94.177 94.177 ↑ 1.0 536,931 1

Seq Scan on tblriskpremium rp (cost=0.00..12,707.31 rows=536,931 width=42) (actual time=0.014..94.177 rows=536,931 loops=1)

52.          

CTE policies

53. 13.396 719.547 ↓ 17.3 3,458 1

GroupAggregate (cost=2,414,502.34..2,591,928.13 rows=200 width=48) (actual time=701.944..719.547 rows=3,458 loops=1)

  • Group Key: p_3.idemployee
54. 15.105 706.151 ↑ 345.7 51,327 1

Sort (cost=2,414,502.34..2,458,858.17 rows=17,742,329 width=81) (actual time=701.914..706.151 rows=51,327 loops=1)

  • Sort Key: p_3.idemployee
  • Sort Method: quicksort Memory: 5546kB
55. 13.669 691.046 ↑ 345.7 51,327 1

Merge Join (cost=11,831.76..278,264.54 rows=17,742,329 width=81) (actual time=670.522..691.046 rows=51,327 loops=1)

  • Merge Cond: (p_3.idpolicyversion = premiums.idpolicyversion)
56. 17.426 245.075 ↑ 1.2 51,327 1

Sort (cost=5,915.88..6,064.80 rows=59,569 width=25) (actual time=240.582..245.075 rows=51,327 loops=1)

  • Sort Key: p_3.idpolicyversion
  • Sort Method: quicksort Memory: 5546kB
57. 227.649 227.649 ↑ 1.2 51,327 1

CTE Scan on policiesdatawindow p_3 (cost=0.00..1,191.38 rows=59,569 width=25) (actual time=96.415..227.649 rows=51,327 loops=1)

58. 10.575 432.302 ↑ 1.2 51,327 1

Sort (cost=5,915.88..6,064.80 rows=59,569 width=72) (actual time=429.925..432.302 rows=51,327 loops=1)

  • Sort Key: premiums.idpolicyversion
  • Sort Method: quicksort Memory: 5546kB
59. 421.727 421.727 ↑ 1.2 51,327 1

CTE Scan on premiums (cost=0.00..1,191.38 rows=59,569 width=72) (actual time=302.703..421.727 rows=51,327 loops=1)

60.          

CTE policies_12

61. 3.671 38.195 ↓ 9.9 1,979 1

GroupAggregate (cost=762,980.03..822,122.63 rows=200 width=48) (actual time=33.597..38.195 rows=1,979 loops=1)

  • Group Key: p_4.idemployee
62. 3.072 34.524 ↑ 517.5 11,429 1

Sort (cost=762,980.03..777,765.06 rows=5,914,010 width=81) (actual time=33.578..34.524 rows=11,429 loops=1)

  • Sort Key: p_4.idemployee
  • Sort Method: quicksort Memory: 1277kB
63. 5.219 31.452 ↑ 517.5 11,429 1

Merge Join (cost=8,971.48..97,780.91 rows=5,914,010 width=81) (actual time=27.146..31.452 rows=11,429 loops=1)

  • Merge Cond: (p_4.idpolicyversion = premiums_1.idpolicyversion)
64. 2.924 11.577 ↑ 1.7 11,429 1

Sort (cost=3,055.60..3,105.24 rows=19,856 width=25) (actual time=10.719..11.577 rows=11,429 loops=1)

  • Sort Key: p_4.idpolicyversion
  • Sort Method: quicksort Memory: 1277kB
65. 8.653 8.653 ↑ 1.7 11,429 1

CTE Scan on policiesdatawindow p_4 (cost=0.00..1,638.15 rows=19,856 width=25) (actual time=0.006..8.653 rows=11,429 loops=1)

  • Filter: (createdtime >= (now() - '1 year'::interval))
  • Rows Removed by Filter: 39898
66. 9.168 14.656 ↑ 1.2 51,327 1

Sort (cost=5,915.88..6,064.80 rows=59,569 width=72) (actual time=12.679..14.656 rows=51,327 loops=1)

  • Sort Key: premiums_1.idpolicyversion
  • Sort Method: quicksort Memory: 5546kB
67. 5.488 5.488 ↑ 1.2 51,327 1

CTE Scan on premiums premiums_1 (cost=0.00..1,191.38 rows=59,569 width=72) (actual time=0.002..5.488 rows=51,327 loops=1)

68.          

CTE bids

69. 32.629 238.636 ↓ 1.4 5,097 1

GroupAggregate (cost=57,855.44..59,704.52 rows=3,772 width=24) (actual time=182.205..238.636 rows=5,097 loops=1)

  • Group Key: p_5.idemployee
70. 71.106 206.007 ↓ 1.0 241,966 1

Sort (cost=57,855.44..58,459.23 rows=241,514 width=16) (actual time=182.184..206.007 rows=241,966 loops=1)

  • Sort Key: p_5.idemployee
  • Sort Method: quicksort Memory: 17487kB
71. 36.958 134.901 ↓ 1.0 241,966 1

Hash Join (cost=4.76..36,261.98 rows=241,514 width=16) (actual time=0.055..134.901 rows=241,966 loops=1)

  • Hash Cond: (p_5.idgeneralagreement = ga_3.id)
72. 97.917 97.917 ↓ 1.0 241,966 1

Seq Scan on tblpolicy p_5 (cost=0.00..35,588.81 rows=241,514 width=26) (actual time=0.016..97.917 rows=241,966 loops=1)

  • Filter: ((canceldate IS NULL) AND (NOT test))
  • Rows Removed by Filter: 29948
73. 0.010 0.026 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=11) (actual time=0.026..0.026 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
74. 0.016 0.016 ↑ 1.5 53 1

Seq Scan on tblgeneralagreement ga_3 (cost=0.00..3.78 rows=78 width=11) (actual time=0.010..0.016 rows=53 loops=1)

75.          

CTE bids_12

76. 11.421 155.852 ↑ 1.2 3,067 1

GroupAggregate (cost=43,782.64..44,372.27 rows=3,772 width=24) (actual time=137.784..155.852 rows=3,067 loops=1)

  • Group Key: p_6.idemployee
77. 20.245 144.431 ↓ 1.0 74,090 1

Sort (cost=43,782.64..43,966.61 rows=73,588 width=16) (actual time=137.765..144.431 rows=74,090 loops=1)

  • Sort Key: p_6.idemployee
  • Sort Method: quicksort Memory: 6545kB
78. 11.847 124.186 ↓ 1.0 74,090 1

Hash Join (cost=4.76..37,834.08 rows=73,588 width=16) (actual time=0.067..124.186 rows=74,090 loops=1)

  • Hash Cond: (p_6.idgeneralagreement = ga_4.id)
79. 112.309 112.309 ↓ 1.0 74,090 1

Seq Scan on tblpolicy p_6 (cost=0.00..37,625.67 rows=73,588 width=26) (actual time=0.026..112.309 rows=74,090 loops=1)

  • Filter: ((canceldate IS NULL) AND (NOT test) AND (createdtime >= (now() - '1 year'::interval)))
  • Rows Removed by Filter: 197824
80. 0.012 0.030 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=11) (actual time=0.030..0.030 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
81. 0.018 0.018 ↑ 1.5 53 1

Seq Scan on tblgeneralagreement ga_4 (cost=0.00..3.78 rows=78 width=11) (actual time=0.011..0.018 rows=53 loops=1)

82. 3.761 4,333.345 ↓ 12,503.0 12,503 1

Nested Loop (cost=8,479.61..8,652.87 rows=1 width=643) (actual time=4,163.210..4,333.345 rows=12,503 loops=1)

83. 4.778 4,317.081 ↓ 12,503.0 12,503 1

Nested Loop Left Join (cost=8,479.33..8,646.57 rows=1 width=604) (actual time=4,163.193..4,317.081 rows=12,503 loops=1)

84. 6.805 4,199.776 ↓ 12,503.0 12,503 1

Hash Join (cost=8,477.62..8,638.29 rows=1 width=454) (actual time=4,163.123..4,199.776 rows=12,503 loops=1)

  • Hash Cond: ((ltree2text(subpath(u2_1.path, 0, 1)))::bigint = u1_1.id)
85. 4.221 4,192.213 ↓ 192.4 12,503 1

Hash Join (cost=8,419.66..8,580.14 rows=65 width=421) (actual time=4,162.352..4,192.213 rows=12,503 loops=1)

  • Hash Cond: (e.idagencyunit = u2_1.id)
86. 6.715 4,187.069 ↓ 1.0 12,632 1

Hash Right Join (cost=8,361.71..8,489.01 rows=12,623 width=371) (actual time=4,161.422..4,187.069 rows=12,632 loops=1)

  • Hash Cond: (bids_12.idemployee = e.id)
87. 156.742 156.742 ↑ 1.2 3,067 1

CTE Scan on bids_12 (cost=0.00..75.44 rows=3,772 width=16) (actual time=137.787..156.742 rows=3,067 loops=1)

88. 4.812 4,023.612 ↓ 1.0 12,632 1

Hash (cost=8,203.92..8,203.92 rows=12,623 width=363) (actual time=4,023.612..4,023.612 rows=12,632 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2551kB
89. 5.489 4,018.800 ↓ 1.0 12,632 1

Hash Right Join (cost=8,076.62..8,203.92 rows=12,623 width=363) (actual time=3,955.568..4,018.800 rows=12,632 loops=1)

  • Hash Cond: (bids.idemployee = e.id)
90. 239.973 239.973 ↓ 1.4 5,097 1

CTE Scan on bids (cost=0.00..75.44 rows=3,772 width=16) (actual time=182.208..239.973 rows=5,097 loops=1)

91. 5.399 3,773.338 ↓ 1.0 12,632 1

Hash (cost=7,918.83..7,918.83 rows=12,623 width=355) (actual time=3,773.338..3,773.338 rows=12,632 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2503kB
92. 4.863 3,767.939 ↓ 1.0 12,632 1

Hash Right Join (cost=7,912.08..7,918.83 rows=12,623 width=355) (actual time=3,758.034..3,767.939 rows=12,632 loops=1)

  • Hash Cond: (policies_12.idemployee = e.id)
93. 38.661 38.661 ↓ 9.9 1,979 1

CTE Scan on policies_12 (cost=0.00..4.00 rows=200 width=48) (actual time=33.599..38.661 rows=1,979 loops=1)

94. 4.749 3,724.415 ↓ 1.0 12,632 1

Hash (cost=7,754.29..7,754.29 rows=12,623 width=315) (actual time=3,724.415..3,724.415 rows=12,632 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2471kB
95. 1.950 3,719.666 ↓ 1.0 12,632 1

Hash Left Join (cost=7,228.38..7,754.29 rows=12,623 width=315) (actual time=3,703.480..3,719.666 rows=12,632 loops=1)

  • Hash Cond: (tbluser.id = logins_12.identity)
96. 2.000 2,893.159 ↓ 1.0 12,632 1

Hash Left Join (cost=7,228.31..7,705.63 rows=12,623 width=315) (actual time=2,878.909..2,893.159 rows=12,632 loops=1)

  • Hash Cond: (tbluser.id = logins.identity)
97. 5.780 1,822.365 ↓ 1.0 12,632 1

Hash Join (cost=7,228.12..7,654.31 rows=12,623 width=307) (actual time=1,810.098..1,822.365 rows=12,632 loops=1)

  • Hash Cond: (tbluser.id = tu.id)
98. 61.432 80.211 ↓ 1.0 12,639 1

HashAggregate (cost=5,222.02..5,348.32 rows=12,630 width=199) (actual time=73.705..80.211 rows=12,639 loops=1)

  • Group Key: tbluser.id
99. 2.982 18.779 ↓ 1.0 16,309 1

Hash Join (cost=734.23..1,108.54 rows=16,291 width=167) (actual time=7.773..18.779 rows=16,309 loops=1)

  • Hash Cond: (tblusergroup.idgroup = tblgroup.id)
100. 6.079 15.764 ↓ 1.0 16,309 1

Hash Join (cost=732.17..1,059.86 rows=16,291 width=143) (actual time=7.727..15.764 rows=16,309 loops=1)

  • Hash Cond: (tblusergroup.iduser = tbluser.id)
101. 2.041 2.041 ↓ 1.0 16,309 1

Seq Scan on tblusergroup (cost=0.00..284.91 rows=16,291 width=16) (actual time=0.009..2.041 rows=16,309 loops=1)

102. 4.265 7.644 ↓ 1.0 12,640 1

Hash (cost=574.30..574.30 rows=12,630 width=135) (actual time=7.644..7.644 rows=12,640 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2298kB
103. 3.379 3.379 ↓ 1.0 12,640 1

Seq Scan on tbluser (cost=0.00..574.30 rows=12,630 width=135) (actual time=0.009..3.379 rows=12,640 loops=1)

104. 0.008 0.033 ↑ 1.0 47 1

Hash (cost=1.47..1.47 rows=47 width=40) (actual time=0.033..0.033 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
105. 0.025 0.025 ↑ 1.0 47 1

Seq Scan on tblgroup (cost=0.00..1.47 rows=47 width=40) (actual time=0.020..0.025 rows=47 loops=1)

106. 4.117 1,736.374 ↓ 1.0 12,633 1

Hash (cost=1,848.31..1,848.31 rows=12,623 width=233) (actual time=1,736.374..1,736.374 rows=12,633 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1829kB
107. 3.697 1,732.257 ↓ 1.0 12,633 1

Hash Join (cost=1,687.86..1,848.31 rows=12,623 width=233) (actual time=1,724.101..1,732.257 rows=12,633 loops=1)

  • Hash Cond: (e.iduser = tu.id)
108. 3.285 1,721.323 ↓ 1.0 12,633 1

Hash Right Join (cost=955.68..1,082.99 rows=12,623 width=208) (actual time=1,716.845..1,721.323 rows=12,633 loops=1)

  • Hash Cond: (offers_12.idemployee = e.id)
109. 391.510 391.510 ↑ 1.2 3,063 1

CTE Scan on offers_12 (cost=0.00..75.44 rows=3,772 width=24) (actual time=390.274..391.510 rows=3,063 loops=1)

110. 2.717 1,326.528 ↓ 1.0 12,633 1

Hash (cost=797.90..797.90 rows=12,623 width=192) (actual time=1,326.527..1,326.528 rows=12,633 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1432kB
111. 3.386 1,323.811 ↓ 1.0 12,633 1

Hash Right Join (cost=791.15..797.90 rows=12,623 width=192) (actual time=1,302.021..1,323.811 rows=12,633 loops=1)

  • Hash Cond: (policies.idemployee = e.id)
112. 720.372 720.372 ↓ 17.3 3,458 1

CTE Scan on policies (cost=0.00..4.00 rows=200 width=48) (actual time=701.947..720.372 rows=3,458 loops=1)

113. 3.212 600.053 ↓ 1.0 12,633 1

Hash (cost=633.36..633.36 rows=12,623 width=152) (actual time=600.053..600.053 rows=12,633 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1384kB
114. 3.589 596.841 ↓ 1.0 12,633 1

Hash Right Join (cost=548.02..633.36 rows=12,623 width=152) (actual time=591.291..596.841 rows=12,633 loops=1)

  • Hash Cond: (offers.idemployee = e.id)
115. 586.936 586.936 ↓ 1.3 5,047 1

CTE Scan on offers (cost=0.00..75.44 rows=3,772 width=24) (actual time=584.956..586.936 rows=5,047 loops=1)

116. 2.233 6.316 ↓ 1.0 12,633 1

Hash (cost=390.23..390.23 rows=12,623 width=136) (actual time=6.316..6.316 rows=12,633 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1305kB
117. 4.083 4.083 ↓ 1.0 12,633 1

Seq Scan on tblemployee e (cost=0.00..390.23 rows=12,623 width=136) (actual time=0.012..4.083 rows=12,633 loops=1)

118. 1.560 7.237 ↓ 1.0 12,640 1

Hash (cost=574.30..574.30 rows=12,630 width=25) (actual time=7.237..7.237 rows=12,640 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 872kB
119. 5.677 5.677 ↓ 1.0 12,640 1

Seq Scan on tbluser tu (cost=0.00..574.30 rows=12,630 width=25) (actual time=0.014..5.677 rows=12,640 loops=1)

120. 0.005 1,068.794 ↑ 6.0 1 1

Hash (cost=0.12..0.12 rows=6 width=16) (actual time=1,068.794..1,068.794 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
121. 1,068.789 1,068.789 ↑ 6.0 1 1

CTE Scan on logins (cost=0.00..0.12 rows=6 width=16) (actual time=1,068.788..1,068.789 rows=1 loops=1)

122. 0.005 824.557 ↑ 2.0 1 1

Hash (cost=0.04..0.04 rows=2 width=16) (actual time=824.557..824.557 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
123. 824.552 824.552 ↑ 2.0 1 1

CTE Scan on logins_12 (cost=0.00..0.04 rows=2 width=16) (actual time=824.552..824.552 rows=1 loops=1)

124. 0.326 0.923 ↓ 119.1 2,621 1

Hash (cost=57.68..57.68 rows=22 width=50) (actual time=0.923..0.923 rows=2,621 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 244kB
125. 0.523 0.597 ↓ 119.1 2,621 1

Bitmap Heap Scan on tblagencyunit u2_1 (cost=4.45..57.68 rows=22 width=50) (actual time=0.085..0.597 rows=2,621 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Heap Blocks: exact=97
126. 0.074 0.074 ↓ 119.1 2,621 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.074..0.074 rows=2,621 loops=1)

  • Index Cond: (nlevel(path) = 2)
127. 0.227 0.758 ↓ 76.5 1,684 1

Hash (cost=57.68..57.68 rows=22 width=33) (actual time=0.758..0.758 rows=1,684 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 145kB
128. 0.465 0.531 ↓ 76.5 1,684 1

Bitmap Heap Scan on tblagencyunit u1_1 (cost=4.45..57.68 rows=22 width=33) (actual time=0.078..0.531 rows=1,684 loops=1)

  • Recheck Cond: (nlevel(path) = 1)
  • Heap Blocks: exact=71
129. 0.066 0.066 ↓ 76.6 1,686 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.065..0.066 rows=1,686 loops=1)

  • Index Cond: (nlevel(path) = 1)
130. 12.503 112.527 ↑ 1.0 1 12,503

Nested Loop (cost=1.71..8.27 rows=1 width=158) (actual time=0.009..0.009 rows=1 loops=12,503)

131. 0.000 87.521 ↑ 1.0 1 12,503

Nested Loop (cost=1.43..7.81 rows=1 width=166) (actual time=0.007..0.007 rows=1 loops=12,503)

132. 0.000 75.018 ↑ 1.0 1 12,503

Nested Loop (cost=1.14..7.48 rows=1 width=121) (actual time=0.006..0.006 rows=1 loops=12,503)

133. 12.503 62.515 ↑ 1.0 1 12,503

Nested Loop (cost=0.86..7.06 rows=1 width=138) (actual time=0.004..0.005 rows=1 loops=12,503)

134. 0.000 37.509 ↑ 1.0 1 12,503

Nested Loop (cost=0.57..0.74 rows=1 width=49) (actual time=0.003..0.003 rows=1 loops=12,503)

135. 25.006 25.006 ↑ 1.0 1 12,503

Index Scan using tblemployee_pkey on tblemployee e_1 (cost=0.29..0.40 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=12,503)

  • Index Cond: (id = e.id)
136. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit u2 (cost=0.28..0.34 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = e_1.idagencyunit)
  • Filter: (nlevel(path) = 2)
137. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit a (cost=0.29..6.31 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = (ltree2text(subpath(u2.path, 0, 1)))::bigint)
138. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.28..0.41 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = a.id)
  • Filter: (nlevel(path) = 1)
139. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit d_1 (cost=0.28..0.34 rows=1 width=61) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = e_1.idagencyunit)
140. 12.503 12.503 ↑ 1.0 1 12,503

Index Only Scan using tbluser_pkey on tbluser u (cost=0.29..0.46 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = e_1.iduser)
  • Heap Fetches: 12503
141. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit au (cost=0.28..6.30 rows=1 width=72) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = u1_1.id)
142. 12.503 12.503 ↑ 1.0 1 12,503

Index Scan using tblagencyunit_pkey on tblagencyunit d (cost=0.28..0.34 rows=1 width=72) (actual time=0.001..0.001 rows=1 loops=12,503)

  • Index Cond: (id = e.idagencyunit)
143.          

SubPlan (forNested Loop)

144. 12.503 12.503 ↑ 1.0 1 12,503

Aggregate (cost=13.64..13.65 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=12,503)

145. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Heap Scan on tblagencyunittag t (cost=4.18..12.64 rows=4 width=32) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Recheck Cond: (idagencyunit = a.id)
146. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Index Scan on tblagencyunittag_pkey (cost=0.00..4.18 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Index Cond: (idagencyunit = a.id)
147. 12.503 12.503 ↑ 1.0 1 12,503

Aggregate (cost=13.64..13.65 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=12,503)

148. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Heap Scan on tblagencyunittag t_1 (cost=4.18..12.64 rows=4 width=32) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Recheck Cond: (idagencyunit = d_1.id)
149. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Index Scan on tblagencyunittag_pkey (cost=0.00..4.18 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Index Cond: (idagencyunit = d_1.id)
150. 12.503 12.503 ↑ 1.0 1 12,503

Aggregate (cost=13.64..13.65 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=12,503)

151. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Heap Scan on tblemployeetag t_2 (cost=4.18..12.64 rows=4 width=32) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Recheck Cond: (idemployee = e_1.id)
152. 0.000 0.000 ↓ 0.0 0 12,503

Bitmap Index Scan on tblemployeetag_pkey (cost=0.00..4.18 rows=4 width=0) (actual time=0.000..0.000 rows=0 loops=12,503)

  • Index Cond: (idemployee = e_1.id)
Planning time : 13.636 ms
Execution time : 4,414.247 ms