explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PziY

Settings
# exclusive inclusive rows x rows loops node
1. 200.991 1,756.504 ↓ 2.5 16,789 1

Result (cost=116,310.49..118,234.53 rows=6,751 width=397) (actual time=1,552.355..1,756.504 rows=16,789 loops=1)

2.          

Initplan (forResult)

3. 0.019 0.019 ↑ 1.0 1 1

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

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

Sort (cost=116,302.19..116,319.06 rows=6,751 width=373) (actual time=1,552.260..1,555.494 rows=16,789 loops=1)

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

Nested Loop Left Join (cost=76,009.93..115,872.79 rows=6,751 width=373) (actual time=701.875..1,532.529 rows=16,789 loops=1)

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

Nested Loop Left Join (cost=76,009.51..109,179.66 rows=6,751 width=506) (actual time=701.855..1,458.406 rows=16,789 loops=1)

7. 15.909 1,393.101 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,009.22..106,043.14 rows=6,751 width=482) (actual time=701.839..1,393.101 rows=16,789 loops=1)

8. 16.090 1,293.247 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,008.79..99,569.41 rows=6,751 width=405) (actual time=701.820..1,293.247 rows=16,789 loops=1)

9. 19.554 1,226.790 ↓ 2.5 16,789 1

Nested Loop Left Join (cost=76,008.37..95,983.25 rows=6,751 width=405) (actual time=701.798..1,226.790 rows=16,789 loops=1)

10. 8.588 1,156.869 ↓ 2.5 16,789 1

Hash Left Join (cost=76,007.96..92,750.97 rows=6,751 width=397) (actual time=701.778..1,156.869 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))
11. 6.297 1,148.221 ↓ 2.5 16,789 1

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

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

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

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

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

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

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

15. 8.153 1,041.323 ↓ 2.5 16,789 1

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

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

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

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

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

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

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

19. 10.935 356.685 ↓ 2.0 16,789 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on tblpolicy p (cost=21.22..20,822.49 rows=23,982 width=116) (actual time=0.035..114.430 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.004 0.012 ↑ 1.0 18 1

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

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

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

39. 0.008 0.021 ↑ 1.0 45 1

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

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

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

41. 0.006 0.019 ↑ 1.0 27 1

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

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

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

43. 4.855 11.509 ↓ 1.0 38,419 1

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

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

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

45. 5.417 13.890 ↓ 1.0 38,419 1

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

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

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

47. 5.897 12.839 ↑ 1.0 42,666 1

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

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

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

49. 67.156 67.156 ↑ 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.004..0.004 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.040 ↓ 24.0 24 1

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

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

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

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

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

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

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

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

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

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

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

57. 0.019 0.060 ↑ 1.0 43 1

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

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

Seq Scan on tblgeneralagreement ega (cost=0.00..3.87 rows=43 width=32) (actual time=0.010..0.041 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

Unique (cost=1,355.98..1,355.99 rows=2 width=8) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,355.98..1,355.98 rows=2 width=8) (never executed)

  • Sort Key: tblemployeepermission_3.idpermittedagencyunit
71. 0.000 0.000 ↓ 0.0 0

Append (cost=0.28..1,355.97 rows=2 width=8) (never executed)

72. 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)
73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=200.83..1,347.64 rows=1 width=8) (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

Bitmap Heap Scan on tblagencyunit a_1 (cost=200.55..1,246.10 rows=9,324 width=20) (never executed)

  • Recheck Cond: (keyagencyunitgroup = ep_1.keyagencyunitgroup)
76. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tblagencyunit_keyagencyunitgroup_index (cost=0.00..198.22 rows=9,324 width=0) (never executed)

  • Index Cond: (keyagencyunitgroup = ep_1.keyagencyunitgroup)
77. 0.000 0.000 ↓ 0.0 0

Unique (cost=1,355.98..1,355.99 rows=2 width=8) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,355.98..1,355.98 rows=2 width=8) (never executed)

  • Sort Key: tblemployeepermission_4.idpermittedagencyunit
79. 0.000 0.000 ↓ 0.0 0

Append (cost=0.28..1,355.97 rows=2 width=8) (never executed)

80. 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)
81. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=200.83..1,347.64 rows=1 width=8) (never executed)

82. 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)
83. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tblagencyunit a_2 (cost=200.55..1,246.10 rows=9,324 width=20) (never executed)

  • Recheck Cond: (keyagencyunitgroup = ep_2.keyagencyunitgroup)
84. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tblagencyunit_keyagencyunitgroup_index (cost=0.00..198.22 rows=9,324 width=0) (never executed)

  • Index Cond: (keyagencyunitgroup = ep_2.keyagencyunitgroup)
85. 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)
86. 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
87. 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
88. 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)
89. 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)
Planning time : 9.948 ms
Execution time : 1,760.509 ms