explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tu69

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 54.150 2,625.299 ↓ 2,742.5 5,485 1

Result (cost=212,619.10..212,619.74 rows=2 width=394) (actual time=2,570.593..2,625.299 rows=5,485 loops=1)

2.          

CTE annualpremiums

3. 123.748 606.718 ↓ 2.1 742,875 1

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

4. 399.234 482.970 ↓ 1.0 751,226 1

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

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

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

6.          

CTE aggregatedannualpremiums

7. 408.692 1,129.603 ↓ 2,318.8 463,762 1

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

  • Group Key: annualpremiums.idpolicyversion
8. 720.911 720.911 ↓ 2.1 742,875 1

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

9. 6.387 2,571.149 ↓ 2,742.5 5,485 1

Sort (cost=106,408.95..106,408.96 rows=2 width=370) (actual time=2,570.502..2,571.149 rows=5,485 loops=1)

  • Sort Key: (date_part('day'::text, (now() - (pv.startdate)::timestamp with time zone))) DESC
  • Sort Method: quicksort Memory: 1934kB
10. 11.544 2,564.762 ↓ 2,742.5 5,485 1

Nested Loop Left Join (cost=97,019.29..106,408.94 rows=2 width=370) (actual time=2,027.777..2,564.762 rows=5,485 loops=1)

11. 6.176 2,536.763 ↓ 2,742.5 5,485 1

Nested Loop Left Join (cost=97,019.00..106,408.03 rows=2 width=362) (actual time=2,027.745..2,536.763 rows=5,485 loops=1)

12. 3.709 2,525.102 ↓ 2,742.5 5,485 1

Nested Loop Left Join (cost=97,018.71..106,407.28 rows=2 width=357) (actual time=2,027.737..2,525.102 rows=5,485 loops=1)

13. 1.193 2,504.938 ↓ 2,742.5 5,485 1

Nested Loop Left Join (cost=97,018.57..106,406.94 rows=2 width=362) (actual time=2,027.725..2,504.938 rows=5,485 loops=1)

14. 0.971 2,487.290 ↓ 2,742.5 5,485 1

Nested Loop (cost=97,018.43..106,405.77 rows=2 width=330) (actual time=2,027.711..2,487.290 rows=5,485 loops=1)

15. 2.168 2,464.379 ↓ 2,742.5 5,485 1

Nested Loop (cost=97,018.00..106,404.65 rows=2 width=338) (actual time=2,027.682..2,464.379 rows=5,485 loops=1)

16. 0.460 2,445.756 ↓ 2,742.5 5,485 1

Nested Loop Left Join (cost=97,017.58..106,403.47 rows=2 width=327) (actual time=2,027.674..2,445.756 rows=5,485 loops=1)

17. 6.458 2,417.881 ↓ 2,741.5 5,483 1

Nested Loop Left Join (cost=97,017.15..106,401.32 rows=2 width=319) (actual time=2,027.660..2,417.881 rows=5,483 loops=1)

  • Filter: ((p.policystatus = 'BINDER'::text) OR ((p.policystatus = 'ACTIVE'::text) AND (tblpolicyinstallmentcache.paidvalue < tblpolicyinstallmentcache.value) AND (pv.enddate >= now())))
  • Rows Removed by Filter: 46421
18. 88.185 2,310.901 ↓ 2,645.3 50,261 1

Hash Join (cost=97,016.86..106,394.80 rows=19 width=302) (actual time=2,027.440..2,310.901 rows=50,261 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = pv.id)
19. 441.478 503.746 ↓ 1.3 463,762 1

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

  • Group Key: tblriskpremium.idpolicyversion
20. 62.268 62.268 ↓ 1.0 751,226 1

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

21. 23.181 1,718.970 ↓ 2,094.2 50,261 1

