explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OSl7

Settings
# exclusive inclusive rows x rows loops node
1. 1.033 124,693.023 ↓ 220.5 441 1

Sort (cost=274,543.12..274,543.12 rows=2 width=328) (actual time=124,692.993..124,693.023 rows=441 loops=1)

  • Sort Key: ((""*SELECT* 1"".orderdate)::date) DESC
  • Sort Method: quicksort Memory: 142kB
2.          

CTE acc_entry

3. 18.858 18.858 ↑ 1.0 48,392 1

Seq Scan on tblaccountingentry (cost=0.00..3,774.51 rows=48,493 width=81) (actual time=0.013..18.858 rows=48,392 loops=1)

  • Filter: (type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[]))
  • Rows Removed by Filter: 68751
4.          

CTE annualpremiums

5. 124.766 621.371 ↓ 2.1 742,875 1

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

6. 406.214 496.605 ↓ 1.0 751,226 1

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

  • Sort Key: tblriskpremium_2.idpolicyversion, tblriskpremium_2.riskname, tblriskpremium_2.validfrom DESC
  • Sort Method: quicksort Memory: 89099kB
7. 90.391 90.391 ↓ 1.0 751,226 1

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

8.          

CTE aggregatedannualpremiums

9. 384.835 1,107.304 ↓ 2,318.8 463,762 1

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

  • Group Key: annualpremiums.idpolicyversion
10. 722.469 722.469 ↓ 2.1 742,875 1

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

11. 5.312 124,691.990 ↓ 220.5 441 1

Result (cost=59,041.39..164,558.45 rows=2 width=328) (actual time=103,460.140..124,691.990 rows=441 loops=1)

12. 0.244 124,686.678 ↓ 220.5 441 1

Append (cost=59,041.39..164,558.30 rows=2 width=217) (actual time=103,460.120..124,686.678 rows=441 loops=1)

13. 0.001 420.233 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 1" (cost=59,041.39..82,247.34 rows=1 width=217) (actual time=420.233..420.233 rows=0 loops=1)

14. 0.000 420.232 ↓ 0.0 0 1

Nested Loop (cost=59,041.39..82,247.33 rows=1 width=217) (actual time=420.232..420.232 rows=0 loops=1)

  • Join Filter: (pv_1.id = aap.idpolicyversion)
15. 0.001 420.232 ↓ 0.0 0 1

Nested Loop (cost=59,041.39..82,240.83 rows=1 width=228) (actual time=420.232..420.232 rows=0 loops=1)

16. 0.000 420.231 ↓ 0.0 0 1

Nested Loop Left Join (cost=59,040.97..82,240.21 rows=1 width=216) (actual time=420.231..420.231 rows=0 loops=1)

17. 0.009 420.231 ↓ 0.0 0 1

Hash Join (cost=59,039.40..82,237.80 rows=1 width=211) (actual time=420.231..420.231 rows=0 loops=1)

  • Hash Cond: (pc.idpolicyversion = pv_1.id)
18. 0.421 201.405 ↑ 460,452.0 1 1

Hash Join (cost=24,531.60..46,003.30 rows=460,452 width=42) (actual time=201.404..201.405 rows=1 loops=1)

  • Hash Cond: (pcr.idpolicyclient = pc.id)
19. 0.014 0.014 ↑ 460,452.0 1 1

