explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uZkd

Settings
# exclusive inclusive rows x rows loops node
1. 324.855 12,529.003 ↓ 12,159.0 12,159 1

Result (cost=49,021.37..49,022.05 rows=1 width=460) (actual time=12,201.027..12,529.003 rows=12,159 loops=1)

2. 26.552 12,204.148 ↓ 12,159.0 12,159 1

Sort (cost=49,021.37..49,021.38 rows=1 width=376) (actual time=12,200.910..12,204.148 rows=12,159 loops=1)

  • Sort Key: pv.createdtime DESC
  • Sort Method: quicksort Memory: 6654kB
3. 19.836 12,177.596 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,569.19..49,021.36 rows=1 width=376) (actual time=5,745.691..12,177.596 rows=12,159 loops=1)

4. 14.718 12,133.442 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,568.90..49,021.03 rows=1 width=579) (actual time=5,745.665..12,133.442 rows=12,159 loops=1)

5. 11.099 12,106.565 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,568.62..49,020.73 rows=1 width=561) (actual time=5,745.650..12,106.565 rows=12,159 loops=1)

6. 10.945 12,022.512 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,568.33..49,020.33 rows=1 width=561) (actual time=5,745.641..12,022.512 rows=12,159 loops=1)

7. 9.097 11,902.136 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,568.04..49,020.01 rows=1 width=553) (actual time=5,745.634..11,902.136 rows=12,159 loops=1)

8. 12.449 10,032.712 ↓ 12,159.0 12,159 1

Nested Loop Left Join (cost=42,567.19..49,018.83 rows=1 width=520) (actual time=5,742.182..10,032.712 rows=12,159 loops=1)

9. 0.000 9,983.786 ↓ 12,159.0 12,159 1

Nested Loop (cost=42,566.62..49,011.68 rows=1 width=520) (actual time=5,742.165..9,983.786 rows=12,159 loops=1)

10. 49.379 9,604.035 ↓ 1,454.6 196,376 1

Hash Left Join (cost=42,566.33..48,969.19 rows=135 width=500) (actual time=5,736.482..9,604.035 rows=196,376 loops=1)

  • Hash Cond: (p.keyintegration = i.key)
11. 131.894 9,554.631 ↓ 1,454.6 196,376 1

Nested Loop Left Join (cost=42,564.48..48,966.98 rows=135 width=468) (actual time=5,736.449..9,554.631 rows=196,376 loops=1)

  • Join Filter: (p.fallbacklocale ~~* (cd_label_fallback.locale || '%'::text))
  • Rows Removed by Join Filter: 157844
12. 17.599 9,029.985 ↓ 1,454.6 196,376 1

Nested Loop Left Join (cost=42,564.05..48,875.03 rows=135 width=382) (actual time=5,736.435..9,029.985 rows=196,376 loops=1)

13. 58.566 6,459.498 ↓ 1,454.6 196,376 1

Hash Left Join (cost=42,563.63..48,784.42 rows=135 width=293) (actual time=5,733.896..6,459.498 rows=196,376 loops=1)

  • Hash Cond: (p.idinsurancetype = it_label.id)
14. 61.427 6,400.904 ↓ 1,454.6 196,376 1

Hash Left Join (cost=42,562.23..48,782.46 rows=135 width=261) (actual time=5,733.862..6,400.904 rows=196,376 loops=1)

  • Hash Cond: (pl.id = pl_label.id)
15. 238.101 6,339.427 ↓ 1,454.6 196,376 1

Hash Join (cost=42,560.73..48,780.42 rows=135 width=229) (actual time=5,733.798..6,339.427 rows=196,376 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = od.id)
16. 627.626 682.844 ↓ 1.9 352,353 1

HashAggregate (cost=16,940.51..20,625.45 rows=184,247 width=62) (actual time=315.292..682.844 rows=352,353 loops=1)

  • Group Key: tblriskpremium.idpolicyversion
17. 55.218 55.218 ↓ 1.0 543,574 1

Seq Scan on tblriskpremium (cost=0.00..12,863.72 rows=543,572 width=18) (actual time=0.012..55.218 rows=543,574 loops=1)

18. 87.587 5,418.482 ↓ 953.7 196,456 1

