explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nnSl

Settings
# exclusive inclusive rows x rows loops node
1. 192.730 1,837.305 ↓ 2.5 16,789 1

Result (cost=116,550.47..118,474.79 rows=6,752 width=397) (actual time=1,641.823..1,837.305 rows=16,789 loops=1)

2.          

Initplan (forResult)

3. 0.018 0.018 ↑ 1.0 1 1

Index Scan using tblemployee_pkey on tblemployee (cost=0.29..8.31 rows=1 width=1) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (id = 4677)
4. 21.780 1,644.557 ↓ 2.5 16,789 1

Sort (cost=116,542.16..116,559.04 rows=6,752 width=373) (actual time=1,641.739..1,644.557 rows=16,789 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 9272kB
5. 38.243 1,622.777 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,245.66..116,112.70 rows=6,752 width=373) (actual time=859.543..1,622.777 rows=16,789 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 22154
6. 7.084 1,550.956 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,245.24..109,418.57 rows=6,752 width=506) (actual time=859.526..1,550.956 rows=16,789 loops=1)

7. 9.324 1,493.505 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,244.95..106,281.59 rows=6,752 width=482) (actual time=859.513..1,493.505 rows=16,789 loops=1)

8. 10.704 1,400.236 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,244.52..99,806.90 rows=6,752 width=405) (actual time=859.498..1,400.236 rows=16,789 loops=1)

9. 14.581 1,339.165 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,244.11..96,220.21 rows=6,752 width=405) (actual time=859.483..1,339.165 rows=16,789 loops=1)

10. 8.356 1,274.217 ↓ 2.5 16,789 1

Hash Left Join (cost=76,243.69..92,987.45 rows=6,752 width=397) (actual time=859.470..1,274.217 rows=16,789 loops=1)

  • Hash Cond: (p.idgeneralagreement = ega.id)
  • Filter: ((ega.id IS NOT NULL) OR (p.idemployee = 4677) OR (hashed SubPlan 6) OR (hashed SubPlan 7) OR (hashed SubPlan 8) OR (hashed SubPlan 9) OR (hashed SubPlan 10) OR (hashed SubPlan 11))
11. 5.919 1,265.825 ↓ 2.5 16,789 1

Hash Left Join (cost=71,682.10..88,403.85 rows=6,753 width=413) (actual time=859.420..1,265.825 rows=16,789 loops=1)

  • Hash Cond: (p.keyintegration = i.key)
12. 6.645 1,259.882 ↓ 2.5 16,789 1

Hash Left Join (cost=71,678.95..88,382.44 rows=6,753 width=381) (actual time=859.389..1,259.882 rows=16,789 loops=1)

  • Hash Cond: (p.idinsurancetype = it_label.id)
13. 8.152 1,253.206 ↓ 2.5 16,789 1

Hash Left Join (cost=71,677.40..88,353.68 rows=6,753 width=349) (actual time=859.353..1,253.206 rows=16,789 loops=1)

  • Hash Cond: (pl.id = pl_label.id)
14. 9.976 1,245.018 ↓ 2.5 16,789 1

Nested Loop (cost=71,676.03..88,324.48 rows=6,753 width=317) (actual time=859.308..1,245.018 rows=16,789 loops=1)

15. 19.379 1,167.886 ↓ 2.5 16,789 1

Nested Loop (cost=71,675.60..84,538.69 rows=6,767 width=325) (actual time=859.293..1,167.886 rows=16,789 loops=1)

  • Join Filter: (pv.id = pc_ubezpieczony.idpolicyversion)
16. 71.116 1,098.140 ↓ 2.5 16,789 1

Hash Join (cost=71,675.18..80,346.34 rows=6,767 width=290) (actual time=859.270..1,098.140 rows=16,789 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = pv.id)
17. 516.415 591.103 ↓ 1.3 411,466 1

HashAggregate (cost=19,453.76..23,550.67 rows=327,752 width=40) (actual time=423.296..591.103 rows=411,466 loops=1)

  • Group Key: tblriskpremium.idpolicyversion
18. 74.688 74.688 ↑ 1.0 662,851 1

Seq Scan on tblriskpremium (cost=0.00..16,139.51 rows=662,851 width=13) (actual time=0.016..74.688 rows=662,851 loops=1)

19. 11.455 435.921 ↓ 2.0 16,789 1

