explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ep2h

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 98,585.011 ↓ 220.5 441 1

Append (cost=62,815.90..168,319.81 rows=2 width=203) (actual time=98,480.475..98,585.011 rows=441 loops=1)

2.          

CTE acc_entry

3. 18.202 18.202 ↑ 1.0 48,392 1

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

  • Filter: (type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[]))
  • Rows Removed by Filter: 68751
4. 0.001 383.192 ↓ 0.0 0 1

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

5. 0.000 383.191 ↓ 0.0 0 1

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

6. 0.010 383.191 ↓ 0.0 0 1

Hash Join (cost=59,039.40..82,237.80 rows=1 width=210) (actual time=383.190..383.191 rows=0 loops=1)

  • Hash Cond: (pc.idpolicyversion = pv_1.id)
7. 0.488 192.311 ↑ 460,452.0 1 1

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

  • Hash Cond: (pcr.idpolicyclient = pc.id)
8. 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)
9. 87.986 191.809 ↓ 1.0 463,762 1

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

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

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

11. 0.000 190.870 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.004 190.870 ↓ 0.0 0 1

Hash Join (cost=24,236.90..34,507.79 rows=1 width=168) (actual time=190.869..190.870 rows=0 loops=1)

  • Hash Cond: (tblriskpremium.idpolicyversion = pv_1.id)
13. 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
14. 0.000 0.000 ↓ 0.0 0

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

15. 0.000 190.866 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.002 190.866 ↓ 0.0 0 1

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

17. 0.000 190.864 ↓ 0.0 0 1

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

18. 0.000 190.864 ↓ 0.0 0 1

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

19. 0.004 190.864 ↓ 0.0 0 1

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

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

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

21. 0.000 190.844 ↓ 0.0 0 1

Hash (cost=992.54..992.54 rows=1 width=109) (actual time=190.844..190.844 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
22. 7.477 190.844 ↓ 0.0 0 1

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

23. 0.000 166.273 ↓ 17,094.0 17,094 1

Nested Loop (cost=11.48..992.20 rows=1 width=77) (actual time=1.052..166.273 rows=17,094 loops=1)

24. 7.575 71.469 ↓ 10,094.0 100,940 1

Nested Loop (cost=11.19..988.94 rows=10 width=54) (actual time=0.939..71.469 rows=100,940 loops=1)

25. 5.529 6.328 ↓ 214.0 214 1

Bitmap Heap Scan on tblagencyunit u2 (cost=5.82..514.29 rows=1 width=26) (actual time=0.924..6.328 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
26. 0.799 0.799 ↓ 108.6 22,145 1

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

  • Index Cond: (nlevel(path) = 2)
27. 52.216 57.566 ↓ 4.1 472 214

Bitmap Heap Scan on tblpolicy p (cost=5.37..473.48 rows=116 width=44) (actual time=0.038..0.269 rows=472 loops=214)

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

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

  • Index Cond: (idagencyunit = u2.id)
29. 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)
30. 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
31. 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)
32. 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)
33. 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)
34. 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)
35. 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)
36. 0.000 0.000 ↓ 0.0 0

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

37. 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)
38. 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)
39. 0.249 98,201.778 ↓ 441.0 441 1

Nested Loop (cost=59,105.00..82,304.44 rows=1 width=203) (actual time=98,097.281..98,201.778 rows=441 loops=1)

40. 0.300 98,200.647 ↓ 441.0 441 1

Nested Loop Left Join (cost=59,104.58..82,303.82 rows=1 width=223) (actual time=98,097.271..98,200.647 rows=441 loops=1)

41. 29.933 98,198.142 ↓ 441.0 441 1

Hash Join (cost=59,103.01..82,301.41 rows=1 width=218) (actual time=98,097.228..98,198.142 rows=441 loops=1)

  • Hash Cond: (pc_1.idpolicyversion = pv_3.id)
42. 136.025 391.113 ↓ 1.0 463,762 1

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

  • Hash Cond: (pcr_1.idpolicyclient = pc_1.id)
43. 83.781 83.781 ↓ 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.007..83.781 rows=463,762 loops=1)

  • Filter: (role = 'ubezpieczony'::text)
  • Rows Removed by Filter: 463762