Hash (cost=25,617.65..25,617.65 rows=206 width=209) (actual time=5,418.482..5,418.482 rows=196,456 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 40110kB
19. 440.309 5,330.895 ↓ 953.7 196,456 1

Nested Loop (cost=24,233.41..25,617.65 rows=206 width=209) (actual time=4,767.666..5,330.895 rows=196,456 loops=1)

  • Join Filter: (ga.idproductline = pl.id)
  • Rows Removed by Join Filter: 3339752
20. 0.076 0.076 ↑ 1.0 18 1

Index Only Scan using tblproductline_pkey on tblproductline pl (cost=0.14..12.41 rows=18 width=32) (actual time=0.010..0.076 rows=18 loops=1)

  • Heap Fetches: 18
21. 248.717 4,890.510 ↓ 953.7 196,456 18

Materialize (cost=24,233.28..25,550.14 rows=206 width=183) (actual time=24.442..271.695 rows=196,456 loops=18)

22. 47.900 4,641.793 ↓ 953.7 196,456 1

Hash Join (cost=24,233.28..25,549.11 rows=206 width=183) (actual time=439.936..4,641.793 rows=196,456 loops=1)

  • Hash Cond: (p.idinsurancecompany = cu.id)
23. 64.305 4,593.883 ↓ 953.7 196,456 1

Hash Join (cost=24,232.23..25,546.37 rows=206 width=159) (actual time=439.917..4,593.883 rows=196,456 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
24. 129.638 4,529.550 ↓ 953.7 196,456 1

Nested Loop (cost=24,227.48..25,541.05 rows=206 width=142) (actual time=439.877..4,529.550 rows=196,456 loops=1)

25. 112.058 3,221.164 ↓ 719.6 196,458 1

Nested Loop (cost=24,227.05..25,280.82 rows=273 width=86) (actual time=438.953..3,221.164 rows=196,458 loops=1)

  • Join Filter: (od.id = pv.id)
26. 137.181 1,276.247 ↓ 434.2 203,651 1

Nested Loop (cost=24,226.63..24,971.79 rows=469 width=50) (actual time=437.749..1,276.247 rows=203,651 loops=1)

27. 143.717 517.256 ↓ 147.3 207,270 1

HashAggregate (cost=24,226.21..24,240.28 rows=1,407 width=8) (actual time=437.724..517.256 rows=207,270 loops=1)

  • Group Key: od_1.id
28. 131.761 373.539 ↓ 147.3 207,270 1

Hash Join (cost=15,507.93..24,222.69 rows=1,407 width=8) (actual time=210.547..373.539 rows=207,270 loops=1)

  • Hash Cond: ((od_1.offernumber = tblofferdata.offernumber) AND ((od_1.offerversion)::bigint = (max((tblofferdata.offerversion)::bigint))))
29. 31.457 31.457 ↓ 1.0 281,405 1

Seq Scan on tblofferdata od_1 (cost=0.00..7,167.04 rows=281,404 width=21) (actual time=0.008..31.457 rows=281,405 loops=1)

30. 36.360 210.321 ↓ 1.3 207,251 1

Hash (cost=13,139.28..13,139.28 rows=157,910 width=19) (actual time=210.320..210.321 rows=207,251 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13383kB
31. 143.831 173.961 ↓ 1.3 207,251 1

HashAggregate (cost=9,981.08..11,560.18 rows=157,910 width=19) (actual time=123.171..173.961 rows=207,251 loops=1)

  • Group Key: tblofferdata.offernumber
32. 30.130 30.130 ↓ 1.0 281,405 1

Seq Scan on tblofferdata (cost=0.00..7,167.04 rows=281,404 width=13) (actual time=0.006..30.130 rows=281,405 loops=1)

33. 621.810 621.810 ↑ 1.0 1 207,270

Index Scan using tblofferdata_pkey on tblofferdata od (cost=0.42..0.52 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=207,270)

  • Index Cond: (id = od_1.id)
  • Filter: ((validstartdate + validdays) < now())
  • Rows Removed by Filter: 0
34. 1,832.859 1,832.859 ↑ 1.0 1 203,651

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..0.65 rows=1 width=36) (actual time=0.009..0.009 rows=1 loops=203,651)

  • Index Cond: (id = od_1.id)
  • Filter: ((canceldate IS NULL) AND (policyversionstatus = 'CURRENT'::text))
  • Rows Removed by Filter: 0
35. 1,178.748 1,178.748 ↑ 1.0 1 196,458

Index Scan using pk_tblinsurance on tblpolicy p (cost=0.42..0.95 rows=1 width=80) (actual time=0.006..0.006 rows=1 loops=196,458)

  • Index Cond: (id = pv.idpolicy)
  • Filter: (policytype = 'OFFER'::text)
  • Rows Removed by Filter: 0
36. 0.010 0.028 ↑ 1.5 53 1

Hash (cost=3.78..3.78 rows=78 width=17) (actual time=0.028..0.028 rows=53 loops=1)

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

Seq Scan on tblgeneralagreement ga (cost=0.00..3.78 rows=78 width=17) (actual time=0.005..0.018 rows=53 loops=1)

38. 0.002 0.010 ↓ 1.5 3 1

Hash (cost=1.02..1.02 rows=2 width=40) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.008 0.008 ↓ 1.5 3 1

Seq Scan on tblinsurancecompanyunit cu (cost=0.00..1.02 rows=2 width=40) (actual time=0.007..0.008 rows=3 loops=1)

40. 0.009 0.050 ↓ 16.0 16 1

Hash (cost=1.48..1.48 rows=1 width=64) (actual time=0.050..0.050 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.041 0.041 ↓ 16.0 16 1

Seq Scan on tblproductlinelabel pl_label (cost=0.00..1.48 rows=1 width=64) (actual time=0.030..0.041 rows=16 loops=1)

  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 16
42. 0.010 0.028 ↓ 9.0 9 1

Hash (cost=1.39..1.39 rows=1 width=64) (actual time=0.028..0.028 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.018 0.018 ↓ 9.0 9 1

Seq Scan on tblinsurancetypelabel it_label (cost=0.00..1.39 rows=1 width=64) (actual time=0.008..0.018 rows=9 loops=1)

  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 17
44. 2,552.888 2,552.888 ↑ 1.0 1 196,376

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label (cost=0.42..0.66 rows=1 width=97) (actual time=0.013..0.013 rows=1 loops=196,376)

  • Index Cond: (idpolicyversion = pv.id)
  • Filter: ('pl_PL'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
45. 392.752 392.752 ↑ 1.0 2 196,376

Index Scan using tblpolicycoveragedescription_unique on tblpolicycoveragedescription cd_label_fallback (cost=0.42..0.65 rows=2 width=100) (actual time=0.001..0.002 rows=2 loops=196,376)

  • Index Cond: (idpolicyversion = pv.id)
46. 0.015 0.025 ↓ 1.0 39 1

Hash (cost=1.38..1.38 rows=38 width=64) (actual time=0.025..0.025 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
47. 0.010 0.010 ↓ 1.0 39 1

Seq Scan on tblintegration i (cost=0.00..1.38 rows=38 width=64) (actual time=0.007..0.010 rows=39 loops=1)

48. 392.752 392.752 ↓ 0.0 0 196,376

Index Scan using tblagencyunit_pkey on tblagencyunit agency (cost=0.28..0.31 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=196,376)

  • Index Cond: (id = p.idagency)
  • Filter: ((ltree2text(subltree(path, 0, 1)))::integer = 2375)
  • Rows Removed by Filter: 1
49. 12.159 36.477 ↑ 1.0 1 12,159

Nested Loop (cost=0.57..7.15 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=12,159)

50. 12.159 12.159 ↑ 1.0 1 12,159

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

  • Index Cond: (id = p.idagencyunit)
  • Filter: (nlevel(path) = 2)
51. 12.159 12.159 ↑ 1.0 1 12,159

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.29..6.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=12,159)

  • Index Cond: (id = (ltree2text(subpath(u2.path, 0, 1)))::bigint)
  • Filter: (nlevel(path) = 1)
52. 12.731 1,860.327 ↑ 1.0 1 12,159

Nested Loop (cost=0.85..1.17 rows=1 width=41) (actual time=0.152..0.153 rows=1 loops=12,159)

53. 1,021.356 1,021.356 ↑ 1.0 1 12,159

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.63 rows=1 width=49) (actual time=0.083..0.084 rows=1 loops=12,159)

  • Index Cond: (pv.id = idpolicyversion)
54. 826.240 826.240 ↑ 1.0 1 12,910

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr_ubezpieczony (cost=0.42..0.54 rows=1 width=8) (actual time=0.064..0.064 rows=1 loops=12,910)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 12159
55. 109.431 109.431 ↓ 0.0 0 12,159

Index Scan using tblclient_pkey on tblclient c (cost=0.29..0.32 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=12,159)

  • Index Cond: (pc_ubezpieczony.idclient = id)
56. 72.954 72.954 ↓ 0.0 0 12,159

Index Only Scan using tblmetaclient_pkey on tblmetaclient mc (cost=0.29..0.40 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=12,159)

  • Index Cond: (id = c.idmetaclient)
  • Heap Fetches: 1585
57. 12.159 12.159 ↑ 1.0 1 12,159

Index Scan using tblagencyunit_pkey on tblagencyunit division (cost=0.28..0.30 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=12,159)

  • Index Cond: (id = p.idagencyunit)
58. 24.318 24.318 ↑ 1.0 1 12,159

Index Scan using tblemployee_pkey on tblemployee employee (cost=0.29..0.31 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=12,159)

  • Index Cond: (id = p.idemployee)
Planning time : 12.131 ms
Execution time : 12,541.334 ms