Hash (cost=52,115.22..52,115.22 rows=8,496 width=250) (actual time=435.921..435.921 rows=16,789 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 4074kB
20. 9.158 424.466 ↓ 2.0 16,789 1

Hash Join (cost=26,409.12..52,115.22 rows=8,496 width=250) (actual time=227.754..424.466 rows=16,789 loops=1)

  • Hash Cond: (p.idemployee = employee.id)
21. 7.855 392.597 ↓ 2.0 16,789 1

Hash Join (cost=24,714.05..50,397.85 rows=8,496 width=237) (actual time=204.977..392.597 rows=16,789 loops=1)

  • Hash Cond: (p.idagencyunit = division.id)
22. 7.273 358.472 ↓ 2.0 16,789 1

Hash Join (cost=22,920.67..48,582.16 rows=8,496 width=216) (actual time=178.646..358.472 rows=16,789 loops=1)

  • Hash Cond: (p.idagency = agency.id)
23. 5.649 332.383 ↓ 2.0 16,789 1

Hash Join (cost=21,127.29..46,766.48 rows=8,496 width=192) (actual time=159.740..332.383 rows=16,789 loops=1)

  • Hash Cond: (ga.idproductline = pl.id)
24. 6.232 326.718 ↓ 2.0 16,789 1

Hash Join (cost=21,125.68..46,740.47 rows=8,496 width=192) (actual time=159.709..326.718 rows=16,789 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
25. 4.999 320.467 ↓ 2.0 16,789 1

Hash Join (cost=21,123.67..46,714.05 rows=8,496 width=160) (actual time=159.684..320.467 rows=16,789 loops=1)

  • Hash Cond: (p.idinsurancecompany = cu.id)
26. 39.647 315.460 ↓ 2.0 16,789 1

Hash Join (cost=21,122.26..46,685.09 rows=8,496 width=136) (actual time=159.669..315.460 rows=16,789 loops=1)

  • Hash Cond: (pv.idpolicy = p.id)
27. 116.224 116.224 ↑ 1.0 138,463 1

Seq Scan on tblpolicyversion pv (cost=0.00..25,198.32 rows=138,860 width=36) (actual time=0.009..116.224 rows=138,463 loops=1)

  • Filter: ((policyversionstatus = 'CURRENT'::text) AND (enddate > (now())::date))
  • Rows Removed by Filter: 273003
28. 18.176 159.589 ↓ 1.8 43,340 1

Hash (cost=20,822.49..20,822.49 rows=23,982 width=116) (actual time=159.588..159.589 rows=43,340 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 6802kB
29. 141.413 141.413 ↓ 1.8 43,340 1

Seq Scan on tblpolicy p (cost=21.22..20,822.49 rows=23,982 width=116) (actual time=0.029..141.413 rows=43,340 loops=1)

  • Filter: ((NOT test) AND (canceldate IS NULL) AND CASE $0 WHEN CASE_TEST_EXPR THEN true ELSE (hashed SubPlan 2) END AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 348611
30.          

SubPlan (forSeq Scan)

31. 0.000 0.000 ↓ 0.0 0

Subquery Scan on eiu (cost=21.09..21.21 rows=6 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=21.09..21.15 rows=6 width=16) (never executed)

  • Group Key: tblemployeeinsurancecompanyunit.idemployee, tblemployeeinsurancecompanyunit.idinsurancecompanyunit
33. 0.000 0.000 ↓ 0.0 0

Append (cost=4.19..21.06 rows=6 width=16) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tblemployeeinsurancecompanyunit (cost=4.19..12.66 rows=5 width=16) (never executed)

  • Recheck Cond: (idemployee = 4677)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tblemployeeinsurancecompanyunit_idemployee_index (cost=0.00..4.19 rows=5 width=0) (never executed)

  • Index Cond: (idemployee = 4677)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployee_pkey on tblemployee tblemployee_1 (cost=0.29..8.31 rows=1 width=16) (never executed)

  • Index Cond: (id = 4677)
  • Filter: (idinsurancecompanyunit IS NOT NULL)
37. 0.003 0.008 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=40) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.005 0.005 ↑ 1.0 18 1

Seq Scan on tblinsurancecompanyunit cu (cost=0.00..1.18 rows=18 width=40) (actual time=0.003..0.005 rows=18 loops=1)

39. 0.008 0.019 ↑ 1.0 45 1

Hash (cost=1.45..1.45 rows=45 width=64) (actual time=0.019..0.019 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
40. 0.011 0.011 ↑ 1.0 45 1

Seq Scan on tblgeneralagreement ga (cost=0.00..1.45 rows=45 width=64) (actual time=0.005..0.011 rows=45 loops=1)

41. 0.004 0.016 ↑ 1.0 27 1

Hash (cost=1.27..1.27 rows=27 width=32) (actual time=0.016..0.016 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
42. 0.012 0.012 ↑ 1.0 27 1

Seq Scan on tblproductline pl (cost=0.00..1.27 rows=27 width=32) (actual time=0.010..0.012 rows=27 loops=1)

43. 8.806 18.816 ↓ 1.0 38,419 1

Hash (cost=1,313.17..1,313.17 rows=38,417 width=32) (actual time=18.816..18.816 rows=38,419 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2503kB
44. 10.010 10.010 ↓ 1.0 38,419 1

Seq Scan on tblagencyunit agency (cost=0.00..1,313.17 rows=38,417 width=32) (actual time=0.020..10.010 rows=38,419 loops=1)

45. 10.340 26.270 ↓ 1.0 38,419 1

Hash (cost=1,313.17..1,313.17 rows=38,417 width=29) (actual time=26.270..26.270 rows=38,419 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2944kB
46. 15.930 15.930 ↓ 1.0 38,419 1

Seq Scan on tblagencyunit division (cost=0.00..1,313.17 rows=38,417 width=29) (actual time=0.013..15.930 rows=38,419 loops=1)

47. 11.014 22.711 ↑ 1.0 42,666 1

Hash (cost=1,161.14..1,161.14 rows=42,714 width=21) (actual time=22.711..22.711 rows=42,666 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2887kB
48. 11.697 11.697 ↑ 1.0 42,666 1

Seq Scan on tblemployee employee (cost=0.00..1,161.14 rows=42,714 width=21) (actual time=0.013..11.697 rows=42,666 loops=1)

49. 50.367 50.367 ↑ 1.0 1 16,789

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.61 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=16,789)

  • Index Cond: (idpolicyversion = tblriskpremium.idpolicyversion)
50. 67.156 67.156 ↑ 1.0 1 16,789

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr_ubezpieczony (cost=0.42..0.56 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=16,789)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 16789
51. 0.006 0.036 ↓ 24.0 24 1

Hash (cost=1.36..1.36 rows=1 width=64) (actual time=0.036..0.036 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
52. 0.030 0.030 ↓ 24.0 24 1

Seq Scan on tblproductlinelabel pl_label (cost=0.00..1.36 rows=1 width=64) (actual time=0.015..0.030 rows=24 loops=1)

  • Filter: ('en_GB'::text ~~* (locale || '%'::text))
53. 0.005 0.031 ↓ 19.0 19 1

Hash (cost=1.54..1.54 rows=1 width=64) (actual time=0.031..0.031 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 0.026 0.026 ↓ 19.0 19 1

Seq Scan on tblinsurancetypelabel it_label (cost=0.00..1.54 rows=1 width=64) (actual time=0.005..0.026 rows=19 loops=1)

  • Filter: ('en_GB'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 17
55. 0.012 0.024 ↑ 1.0 51 1

Hash (cost=2.51..2.51 rows=51 width=64) (actual time=0.024..0.024 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
56. 0.012 0.012 ↑ 1.0 51 1

Seq Scan on tblintegration i (cost=0.00..2.51 rows=51 width=64) (actual time=0.005..0.012 rows=51 loops=1)

57. 0.013 0.036 ↑ 1.0 43 1

Hash (cost=3.87..3.87 rows=43 width=32) (actual time=0.036..0.036 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
58. 0.023 0.023 ↑ 1.0 43 1

Seq Scan on tblgeneralagreement ega (cost=0.00..3.87 rows=43 width=32) (actual time=0.006..0.023 rows=43 loops=1)

  • Filter: (id = ANY ('{AJG-USSMB-CR,RPS-CYBER-SCHOLASTIC,RPS-PI-ARCHITECTS,RPS-FL,RPS-CPL-COLONY,RPS-ML-HISCOX,AJG-USSMB-CYBER-CB-ADMITTED,RPS-PI-RIA,RPS-BSP,AJGUSSMB-PI-ACCOUNTANTS,RPS-PI-ACCOUNTANTS,RPS-PI-REA,AJGUSSMB-TERRORISM-2,RPS-TD,RPS-MB-LLOYDS,RPS-PI-MISC,AJGUSSMB-PI-MISC,RPS-MB-ASPEN-PLENO,RPS-IAB,AJGUSSMB-TERRORISM-2-VEHICLE-B,RPS-CR,AJG-USSMB-CYBER-SCHOLASTIC,AJGUSSMB-GCP,RPS-TERRORISM-2-VEHICLE-B,RPS-EL,AJG-CYBER-AXIS,AJG-CYBER-ENO-SIAA,AJG-USSMB-CYBER-CB,AJG-USSMB-CYBER,AJG-CYBER-ENO-SIAA-ADMITTED,RPS-EB,RPS-TERRORISM-2,RPS-MB-ASPEN-BOND,AJG-USSMB-CYBER-AXIS,RPS-MB-LIBERTY-BOND,AJG-USSMB-CYBER-ADMITTED,AJG-CYBER-ENO,RPS-CYBER-HISCOX,RPS-MB-LIBERTY-PLENO,AJGUSSMB-PI-ARCHITECTS,AJG-CYBER-ENO-ADMITTED,RPS-CPL-NAV,RPS-TERR}'::text[]))
  • Rows Removed by Filter: 2
59.          

SubPlan (forHash Left Join)

60. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblpolicyacl acl (cost=1,482.16..1,592.10 rows=3,629 width=8) (never executed)

  • Filter: ((idemployee = 4677) OR (hashed SubPlan 3) OR (hashed SubPlan 4) OR (hashed SubPlan 5))
61.          

SubPlan (forSeq Scan)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (idemployee = 4677)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission tblemployeepermission_1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (idemployee = 4677)
64. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.31..1,465.55 rows=1 width=8) (never executed)

  • Hash Cond: (a.keyagencyunitgroup = ep.keyagencyunitgroup)
65. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblagencyunit a (cost=0.00..1,313.17 rows=38,417 width=20) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=32) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission ep (cost=0.28..8.30 rows=1 width=32) (never executed)

  • Index Cond: (idemployee = 4677)
68. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission tblemployeepermission_2 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (idemployee = 4677)
69. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission tblemployeepermission_3 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (idemployee = 4677)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission tblemployeepermission_4 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (idemployee = 4677)
71. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.31..1,465.55 rows=1 width=8) (never executed)

  • Hash Cond: (a_1.keyagencyunitgroup = ep_1.keyagencyunitgroup)
72. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblagencyunit a_1 (cost=0.00..1,313.17 rows=38,417 width=20) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=32) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission ep_1 (cost=0.28..8.30 rows=1 width=32) (never executed)

  • Index Cond: (idemployee = 4677)
75. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.31..1,465.55 rows=1 width=8) (never executed)

  • Hash Cond: (a_2.keyagencyunitgroup = ep_2.keyagencyunitgroup)
76. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblagencyunit a_2 (cost=0.00..1,313.17 rows=38,417 width=20) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=32) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployeepermission_idemployee_index on tblemployeepermission ep_2 (cost=0.28..8.30 rows=1 width=32) (never executed)

  • Index Cond: (idemployee = 4677)
79. 50.367 50.367 ↑ 1.0 1 16,789

Index Scan using tblclient_pkey on tblclient c (cost=0.42..0.48 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=16,789)

  • Index Cond: (pc_ubezpieczony.idclient = id)
80. 50.367 50.367 ↑ 1.0 1 16,789

Index Only Scan using tblmetaclient_pkey on tblmetaclient mc (cost=0.42..0.53 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=16,789)

  • Index Cond: (id = c.idmetaclient)
  • Heap Fetches: 16789
81. 83.945 83.945 ↑ 1.0 1 16,789

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label (cost=0.42..0.95 rows=1 width=85) (actual time=0.004..0.005 rows=1 loops=16,789)

  • Index Cond: (idpolicyversion = pv.id)
  • Filter: ('en_GB'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
82. 50.367 50.367 ↑ 1.0 1 16,789

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.45 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=16,789)

  • Index Cond: (p.id = idpolicy)
83. 33.578 33.578 ↑ 1.0 2 16,789

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label_fallback (cost=0.42..0.94 rows=2 width=91) (actual time=0.001..0.002 rows=2 loops=16,789)

  • Index Cond: (idpolicyversion = pv.id)