44. 75.887 171.307 ↓ 1.0 463,762 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 40011kB
45. 95.420 95.420 ↓ 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..95.420 rows=463,762 loops=1)

46. 0.244 97,777.096 ↓ 441.0 441 1

Hash (cost=34,571.40..34,571.40 rows=1 width=176) (actual time=97,777.096..97,777.096 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
47. 29.426 97,776.852 ↓ 441.0 441 1

Hash Join (cost=24,300.51..34,571.40 rows=1 width=176) (actual time=97,548.250..97,776.852 rows=441 loops=1)

  • Hash Cond: (tblriskpremium_1.idpolicyversion = pv_3.id)
48. 418.133 474.449 ↓ 1.3 463,762 1

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

  • Group Key: tblriskpremium_1.idpolicyversion
49. 56.316 56.316 ↓ 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.316 rows=751,226 loops=1)

50. 1.014 97,272.977 ↓ 441.0 441 1

Hash (cost=2,217.17..2,217.17 rows=1 width=150) (actual time=97,272.977..97,272.977 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
51. 778.565 97,271.963 ↓ 441.0 441 1

Nested Loop (cost=12.77..2,217.17 rows=1 width=150) (actual time=67.152..97,271.963 rows=441 loops=1)

  • Join Filter: (paym_1.id = acc_entry_1.idpayment)
  • Rows Removed by Join Filter: 15148176
52. 7.119 405.652 ↓ 9,767.0 9,767 1

Nested Loop (cost=12.77..1,001.82 rows=1 width=118) (actual time=1.023..405.652 rows=9,767 loops=1)

53. 7.326 347.251 ↓ 17,094.0 17,094 1

Nested Loop (cost=12.48..1,001.48 rows=1 width=118) (actual time=1.000..347.251 rows=17,094 loops=1)

54. 7.742 305.737 ↓ 17,094.0 17,094 1

Nested Loop (cost=12.19..1,001.17 rows=1 width=111) (actual time=0.993..305.737 rows=17,094 loops=1)

55. 39.068 229.619 ↓ 17,094.0 17,094 1

Nested Loop (cost=11.77..1,000.52 rows=1 width=79) (actual time=0.982..229.619 rows=17,094 loops=1)

56. 10.108 89.611 ↓ 10,094.0 100,940 1

Nested Loop (cost=11.48..997.26 rows=10 width=56) (actual time=0.884..89.611 rows=100,940 loops=1)

57. 0.073 6.529 ↓ 214.0 214 1

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

58. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

  • Index Cond: (id = 23)
  • Filter: (nlevel(path) = 1)
59. 5.703 6.445 ↓ 214.0 214 1

Bitmap Heap Scan on tblagencyunit u2_1 (cost=5.82..514.29 rows=1 width=26) (actual time=0.855..6.445 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
60. 0.742 0.742 ↓ 108.6 22,145 1

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

  • Index Cond: (nlevel(path) = 2)
61. 66.982 72.974 ↓ 4.1 472 214

Bitmap Heap Scan on tblpolicy p_1 (cost=5.37..473.48 rows=116 width=44) (actual time=0.042..0.341 rows=472 loops=214)

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

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

  • Index Cond: (idagencyunit = u2_1.id)
63. 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)
64. 68.376 68.376 ↑ 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.003..0.004 rows=1 loops=17,094)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
65. 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)
66. 51.282 51.282 ↑ 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.002..0.003 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
67. 96,087.746 96,087.746 ↓ 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.490..9.838 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
68. 0.430 2.205 ↑ 1.0 1 441

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

  • Hash Cond: (tc_1.commissiontype = tblcommissiontypedef_1.key)
69. 1.764 1.764 ↑ 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.003..0.004 rows=7 loops=441)

  • Index Cond: (pv_3.id = idpolicyversion)
70. 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
71. 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
72. 0.882 0.882 ↑ 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.002..0.002 rows=1 loops=441)

  • Index Cond: (id = pv_3.id)
Planning time : 10.350 ms
Execution time : 98,589.256 ms