Hash (cost=74,933.23..74,933.23 rows=24 width=262) (actual time=1,718.970..1,718.970 rows=50,261 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11134kB
22. 82.253 1,695.789 ↓ 2,094.2 50,261 1

Hash Join (cost=74,928.24..74,933.23 rows=24 width=262) (actual time=1,344.865..1,695.789 rows=50,261 loops=1)

  • Hash Cond: (aggregatedannualpremiums.idpolicyversion = pv.id)
23. 1,211.486 1,211.486 ↓ 2,318.8 463,762 1

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

24. 18.633 402.050 ↑ 1.1 50,261 1

Hash (cost=74,223.54..74,223.54 rows=56,376 width=222) (actual time=402.050..402.050 rows=50,261 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 10316kB
25. 15.459 383.417 ↑ 1.1 50,261 1

Hash Join (cost=47,543.22..74,223.54 rows=56,376 width=222) (actual time=180.387..383.417 rows=50,261 loops=1)

  • Hash Cond: (p.idemployee = employee.id)
26. 12.607 353.371 ↑ 1.1 50,261 1

Hash Join (cost=45,709.84..72,242.17 rows=56,376 width=192) (actual time=165.749..353.371 rows=50,261 loops=1)

  • Hash Cond: (p.idagencyunit = division.id)
27. 11.665 327.324 ↑ 1.1 50,261 1

Hash Join (cost=43,794.45..70,178.78 rows=56,376 width=179) (actual time=152.258..327.324 rows=50,261 loops=1)

  • Hash Cond: (p.idagency = agency.id)
28. 10.799 306.126 ↑ 1.1 50,261 1

Hash Join (cost=41,879.05..68,115.38 rows=56,376 width=163) (actual time=142.676..306.126 rows=50,261 loops=1)

  • Hash Cond: (ga.idproductline = pl.id)
29. 11.556 295.315 ↑ 1.1 50,261 1

Hash Join (cost=41,877.40..67,944.12 rows=56,376 width=138) (actual time=142.656..295.315 rows=50,261 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
30. 9.126 283.740 ↑ 1.1 50,261 1

Hash Join (cost=41,874.34..67,779.73 rows=56,376 width=151) (actual time=142.627..283.740 rows=50,261 loops=1)

  • Hash Cond: (p.idinsurancecompany = cu.id)
31. 66.318 274.605 ↑ 1.1 50,261 1

Hash Join (cost=41,872.91..67,597.31 rows=56,376 width=127) (actual time=142.613..274.605 rows=50,261 loops=1)

  • Hash Cond: (pv.idpolicy = p.id)
32. 65.736 65.736 ↑ 1.0 441,552 1

Seq Scan on tblpolicyversion pv (cost=0.00..24,562.00 rows=442,814 width=46) (actual time=0.005..65.736 rows=441,552 loops=1)

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 22210
33. 10.848 142.551 ↑ 1.1 50,261 1

Hash (cost=41,170.24..41,170.24 rows=56,214 width=100) (actual time=142.550..142.551 rows=50,261 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 7267kB
34. 131.703 131.703 ↑ 1.1 50,261 1

Seq Scan on tblpolicy p (cost=0.00..41,170.24 rows=56,214 width=100) (actual time=0.007..131.703 rows=50,261 loops=1)

  • Filter: ((NOT test) AND (canceldate IS NULL) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 391281
35. 0.004 0.009 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=40) (actual time=0.008..0.009 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.005 0.005 ↑ 1.0 19 1

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

37. 0.008 0.019 ↑ 1.0 47 1

Hash (cost=2.47..2.47 rows=47 width=23) (actual time=0.019..0.019 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
38. 0.011 0.011 ↑ 1.0 47 1

Seq Scan on tblgeneralagreement ga (cost=0.00..2.47 rows=47 width=23) (actual time=0.005..0.011 rows=47 loops=1)

39. 0.005 0.012 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=32) (actual time=0.012..0.012 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.007 0.007 ↑ 1.0 29 1

Seq Scan on tblproductline pl (cost=0.00..1.29 rows=29 width=32) (actual time=0.004..0.007 rows=29 loops=1)

41. 4.526 9.533 ↑ 1.0 40,862 1

Hash (cost=1,404.62..1,404.62 rows=40,862 width=32) (actual time=9.533..9.533 rows=40,862 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2631kB
42. 5.007 5.007 ↑ 1.0 40,862 1

Seq Scan on tblagencyunit agency (cost=0.00..1,404.62 rows=40,862 width=32) (actual time=0.004..5.007 rows=40,862 loops=1)

43. 5.091 13.440 ↑ 1.0 40,862 1

Hash (cost=1,404.62..1,404.62 rows=40,862 width=29) (actual time=13.440..13.440 rows=40,862 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3096kB
44. 8.349 8.349 ↑ 1.0 40,862 1

Seq Scan on tblagencyunit division (cost=0.00..1,404.62 rows=40,862 width=29) (actual time=0.006..8.349 rows=40,862 loops=1)

45. 7.020 14.587 ↑ 1.0 46,150 1

Hash (cost=1,256.50..1,256.50 rows=46,150 width=46) (actual time=14.586..14.587 rows=46,150 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4231kB
46. 7.567 7.567 ↑ 1.0 46,150 1

Seq Scan on tblemployee employee (cost=0.00..1,256.50 rows=46,150 width=46) (actual time=0.014..7.567 rows=46,150 loops=1)

47. 100.522 100.522 ↑ 1.0 1 50,261

Index Scan using index_tblpolicyinstallmentcache_idpolicyversion on tblpolicyinstallmentcache (cost=0.29..0.32 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=50,261)

  • Index Cond: (pv.id = idpolicyversion)
48. 27.415 27.415 ↑ 1.0 1 5,483

Index Scan using tblprint_idpolicyversion on tblprint invoice (cost=0.43..1.07 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=5,483)

  • Index Cond: (pv.id = idpolicyversion)
  • Filter: (documenttype = 'FAKTURA'::text)
  • Rows Removed by Filter: 4
49. 16.455 16.455 ↑ 1.0 1 5,485

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..0.58 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=5,485)

  • Index Cond: (idpolicyversion = pv.id)
50. 21.940 21.940 ↑ 1.0 1 5,485

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=5,485)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 5485
51. 16.455 16.455 ↑ 1.0 1 5,485

Index Scan using tblproductlinelabel_pkey on tblproductlinelabel pl_label (cost=0.14..0.57 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=5,485)

  • Index Cond: (id = pl.id)
  • Filter: ('en_US'::text ~~* (locale || '%'::text))
52. 16.455 16.455 ↑ 1.0 1 5,485

Index Only Scan using tblinsurancetypelabel_pkey on tblinsurancetypelabel it_label (cost=0.14..0.16 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=5,485)

  • Index Cond: (id = p.idinsurancetype)
  • Filter: ('en_US'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 1
  • Heap Fetches: 13677
53. 5.485 5.485 ↑ 1.0 1 5,485

Index Scan using tblemployee_pkey on tblemployee producer (cost=0.29..0.37 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=5,485)

  • Index Cond: (id = p.idproduceremployee)
54. 16.455 16.455 ↑ 1.0 1 5,485

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.39 rows=1 width=40) (actual time=0.002..0.003 rows=1 loops=5,485)

  • Index Cond: (p.id = idpolicy)
Planning time : 13.479 ms
Execution time : 2,637.970 ms