explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KrW3

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 93,221.233 ↓ 220.0 440 1

Append (cost=62,814.00..168,315.85 rows=2 width=203) (actual time=93,115.147..93,221.233 rows=440 loops=1)

2.          

CTE acc_entry

3. 18.435 18.435 ↑ 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.435 rows=48,392 loops=1)

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

Nested Loop (cost=59,039.49..82,238.93 rows=1 width=203) (actual time=288.483..288.483 rows=0 loops=1)

5. 0.000 288.482 ↓ 0.0 0 1

Nested Loop Left Join (cost=59,039.07..82,238.31 rows=1 width=215) (actual time=288.482..288.482 rows=0 loops=1)

6. 0.009 288.482 ↓ 0.0 0 1

Hash Join (cost=59,037.50..82,235.91 rows=1 width=210) (actual time=288.482..288.482 rows=0 loops=1)

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

Hash Join (cost=24,531.60..46,003.30 rows=460,452 width=42) (actual time=191.176..191.177 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. 86.731 190.653 ↓ 1.0 463,762 1

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

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

11. 0.000 97.296 ↓ 0.0 0 1

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

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

Hash Join (cost=24,235.00..34,505.89 rows=1 width=168) (actual time=97.295..97.296 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.001 97.292 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.000 97.291 ↓ 0.0 0 1

Nested Loop (cost=991.68..2,151.66 rows=1 width=142) (actual time=97.291..97.291 rows=0 loops=1)

17. 0.001 97.291 ↓ 0.0 0 1

Nested Loop (cost=991.39..2,151.14 rows=1 width=135) (actual time=97.291..97.291 rows=0 loops=1)

18. 0.003 97.290 ↓ 0.0 0 1

Hash Join (cost=991.10..2,142.82 rows=1 width=133) (actual time=97.290..97.290 rows=0 loops=1)

  • Hash Cond: (acc_entry.idpayment = paym.id)
19. 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)

20. 0.001 97.271 ↓ 0.0 0 1

Hash (cost=991.09..991.09 rows=1 width=109) (actual time=97.271..97.271 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.000 97.270 ↓ 0.0 0 1

Nested Loop (cost=12.16..991.09 rows=1 width=109) (actual time=97.270..97.270 rows=0 loops=1)

  • Join Filter: (p.id = paym.idpolicy)
22. 3.601 88.018 ↓ 4,850.0 4,850 1

Nested Loop (cost=11.87..990.75 rows=1 width=109) (actual time=1.023..88.018 rows=4,850 loops=1)

  • Join Filter: (p.id = pv_1.idpolicy)
23. 3.620 74.717 ↓ 4,850.0 4,850 1

Nested Loop (cost=11.45..988.94 rows=1 width=77) (actual time=1.012..74.717 rows=4,850 loops=1)

24. 0.701 66.247 ↓ 4,850.0 4,850 1

Nested Loop (cost=11.16..988.41 rows=1 width=54) (actual time=1.003..66.247 rows=4,850 loops=1)

25. 5.684 6.482 ↓ 214.0 214 1

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

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

  • Index Cond: (nlevel(path) = 2)
27. 53.072 59.064 ↓ 4.6 23 214

Bitmap Heap Scan on tblpolicy p (cost=5.34..474.06 rows=5 width=44) (actual time=0.178..0.276 rows=23 loops=214)

  • Recheck Cond: (idagencyunit = u2.id)
  • Filter: ((NOT test) AND (canceldate IS NULL) AND (createdtime < '2017-04-01 00:00:00+00'::timestamp with time zone) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 449
  • Heap Blocks: exact=31642
28. 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.id)
29. 4.850 4.850 ↑ 1.0 1 4,850

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.52 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=4,850)

  • Index Cond: (idpolicy = p.id)
30. 9.700 9.700 ↑ 1.0 1 4,850

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_1 (cost=0.42..1.80 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,850)

  • Index Cond: (idpolicy = n.idpolicy)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
31. 9.700 9.700 ↓ 0.0 0 4,850

Index Scan using tblpayment_idpolicy on tblpayment paym (cost=0.29..0.33 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=4,850)

  • Index Cond: (idpolicy = pv_1.idpolicy)
  • 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
32. 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)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using tblemployee_pkey on tblemployee e (cost=0.29..0.52 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.258 92,932.702 ↓ 440.0 440 1

Nested Loop (cost=59,102.94..82,302.38 rows=1 width=203) (actual time=92,826.662..92,932.702 rows=440 loops=1)

40. 0.369 92,931.564 ↓ 440.0 440 1

Nested Loop Left Join (cost=59,102.52..82,301.76 rows=1 width=223) (actual time=92,826.653..92,931.564 rows=440 loops=1)

41. 29.106 92,928.995 ↓ 440.0 440 1

Hash Join (cost=59,100.95..82,299.35 rows=1 width=218) (actual time=92,826.610..92,928.995 rows=440 loops=1)

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

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

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

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

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

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

46. 0.227 92,499.625 ↓ 440.0 440 1

Hash (cost=34,569.34..34,569.34 rows=1 width=176) (actual time=92,499.624..92,499.625 rows=440 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
47. 28.790 92,499.398 ↓ 440.0 440 1

Hash Join (cost=24,298.45..34,569.34 rows=1 width=176) (actual time=92,273.074..92,499.398 rows=440 loops=1)

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

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

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

50. 1.009 91,984.864 ↓ 440.0 440 1

Hash (cost=2,215.10..2,215.10 rows=1 width=150) (actual time=91,984.864..91,984.864 rows=440 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
51. 719.950 91,983.855 ↓ 440.0 440 1

Nested Loop (cost=12.74..2,215.10 rows=1 width=150) (actual time=58.034..91,983.855 rows=440 loops=1)

  • Join Filter: (paym_1.id = acc_entry_1.idpayment)
  • Rows Removed by Join Filter: 14413003
52. 12.081 266.849 ↓ 9,293.0 9,293 1

Nested Loop (cost=12.74..999.75 rows=1 width=118) (actual time=0.983..266.849 rows=9,293 loops=1)

  • Join Filter: (p_1.id = paym_1.idpolicy)
53. 10.092 222.611 ↓ 10,719.0 10,719 1

Nested Loop (cost=12.45..999.42 rows=1 width=118) (actual time=0.974..222.611 rows=10,719 loops=1)

54. 11.630 191.081 ↓ 10,719.0 10,719 1

Nested Loop (cost=12.16..999.01 rows=1 width=111) (actual time=0.966..191.081 rows=10,719 loops=1)

  • Join Filter: (p_1.id = pv_3.idpolicy)
55. 8.991 136.575 ↓ 10,719.0 10,719 1

Nested Loop (cost=11.74..997.21 rows=1 width=79) (actual time=0.955..136.575 rows=10,719 loops=1)

56. 3.926 84.708 ↓ 10,719.0 10,719 1

Nested Loop (cost=11.45..996.79 rows=1 width=56) (actual time=0.949..84.708 rows=10,719 loops=1)

57. 0.096 6.524 ↓ 214.0 214 1

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

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

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

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

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

  • Index Cond: (nlevel(path) = 2)
61. 68.052 74.258 ↓ 4.5 50 214

Bitmap Heap Scan on tblpolicy p_1 (cost=5.34..474.06 rows=11 width=44) (actual time=0.048..0.347 rows=50 loops=214)

  • Recheck Cond: (idagencyunit = u2_1.id)
  • Filter: ((NOT test) AND (canceldate IS NULL) AND (createdtime >= '2017-04-01 00:00:00+00'::timestamp with time zone) AND (policytype = 'POLICY'::text))
  • Rows Removed by Filter: 422
  • Heap Blocks: exact=31642
62. 6.206 6.206 ↓ 3.9 472 214

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

  • Index Cond: (idagencyunit = u2_1.id)
63. 42.876 42.876 ↑ 1.0 1 10,719

Index Scan using tblnumber_idpolicy_indx on tblnumber n_1 (cost=0.29..0.40 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=10,719)

  • Index Cond: (idpolicy = p_1.id)
64. 42.876 42.876 ↑ 1.0 1 10,719

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_3 (cost=0.42..1.80 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=10,719)

  • Index Cond: (idpolicy = n_1.idpolicy)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
65. 21.438 21.438 ↑ 1.0 1 10,719

Index Scan using tblemployee_pkey on tblemployee e_1 (cost=0.29..0.40 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=10,719)

  • Index Cond: (id = p_1.idemployee)
66. 32.157 32.157 ↑ 1.0 1 10,719

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

  • Index Cond: (idpolicy = pv_3.idpolicy)
  • 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. 90,997.056 90,997.056 ↓ 6.4 1,551 9,293

CTE Scan on acc_entry acc_entry_1 (cost=0.00..1,212.33 rows=242 width=48) (actual time=9.442..9.792 rows=1,551 loops=9,293)

  • Filter: (date_trunc('month'::text, createdtime) = '2019-08-01 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 46841
68. 0.429 2.200 ↑ 1.0 1 440

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

  • Hash Cond: (tc_1.commissiontype = tblcommissiontypedef_1.key)
69. 1.760 1.760 ↑ 1.9 7 440

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=440)

  • 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.880 0.880 ↑ 1.0 1 440

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=440)

  • Index Cond: (id = pv_3.id)
Planning time : 11.252 ms
Execution time : 93,225.546 ms