Seq Scan on tblpolicyclientrole pcr (cost=0.00..20,263.00 rows=460,452 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: (role = 'ubezpieczony'::text)
20. 93.202 200.970 ↓ 1.0 463,762 1

Hash (cost=18,734.60..18,734.60 rows=463,760 width=50) (actual time=200.970..200.970 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 40011kB
21. 107.768 107.768 ↓ 1.0 463,762 1

Seq Scan on tblpolicyclient pc (cost=0.00..18,734.60 rows=463,760 width=50) (actual time=0.009..107.768 rows=463,762 loops=1)

22. 0.000 218.817 ↓ 0.0 0 1

Hash (cost=34,507.79..34,507.79 rows=1 width=169) (actual time=218.817..218.817 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 0.004 218.817 ↓ 0.0 0 1

Hash Join (cost=24,236.90..34,507.79 rows=1 width=169) (actual time=218.817..218.817 rows=0 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = pv_1.id)
24. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=22,083.33..27,442.05 rows=357,248 width=62) (never executed)

  • Group Key: tblriskpremium.idpolicyversion
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblriskpremium (cost=0.00..18,327.22 rows=751,222 width=13) (never executed)

26. 0.000 218.813 ↓ 0.0 0 1

Hash (cost=2,153.55..2,153.55 rows=1 width=143) (actual time=218.813..218.813 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.001 218.813 ↓ 0.0 0 1

Nested Loop (cost=993.55..2,153.55 rows=1 width=143) (actual time=218.813..218.813 rows=0 loops=1)

28. 0.000 218.812 ↓ 0.0 0 1

Nested Loop (cost=993.26..2,153.24 rows=1 width=136) (actual time=218.812..218.812 rows=0 loops=1)

29. 0.001 218.812 ↓ 0.0 0 1

Nested Loop (cost=992.84..2,152.59 rows=1 width=136) (actual time=218.812..218.812 rows=0 loops=1)

30. 0.002 218.811 ↓ 0.0 0 1

Hash Join (cost=992.55..2,144.27 rows=1 width=134) (actual time=218.811..218.811 rows=0 loops=1)

  • Hash Cond: (acc_entry.idpayment = paym.id)
31. 0.015 0.015 ↑ 48,493.0 1 1

CTE Scan on acc_entry (cost=0.00..969.86 rows=48,493 width=40) (actual time=0.015..0.015 rows=1 loops=1)

32. 0.001 218.794 ↓ 0.0 0 1

Hash (cost=992.54..992.54 rows=1 width=110) (actual time=218.794..218.794 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
33. 9.036 218.793 ↓ 0.0 0 1

Nested Loop (cost=11.77..992.54 rows=1 width=110) (actual time=218.793..218.793 rows=0 loops=1)

34. 0.000 192.663 ↓ 17,094.0 17,094 1

Nested Loop (cost=11.48..992.20 rows=1 width=78) (actual time=1.023..192.663 rows=17,094 loops=1)

35. 8.515 91.771 ↓ 10,094.0 100,940 1

Nested Loop (cost=11.19..988.94 rows=10 width=55) (actual time=0.911..91.771 rows=100,940 loops=1)

36. 5.437 6.216 ↓ 214.0 214 1

Bitmap Heap Scan on tblagencyunit u2 (cost=5.82..514.29 rows=1 width=26) (actual time=0.897..6.216 rows=214 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 23)
  • Rows Removed by Filter: 21931
  • Heap Blocks: exact=981
37. 0.779 0.779 ↓ 108.6 22,145 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..5.82 rows=204 width=0) (actual time=0.779..0.779 rows=22,145 loops=1)

  • Index Cond: (nlevel(path) = 2)
38. 71.262 77.040 ↓ 4.1 472 214

Bitmap Heap Scan on tblpolicy p (cost=5.37..473.48 rows=116 width=45) (actual time=0.040..0.360 rows=472 loops=214)

  • Recheck Cond: (idagencyunit = u2.id)
  • Filter: (NOT test)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=31642
39. 5.778 5.778 ↓ 3.9 472 214

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..5.34 rows=122 width=0) (actual time=0.027..0.027 rows=472 loops=214)

  • Index Cond: (idagencyunit = u2.id)
40. 100.940 100.940 ↓ 0.0 0 100,940

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.32 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=100,940)

  • Index Cond: (idpolicy = p.id)
41. 17.094 17.094 ↓ 0.0 0 17,094

Index Scan using tblpayment_idpolicy on tblpayment paym (cost=0.29..0.33 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=17,094)

  • Index Cond: (idpolicy = p.id)
  • Filter: ((createdtime < '2017-04-01'::date) AND (date_trunc('month'::text, paymentdate) = '2019-08-01 00:00:00+00'::timestamp with time zone) AND ((canceldate IS NULL) OR (date_trunc('month'::text, createdtime) <> date_trunc('month'::text, canceldate))))
  • Rows Removed by Filter: 1
42. 0.000 0.000 ↓ 0.0 0

Index Scan using tblagencyunit_pkey on tblagencyunit u1 (cost=0.29..8.31 rows=1 width=28) (never executed)

  • Index Cond: (id = 23)
  • Filter: (nlevel(path) = 1)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_1 (cost=0.42..0.63 rows=1 width=32) (never executed)

  • Index Cond: (idpolicy = p.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployee_pkey on tblemployee e (cost=0.29..0.32 rows=1 width=23) (never executed)

  • Index Cond: (id = p.idemployee)
45. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.57..2.40 rows=1 width=13) (never executed)

  • Hash Cond: (tc.commissiontype = tblcommissiontypedef.key)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using tbltechcommission_idpolicyversion on tbltechcommission tc (cost=0.43..1.21 rows=13 width=21) (never executed)

  • Index Cond: (pv_1.id = idpolicyversion)
47. 0.000 0.000 ↓ 0.0 0

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

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on tblcommissiontypedef (cost=0.00..1.12 rows=1 width=32) (never executed)

  • Filter: (commissiongroup = 'RB'::text)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv (cost=0.42..0.62 rows=1 width=12) (never executed)

  • Index Cond: (id = pv_1.id)
50. 0.000 0.000 ↓ 0.0 0

CTE Scan on aggregatedannualpremiums aap (cost=0.00..4.00 rows=200 width=40) (never executed)

51. 0.373 124,266.201 ↓ 441.0 441 1

Subquery Scan on "*SELECT* 2" (cost=59,105.00..82,310.95 rows=1 width=217) (actual time=103,039.885..124,266.201 rows=441 loops=1)

52. 10,191.839 124,265.828 ↓ 441.0 441 1

Nested Loop (cost=59,105.00..82,310.94 rows=1 width=217) (actual time=103,039.883..124,265.828 rows=441 loops=1)

  • Join Filter: (pv_3.id = aap_1.idpolicyversion)
  • Rows Removed by Join Filter: 204518601
53. 1.039 101,979.564 ↓ 441.0 441 1

Nested Loop (cost=59,105.00..82,304.44 rows=1 width=236) (actual time=101,853.981..101,979.564 rows=441 loops=1)

54. 1.304 101,975.879 ↓ 441.0 441 1

Nested Loop Left Join (cost=59,104.58..82,303.82 rows=1 width=224) (actual time=101,853.972..101,975.879 rows=441 loops=1)

55. 32.508 101,966.196 ↓ 441.0 441 1

Hash Join (cost=59,103.01..82,301.41 rows=1 width=219) (actual time=101,853.928..101,966.196 rows=441 loops=1)

  • Hash Cond: (pc_1.idpolicyversion = pv_3.id)
56. 145.319 412.811 ↓ 1.0 463,762 1

Hash Join (cost=24,531.60..46,003.30 rows=460,452 width=42) (actual time=181.761..412.811 rows=463,762 loops=1)

  • Hash Cond: (pcr_1.idpolicyclient = pc_1.id)
57. 86.095 86.095 ↓ 1.0 463,762 1

Seq Scan on tblpolicyclientrole pcr_1 (cost=0.00..20,263.00 rows=460,452 width=8) (actual time=0.008..86.095 rows=463,762 loops=1)

  • Filter: (role = 'ubezpieczony'::text)
  • Rows Removed by Filter: 463762
58. 83.800 181.397 ↓ 1.0 463,762 1

Hash (cost=18,734.60..18,734.60 rows=463,760 width=50) (actual time=181.396..181.397 rows=463,762 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 40011kB
59. 97.597 97.597 ↓ 1.0 463,762 1

Seq Scan on tblpolicyclient pc_1 (cost=0.00..18,734.60 rows=463,760 width=50) (actual time=0.006..97.597 rows=463,762 loops=1)

60. 0.230 101,520.877 ↓ 441.0 441 1

Hash (cost=34,571.40..34,571.40 rows=1 width=177) (actual time=101,520.877..101,520.877 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
61. 29.047 101,520.647 ↓ 441.0 441 1

Hash Join (cost=24,300.51..34,571.40 rows=1 width=177) (actual time=101,294.269..101,520.647 rows=441 loops=1)

  • Hash Cond: (tblriskpremium_1.idpolicyversion = pv_3.id)
62. 418.214 474.843 ↓ 1.3 463,762 1

HashAggregate (cost=22,083.33..27,442.05 rows=357,248 width=62) (actual time=277.313..474.843 rows=463,762 loops=1)

  • Group Key: tblriskpremium_1.idpolicyversion
63. 56.629 56.629 ↓ 1.0 751,226 1

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

64. 1.443 101,016.757 ↓ 441.0 441 1

Hash (cost=2,217.17..2,217.17 rows=1 width=151) (actual time=101,016.757..101,016.757 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
65. 796.349 101,015.314 ↓ 441.0 441 1

Nested Loop (cost=12.77..2,217.17 rows=1 width=151) (actual time=69.597..101,015.314 rows=441 loops=1)

  • Join Filter: (paym_1.id = acc_entry_1.idpayment)
  • Rows Removed by Join Filter: 15148176
66. 8.413 536.963 ↓ 9,767.0 9,767 1

Nested Loop (cost=12.77..1,001.82 rows=1 width=119) (actual time=1.137..536.963 rows=9,767 loops=1)

67. 23.246 460.174 ↓ 17,094.0 17,094 1

Nested Loop (cost=12.48..1,001.48 rows=1 width=119) (actual time=1.114..460.174 rows=17,094 loops=1)

68. 13.296 402.740 ↓ 17,094.0 17,094 1

Nested Loop (cost=12.19..1,001.17 rows=1 width=112) (actual time=1.107..402.740 rows=17,094 loops=1)

69. 75.087 303.974 ↓ 17,094.0 17,094 1

Nested Loop (cost=11.77..1,000.52 rows=1 width=80) (actual time=1.097..303.974 rows=17,094 loops=1)

70. 13.126 127.947 ↓ 10,094.0 100,940 1

Nested Loop (cost=11.48..997.26 rows=10 width=57) (actual time=0.986..127.947 rows=100,940 loops=1)

71. 0.088 8.677 ↓ 214.0 214 1

Nested Loop (cost=6.11..522.62 rows=1 width=28) (actual time=0.971..8.677 rows=214 loops=1)

72. 0.033 0.033 ↑ 1.0 1 1

Index Scan using tblagencyunit_pkey on tblagencyunit u1_1 (cost=0.29..8.31 rows=1 width=28) (actual time=0.032..0.033 rows=1 loops=1)

  • Index Cond: (id = 23)
  • Filter: (nlevel(path) = 1)
73. 7.744 8.556 ↓ 214.0 214 1

Bitmap Heap Scan on tblagencyunit u2_1 (cost=5.82..514.29 rows=1 width=26) (actual time=0.932..8.556 rows=214 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 23)
  • Rows Removed by Filter: 21931
  • Heap Blocks: exact=981
74. 0.812 0.812 ↓ 108.6 22,145 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..5.82 rows=204 width=0) (actual time=0.812..0.812 rows=22,145 loops=1)

  • Index Cond: (nlevel(path) = 2)
75. 99.724 106.144 ↓ 4.1 472 214

Bitmap Heap Scan on tblpolicy p_1 (cost=5.37..473.48 rows=116 width=45) (actual time=0.046..0.496 rows=472 loops=214)

  • Recheck Cond: (idagencyunit = u2_1.id)
  • Filter: (NOT test)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=31642
76. 6.420 6.420 ↓ 3.9 472 214

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..5.34 rows=122 width=0) (actual time=0.030..0.030 rows=472 loops=214)

  • Index Cond: (idagencyunit = u2_1.id)
77. 100.940 100.940 ↓ 0.0 0 100,940

Index Scan using tblnumber_idpolicy_indx on tblnumber n_1 (cost=0.29..0.32 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=100,940)

  • Index Cond: (idpolicy = p_1.id)
78. 85.470 85.470 ↑ 1.0 1 17,094

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_3 (cost=0.42..0.63 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=17,094)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
79. 34.188 34.188 ↑ 1.0 1 17,094

Index Scan using tblemployee_pkey on tblemployee e_1 (cost=0.29..0.32 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=17,094)

  • Index Cond: (id = p_1.idemployee)
80. 68.376 68.376 ↑ 1.0 1 17,094

Index Scan using tblpayment_idpolicy on tblpayment paym_1 (cost=0.29..0.32 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=17,094)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: ((createdtime >= '2017-04-01'::date) AND ((canceldate IS NULL) OR (date_trunc('month'::text, createdtime) <> date_trunc('month'::text, canceldate))))
  • Rows Removed by Filter: 0
81. 99,682.002 99,682.002 ↓ 6.4 1,551 9,767

CTE Scan on acc_entry acc_entry_1 (cost=0.00..1,212.33 rows=242 width=48) (actual time=9.842..10.206 rows=1,551 loops=9,767)

  • Filter: (date_trunc('month'::text, createdtime) = '2019-08-01 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 46841
82. 2.194 8.379 ↑ 1.0 1 441

Hash Join (cost=1.57..2.40 rows=1 width=13) (actual time=0.012..0.019 rows=1 loops=441)

  • Hash Cond: (tc_1.commissiontype = tblcommissiontypedef_1.key)
83. 6.174 6.174 ↑ 1.9 7 441

Index Scan using tbltechcommission_idpolicyversion on tbltechcommission tc_1 (cost=0.43..1.21 rows=13 width=21) (actual time=0.009..0.014 rows=7 loops=441)

  • Index Cond: (pv_3.id = idpolicyversion)
84. 0.002 0.011 ↑ 1.0 1 1

Hash (cost=1.12..1.12 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
85. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on tblcommissiontypedef tblcommissiontypedef_1 (cost=0.00..1.12 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (commissiongroup = 'RB'::text)
  • Rows Removed by Filter: 9
86. 2.646 2.646 ↑ 1.0 1 441

Index Scan using tblpolicyversion_pkey on tblpolicyversion pv_2 (cost=0.42..0.62 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=441)

  • Index Cond: (id = pv_3.id)
87. 12,094.425 12,094.425 ↓ 2,318.8 463,762 441

CTE Scan on aggregatedannualpremiums aap_1 (cost=0.00..4.00 rows=200 width=40) (actual time=2.086..27.425 rows=463,762 loops=441)

Planning time : 19.988 ms
Execution time : 124,706.142 ms