explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZNmB : Optimization for: plan #Tu69

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 55.929 1,381.021 ↑ 47.2 5,485 1

Result (cost=256,156.72..339,661.96 rows=258,931 width=504) (actual time=1,324.744..1,381.021 rows=5,485 loops=1)

2.          

CTE datawindow

3. 27.904 356.208 ↑ 1.2 5,483 1

Hash Right Join (cost=68,302.01..70,463.04 rows=6,551 width=155) (actual time=323.197..356.208 rows=5,483 loops=1)

  • Hash Cond: (tblpolicyinstallmentcache.idpolicyversion = pv.id)
  • Filter: ((p_1.policystatus = 'BINDER'::text) OR ((p_1.policystatus = 'ACTIVE'::text) AND (tblpolicyinstallmentcache.paidvalue < tblpolicyinstallmentcache.value) AND (pv.enddate >= now())))
  • Rows Removed by Filter: 46421
4. 5.186 5.186 ↓ 1.0 70,611 1

Seq Scan on tblpolicyinstallmentcache (cost=0.00..1,703.10 rows=70,610 width=25) (actual time=0.009..5.186 rows=70,611 loops=1)

5. 15.975 323.118 ↑ 1.1 50,261 1

Hash (cost=67,597.31..67,597.31 rows=56,376 width=138) (actual time=323.118..323.118 rows=50,261 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 9229kB
6. 76.473 307.143 ↑ 1.1 50,261 1

Hash Join (cost=41,872.91..67,597.31 rows=56,376 width=138) (actual time=158.716..307.143 rows=50,261 loops=1)

  • Hash Cond: (pv.idpolicy = p_1.id)
7. 72.042 72.042 ↑ 1.0 441,552 1

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

  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 22210
8. 12.848 158.628 ↑ 1.1 50,261 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 7248kB
9. 145.780 145.780 ↑ 1.1 50,261 1

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

  • Filter: ((NOT test) AND (canceldate IS NULL) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 391281
10.          

CTE annualpremiums

11. 1.144 586.269 ↑ 1.1 8,380 1

Unique (cost=28,966.04..29,069.35 rows=9,200 width=40) (actual time=584.785..586.269 rows=8,380 loops=1)

12. 3.872 585.125 ↑ 1.6 8,534 1

Sort (cost=28,966.04..29,000.47 rows=13,775 width=40) (actual time=584.784..585.125 rows=8,534 loops=1)

  • Sort Key: w.idpolicyversion, rp.riskname, rp.validfrom DESC
  • Sort Method: quicksort Memory: 1137kB
13. 3.304 581.253 ↑ 1.6 8,534 1

Hash Join (cost=27,717.50..28,019.02 rows=13,775 width=40) (actual time=542.906..581.253 rows=8,534 loops=1)

  • Hash Cond: (w.idpolicyversion = rp.idpolicyversion)
14. 359.074 359.074 ↑ 1.2 5,483 1

CTE Scan on datawindow w (cost=0.00..131.02 rows=6,551 width=8) (actual time=323.200..359.074 rows=5,483 loops=1)

15. 121.617 218.875 ↓ 1.0 751,226 1

Hash (cost=18,327.22..18,327.22 rows=751,222 width=40) (actual time=218.875..218.875 rows=751,226 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 64249kB
16. 97.258 97.258 ↓ 1.0 751,226 1

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

17.          

CTE aggregatedannualpremiums

18. 3.428 590.796 ↓ 27.4 5,475 1

HashAggregate (cost=230.00..232.50 rows=200 width=40) (actual time=589.203..590.796 rows=5,475 loops=1)

  • Group Key: annualpremiums.idpolicyversion
19. 587.368 587.368 ↑ 1.1 8,380 1

CTE Scan on annualpremiums (cost=0.00..184.00 rows=9,200 width=26) (actual time=584.787..587.368 rows=8,380 loops=1)

20. 4.070 1,325.092 ↑ 47.2 5,485 1

Sort (cost=156,391.83..157,039.16 rows=258,931 width=480) (actual time=1,324.666..1,325.092 rows=5,485 loops=1)

  • Sort Key: (date_part('day'::text, (now() - (p.startdate)::timestamp with time zone))) DESC
  • Sort Method: quicksort Memory: 1934kB
21. 10.054 1,321.022 ↑ 47.2 5,485 1

Hash Left Join (cost=98,303.01..133,111.08 rows=258,931 width=480) (actual time=1,263.354..1,321.022 rows=5,485 loops=1)

  • Hash Cond: (p.id = n.idpolicy)
22. 1.661 1,283.074 ↑ 47.2 5,485 1

Hash Left Join (cost=95,455.80..111,167.71 rows=258,931 width=552) (actual time=1,235.393..1,283.074 rows=5,485 loops=1)

  • Hash Cond: (p.idproduceremployee = producer.id)
23. 1.707 1,268.510 ↑ 47.2 5,485 1

Hash Left Join (cost=93,622.43..108,654.57 rows=258,931 width=547) (actual time=1,222.442..1,268.510 rows=5,485 loops=1)

  • Hash Cond: (p.idinsurancetype = it_label.id)
24. 1.904 1,266.780 ↑ 47.2 5,485 1

Hash Left Join (cost=93,620.86..107,669.07 rows=258,931 width=579) (actual time=1,222.414..1,266.780 rows=5,485 loops=1)

  • Hash Cond: (pl.id = pl_label.id)
25. 2.515 1,264.842 ↑ 47.2 5,485 1

Hash Left Join (cost=93,619.46..106,607.38 rows=258,931 width=547) (actual time=1,222.370..1,264.842 rows=5,485 loops=1)

  • Hash Cond: (p.idpolicyversion = invoice.idpolicyversion)
26. 2.238 1,223.127 ↑ 47.2 5,483 1

Hash Join (cost=36,342.40..46,674.19 rows=258,931 width=547) (actual time=1,183.121..1,223.127 rows=5,483 loops=1)

  • Hash Cond: (p.idpolicyversion = aggregatedannualpremiums.idpolicyversion)
27. 3.180 603.489 ↑ 47.6 5,483 1

Hash Join (cost=34,594.02..35,159.19 rows=260,791 width=496) (actual time=565.713..603.489 rows=5,483 loops=1)

  • Hash Cond: (p.idpolicyversion = premium.idpolicyversion)
28. 4.138 35.041 ↓ 37.6 5,483 1

Nested Loop (cost=7.01..571.79 rows=146 width=456) (actual time=0.082..35.041 rows=5,483 loops=1)

29. 3.018 25.420 ↓ 37.6 5,483 1

Nested Loop (cost=6.72..444.52 rows=146 width=426) (actual time=0.076..25.420 rows=5,483 loops=1)

30. 3.846 16.919 ↓ 37.6 5,483 1

Nested Loop (cost=6.43..300.67 rows=146 width=413) (actual time=0.071..16.919 rows=5,483 loops=1)

31. 1.641 7.590 ↓ 37.6 5,483 1

Hash Join (cost=6.14..156.83 rows=146 width=397) (actual time=0.064..7.590 rows=5,483 loops=1)

  • Hash Cond: (ga.idproductline = pl.id)
32. 1.892 5.933 ↓ 37.6 5,483 1

Hash Join (cost=4.49..154.73 rows=146 width=372) (actual time=0.039..5.933 rows=5,483 loops=1)

  • Hash Cond: (p.idgeneralagreement = ga.id)
33. 2.663 4.023 ↓ 8.8 5,483 1

Hash Join (cost=1.43..150.01 rows=622 width=397) (actual time=0.016..4.023 rows=5,483 loops=1)

  • Hash Cond: (p.idinsurancecompany = cu.id)
34. 1.351 1.351 ↑ 1.2 5,483 1

CTE Scan on datawindow p (cost=0.00..131.02 rows=6,551 width=405) (actual time=0.001..1.351 rows=5,483 loops=1)

35. 0.003 0.009 ↑ 1.0 19 1

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

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

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

37. 0.008 0.018 ↑ 1.0 47 1

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

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

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

39. 0.006 0.016 ↑ 1.0 29 1

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

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

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

41. 5.483 5.483 ↑ 1.0 1 5,483

Index Scan using tblagencyunit_pkey on tblagencyunit agency (cost=0.29..0.99 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=5,483)

  • Index Cond: (id = p.idagency)
42. 5.483 5.483 ↑ 1.0 1 5,483

Index Scan using tblagencyunit_pkey on tblagencyunit division (cost=0.29..0.99 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=5,483)

  • Index Cond: (id = p.idagencyunit)
43. 5.483 5.483 ↑ 1.0 1 5,483

Index Scan using tblemployee_pkey on tblemployee employee (cost=0.29..0.87 rows=1 width=46) (actual time=0.001..0.001 rows=1 loops=5,483)

  • Index Cond: (id = p.idemployee)
44. 71.080 565.268 ↓ 1.3 463,762 1

Hash (cost=30,121.41..30,121.41 rows=357,248 width=40) (actual time=565.267..565.268 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 25848kB
45. 32.047 494.188 ↓ 1.3 463,762 1

Subquery Scan on premium (cost=22,083.33..30,121.41 rows=357,248 width=40) (actual time=274.507..494.188 rows=463,762 loops=1)

46. 406.304 462.141 ↓ 1.3 463,762 1

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

  • Group Key: tblriskpremium.idpolicyversion
47. 55.837 55.837 ↓ 1.0 751,226 1

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

48. 1.107 617.400 ↓ 27.5 5,475 1

Hash (cost=1,745.89..1,745.89 rows=199 width=75) (actual time=617.400..617.400 rows=5,475 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 552kB
49. 2.396 616.293 ↓ 27.5 5,475 1

Nested Loop (cost=0.85..1,745.89 rows=199 width=75) (actual time=589.233..616.293 rows=5,475 loops=1)

50. 0.264 602.947 ↓ 27.4 5,475 1

Nested Loop (cost=0.42..1,634.00 rows=200 width=83) (actual time=589.222..602.947 rows=5,475 loops=1)

51. 591.733 591.733 ↓ 27.4 5,475 1

CTE Scan on aggregatedannualpremiums (cost=0.00..4.00 rows=200 width=40) (actual time=589.205..591.733 rows=5,475 loops=1)

52. 10.950 10.950 ↑ 1.0 1 5,475

Index Scan using tblpolicyclient_idpolicyversion on tblpolicyclient pc_ubezpieczony (cost=0.42..8.14 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=5,475)

  • Index Cond: (idpolicyversion = aggregatedannualpremiums.idpolicyversion)
53. 10.950 10.950 ↑ 1.0 1 5,475

Index Only Scan using tblpolicyclientrole_role_unique on tblpolicyclientrole pcr_ubezpieczony (cost=0.42..0.56 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,475)

  • Index Cond: ((idpolicyclient = pc_ubezpieczony.id) AND (role = 'ubezpieczony'::text))
  • Heap Fetches: 5475
54. 5.422 39.200 ↓ 1.0 46,377 1

Hash (cost=56,697.99..56,697.99 rows=46,326 width=16) (actual time=39.193..39.200 rows=46,377 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2686kB
55. 30.374 33.778 ↓ 1.0 46,377 1

Bitmap Heap Scan on tblprint invoice (cost=879.45..56,697.99 rows=46,326 width=16) (actual time=5.859..33.778 rows=46,377 loops=1)

  • Recheck Cond: (documenttype = 'FAKTURA'::text)
  • Heap Blocks: exact=20753
56. 3.404 3.404 ↓ 1.0 46,377 1

Bitmap Index Scan on tblprint_documenttype_index (cost=0.00..867.87 rows=46,326 width=0) (actual time=3.404..3.404 rows=46,377 loops=1)

  • Index Cond: (documenttype = 'FAKTURA'::text)
57. 0.006 0.034 ↓ 26.0 26 1

Hash (cost=1.39..1.39 rows=1 width=64) (actual time=0.033..0.034 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
58. 0.028 0.028 ↓ 26.0 26 1

Seq Scan on tblproductlinelabel pl_label (cost=0.00..1.39 rows=1 width=64) (actual time=0.015..0.028 rows=26 loops=1)

  • Filter: ('en_US'::text ~~* (locale || '%'::text))
59. 0.003 0.023 ↓ 20.0 20 1

Hash (cost=1.55..1.55 rows=1 width=32) (actual time=0.023..0.023 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
60. 0.020 0.020 ↓ 20.0 20 1

Seq Scan on tblinsurancetypelabel it_label (cost=0.00..1.55 rows=1 width=32) (actual time=0.004..0.020 rows=20 loops=1)

  • Filter: ('en_US'::text ~~* (locale || '%'::text))
  • Rows Removed by Filter: 17
61. 6.073 12.903 ↑ 1.0 46,150 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3081kB
62. 6.830 6.830 ↑ 1.0 46,150 1

Seq Scan on tblemployee producer (cost=0.00..1,256.50 rows=46,150 width=21) (actual time=0.005..6.830 rows=46,150 loops=1)

63. 6.816 27.894 ↓ 1.0 59,264 1

Hash (cost=2,106.42..2,106.42 rows=59,263 width=40) (actual time=27.894..27.894 rows=59,264 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3716kB
64. 21.078 21.078 ↓ 1.0 59,264 1

Seq Scan on tblnumber n (cost=0.00..2,106.42 rows=59,263 width=40) (actual time=0.014..21.078 rows=59,264 loops=1)

Planning time : 4.543 ms
Execution time : 1,383.634 ms