explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VWvn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.126 1,232.722 ↓ 54.5 109 1

Sort (cost=143,813.36..143,813.37 rows=2 width=323) (actual time=1,232.708..1,232.722 rows=109 loops=1)

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

CTE acc_entry

3. 107.074 107.074 ↑ 1.0 132,618 1

Seq Scan on tblaccountingentry (cost=0.00..21,337.24 rows=132,826 width=82) (actual time=0.015..107.074 rows=132,618 loops=1)

  • Filter: (type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[]))
  • Rows Removed by Filter: 530421
4. 0.421 1,232.596 ↓ 54.5 109 1

Result (cost=43,751.99..122,476.11 rows=2 width=323) (actual time=1,089.483..1,232.596 rows=109 loops=1)

5. 0.018 1,232.175 ↓ 54.5 109 1

Append (cost=43,751.99..122,475.96 rows=2 width=200) (actual time=1,089.457..1,232.175 rows=109 loops=1)

6. 0.001 195.541 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 1" (cost=43,751.99..61,153.72 rows=1 width=200) (actual time=195.541..195.541 rows=0 loops=1)

7. 0.002 195.540 ↓ 0.0 0 1

Nested Loop (cost=43,751.99..61,153.71 rows=1 width=200) (actual time=195.539..195.540 rows=0 loops=1)

  • Join Filter: (pv_1.id = pv.id)
8. 0.000 195.538 ↓ 0.0 0 1

Nested Loop Left Join (cost=43,751.56..61,152.99 rows=1 width=212) (actual time=195.538..195.538 rows=0 loops=1)

9. 0.024 195.538 ↓ 0.0 0 1

Hash Join (cost=43,751.01..61,151.77 rows=1 width=207) (actual time=195.538..195.538 rows=0 loops=1)

  • Hash Cond: (pc.idpolicyversion = pv_1.id)
10. 1.890 135.676 ↑ 349,240.0 1 1

Hash Join (cost=19,335.90..35,427.00 rows=349,240 width=36) (actual time=135.675..135.676 rows=1 loops=1)

  • Hash Cond: (pc.id = pcr.idpolicyclient)
11. 0.006 0.006 ↑ 420,918.0 1 1

Seq Scan on tblpolicyclient pc (cost=0.00..14,986.18 rows=420,918 width=44) (actual time=0.006..0.006 rows=1 loops=1)

12. 52.995 133.780 ↓ 1.0 349,981 1

Hash (cost=14,970.40..14,970.40 rows=349,240 width=8) (actual time=133.780..133.780 rows=349,981 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 17768kB
13. 80.785 80.785 ↓ 1.0 349,981 1

Seq Scan on tblpolicyclientrole pcr (cost=0.00..14,970.40 rows=349,240 width=8) (actual time=0.010..80.785 rows=349,981 loops=1)

  • Filter: (role = 'ubezpieczony'::text)
  • Rows Removed by Filter: 335281
14. 0.001 59.838 ↓ 0.0 0 1

Hash (cost=24,415.09..24,415.09 rows=1 width=171) (actual time=59.838..59.838 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.014 59.837 ↓ 0.0 0 1

Hash Join (cost=19,319.23..24,415.09 rows=1 width=171) (actual time=59.837..59.837 rows=0 loops=1)

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

HashAggregate (cost=15,391.97..18,050.67 rows=177,247 width=62) (never executed)

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

Seq Scan on tblriskpremium (cost=0.00..12,707.31 rows=536,931 width=13) (never executed)

18. 0.000 59.823 ↓ 0.0 0 1

Hash (cost=3,927.25..3,927.25 rows=1 width=145) (actual time=59.823..59.823 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.001 59.823 ↓ 0.0 0 1

Nested Loop (cost=764.08..3,927.25 rows=1 width=145) (actual time=59.823..59.823 rows=0 loops=1)

20. 0.001 59.822 ↓ 0.0 0 1

Nested Loop (cost=763.80..3,926.95 rows=1 width=127) (actual time=59.822..59.822 rows=0 loops=1)

21. 0.000 59.821 ↓ 0.0 0 1

Nested Loop (cost=763.38..3,926.04 rows=1 width=127) (actual time=59.821..59.821 rows=0 loops=1)

22. 0.014 59.821 ↓ 0.0 0 1

Hash Join (cost=763.09..3,917.72 rows=1 width=119) (actual time=59.821..59.821 rows=0 loops=1)

  • Hash Cond: (acc_entry.idpayment = paym.id)
23. 0.018 0.018 ↑ 132,826.0 1 1

CTE Scan on acc_entry (cost=0.00..2,656.52 rows=132,826 width=40) (actual time=0.018..0.018 rows=1 loops=1)

24. 0.000 59.789 ↓ 0.0 0 1

Hash (cost=763.08..763.08 rows=1 width=95) (actual time=59.789..59.789 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 3.529 59.789 ↓ 0.0 0 1

Nested Loop (cost=18.93..763.08 rows=1 width=95) (actual time=59.789..59.789 rows=0 loops=1)

26. 3.865 41.462 ↓ 493.3 7,399 1

Nested Loop (cost=18.51..753.70 rows=15 width=63) (actual time=21.034..41.462 rows=7,399 loops=1)

27. 1.581 17.908 ↓ 328.1 19,689 1

Nested Loop (cost=18.22..733.56 rows=60 width=43) (actual time=2.907..17.908 rows=19,689 loops=1)

28. 0.716 0.802 ↓ 3.0 3 1

Bitmap Heap Scan on tblagencyunit u2 (cost=4.45..57.95 rows=1 width=25) (actual time=0.426..0.802 rows=3 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 2375)
  • Rows Removed by Filter: 2618
  • Heap Blocks: exact=97
29. 0.086 0.086 ↓ 119.1 2,621 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.086..0.086 rows=2,621 loops=1)

  • Index Cond: (nlevel(path) = 2)
30. 13.530 15.525 ↓ 39.8 6,563 3

Bitmap Heap Scan on tblpolicy p (cost=13.77..673.97 rows=165 width=34) (actual time=0.838..5.175 rows=6,563 loops=3)

  • Recheck Cond: (idagencyunit = u2.id)
  • Filter: (NOT test)
  • Heap Blocks: exact=5465
31. 1.995 1.995 ↓ 37.7 6,563 3

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.665..0.665 rows=6,563 loops=3)

  • Index Cond: (idagencyunit = u2.id)
32. 19.689 19.689 ↓ 0.0 0 19,689

Index Scan using tblnumber_idpolicy_indx on tblnumber n (cost=0.29..0.33 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=19,689)

  • Index Cond: (idpolicy = p.id)
33. 14.798 14.798 ↓ 0.0 0 7,399

Index Scan using tblpayment_idpolicy on tblpayment paym (cost=0.42..0.62 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=7,399)

  • Index Cond: (idpolicy = p.id)
  • Filter: ((createdtime < '2017-04-01'::date) AND (date_trunc('month'::text, paymentdate) = '2019-05-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
34. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

Nested Loop (cost=0.56..1.21 rows=1 width=13) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using tbltechcommission_idpolicyversion on tbltechcommission tc (cost=0.43..0.75 rows=3 width=19) (never executed)

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

Index Scan using tblcommissiontypedef_pkey on tblcommissiontypedef (cost=0.13..0.15 rows=1 width=32) (never executed)

  • Index Cond: (key = tc.commissiontype)
  • Filter: (commissiongroup = 'RB'::text)
40. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = pc.idpolicyversion)
41. 0.033 1,036.616 ↓ 109.0 109 1

Subquery Scan on "*SELECT* 2" (cost=43,920.48..61,322.22 rows=1 width=200) (actual time=893.914..1,036.616 rows=109 loops=1)

42. 0.144 1,036.583 ↓ 109.0 109 1

Nested Loop (cost=43,920.48..61,322.21 rows=1 width=200) (actual time=893.912..1,036.583 rows=109 loops=1)

  • Join Filter: (pv_3.id = pv_2.id)
43. 0.062 1,035.785 ↓ 109.0 109 1

Nested Loop Left Join (cost=43,920.06..61,321.49 rows=1 width=220) (actual time=893.882..1,035.785 rows=109 loops=1)

44. 18.661 1,034.197 ↓ 109.0 109 1

Hash Join (cost=43,919.50..61,320.26 rows=1 width=215) (actual time=893.829..1,034.197 rows=109 loops=1)

  • Hash Cond: (pc_1.idpolicyversion = pv_3.id)
45. 155.846 340.841 ↓ 1.0 349,981 1

Hash Join (cost=19,335.90..35,427.00 rows=349,240 width=36) (actual time=132.535..340.841 rows=349,981 loops=1)

  • Hash Cond: (pc_1.id = pcr_1.idpolicyclient)
46. 54.094 54.094 ↓ 1.0 420,944 1

Seq Scan on tblpolicyclient pc_1 (cost=0.00..14,986.18 rows=420,918 width=44) (actual time=0.006..54.094 rows=420,944 loops=1)

47. 51.347 130.901 ↓ 1.0 349,981 1

Hash (cost=14,970.40..14,970.40 rows=349,240 width=8) (actual time=130.901..130.901 rows=349,981 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 17768kB
48. 79.554 79.554 ↓ 1.0 349,981 1

Seq Scan on tblpolicyclientrole pcr_1 (cost=0.00..14,970.40 rows=349,240 width=8) (actual time=0.013..79.554 rows=349,981 loops=1)

  • Filter: (role = 'ubezpieczony'::text)
  • Rows Removed by Filter: 335281
49. 0.103 674.695 ↓ 109.0 109 1

Hash (cost=24,583.59..24,583.59 rows=1 width=179) (actual time=674.695..674.695 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
50. 19.105 674.592 ↓ 109.0 109 1

Hash Join (cost=19,487.73..24,583.59 rows=1 width=179) (actual time=469.854..674.592 rows=109 loops=1)

  • Hash Cond: (tblriskpremium_1.idpolicyversion = pv_3.id)
51. 382.723 436.541 ↓ 2.0 349,901 1

HashAggregate (cost=15,391.97..18,050.67 rows=177,247 width=62) (actual time=247.889..436.541 rows=349,901 loops=1)

  • Group Key: tblriskpremium_1.idpolicyversion
52. 53.818 53.818 ↑ 1.0 536,931 1

Seq Scan on tblriskpremium tblriskpremium_1 (cost=0.00..12,707.31 rows=536,931 width=13) (actual time=0.011..53.818 rows=536,931 loops=1)

53. 0.047 218.946 ↓ 109.0 109 1

Hash (cost=4,095.75..4,095.75 rows=1 width=153) (actual time=218.946..218.946 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
54. 0.034 218.899 ↓ 109.0 109 1

Nested Loop (cost=764.06..4,095.75 rows=1 width=153) (actual time=66.148..218.899 rows=109 loops=1)

55. 0.061 218.756 ↓ 109.0 109 1

Nested Loop (cost=763.77..4,095.44 rows=1 width=135) (actual time=66.133..218.756 rows=109 loops=1)

56. 0.085 218.041 ↓ 109.0 109 1

Nested Loop (cost=763.35..4,094.53 rows=1 width=135) (actual time=66.106..218.041 rows=109 loops=1)

57. 0.464 217.847 ↓ 109.0 109 1

Hash Join (cost=763.07..4,086.22 rows=1 width=127) (actual time=66.079..217.847 rows=109 loops=1)

  • Hash Cond: (acc_entry_1.idpayment = paym_1.id)
58. 166.325 166.325 ↓ 4.8 3,212 1

CTE Scan on acc_entry acc_entry_1 (cost=0.00..3,320.65 rows=664 width=48) (actual time=15.008..166.325 rows=3,212 loops=1)

  • Filter: (date_trunc('month'::text, createdtime) = '2019-05-01 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 129406
59. 0.447 51.058 ↓ 355.0 1,775 1

Hash (cost=763.01..763.01 rows=5 width=95) (actual time=51.058..51.058 rows=1,775 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 256kB
60. 0.000 50.611 ↓ 355.0 1,775 1

Nested Loop (cost=18.93..763.01 rows=5 width=95) (actual time=21.546..50.611 rows=1,775 loops=1)

61. 1.045 37.547 ↓ 493.3 7,399 1

Nested Loop (cost=18.51..753.70 rows=15 width=63) (actual time=19.796..37.547 rows=7,399 loops=1)

62. 1.575 16.813 ↓ 328.1 19,689 1

Nested Loop (cost=18.22..733.56 rows=60 width=43) (actual time=2.306..16.813 rows=19,689 loops=1)

63. 0.683 0.763 ↓ 3.0 3 1

Bitmap Heap Scan on tblagencyunit u2_1 (cost=4.45..57.95 rows=1 width=25) (actual time=0.408..0.763 rows=3 loops=1)

  • Recheck Cond: (nlevel(path) = 2)
  • Filter: ((ltree2text(subpath(path, 0, 1)))::bigint = 2375)
  • Rows Removed by Filter: 2618
  • Heap Blocks: exact=97
64. 0.080 0.080 ↓ 119.1 2,621 1

Bitmap Index Scan on tblagencyunit_nlevel (cost=0.00..4.45 rows=22 width=0) (actual time=0.080..0.080 rows=2,621 loops=1)

  • Index Cond: (nlevel(path) = 2)
65. 13.101 14.475 ↓ 39.8 6,563 3

Bitmap Heap Scan on tblpolicy p_1 (cost=13.77..673.97 rows=165 width=34) (actual time=0.634..4.825 rows=6,563 loops=3)

  • Recheck Cond: (idagencyunit = u2_1.id)
  • Filter: (NOT test)
  • Heap Blocks: exact=5465
66. 1.374 1.374 ↓ 37.7 6,563 3

Bitmap Index Scan on tblpolicy_idagencyunit (cost=0.00..13.73 rows=174 width=0) (actual time=0.458..0.458 rows=6,563 loops=3)

  • Index Cond: (idagencyunit = u2_1.id)
67. 19.689 19.689 ↓ 0.0 0 19,689

Index Scan using tblnumber_idpolicy_indx on tblnumber n_1 (cost=0.29..0.33 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=19,689)

  • Index Cond: (idpolicy = p_1.id)
68. 14.798 14.798 ↓ 0.0 0 7,399

Index Scan using tblpayment_idpolicy on tblpayment paym_1 (cost=0.42..0.59 rows=3 width=32) (actual time=0.002..0.002 rows=0 loops=7,399)

  • 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: 1
69. 0.109 0.109 ↑ 1.0 1 109

Index Scan using tblagencyunit_pkey on tblagencyunit u1_1 (cost=0.28..8.31 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = 2375)
  • Filter: (nlevel(path) = 1)
70. 0.654 0.654 ↑ 1.0 1 109

Index Scan using tblpolicyversion_idpolicy on tblpolicyversion pv_3 (cost=0.42..0.90 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=109)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
71. 0.109 0.109 ↑ 1.0 1 109

Index Scan using tblemployee_pkey on tblemployee e_1 (cost=0.29..0.31 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = p_1.idemployee)
72. 0.218 1.526 ↑ 1.0 1 109

Nested Loop (cost=0.56..1.21 rows=1 width=13) (actual time=0.012..0.014 rows=1 loops=109)

73. 0.981 0.981 ↑ 1.0 3 109

Index Scan using tbltechcommission_idpolicyversion on tbltechcommission tc_1 (cost=0.43..0.75 rows=3 width=19) (actual time=0.008..0.009 rows=3 loops=109)

  • Index Cond: (pv_3.id = idpolicyversion)
74. 0.327 0.327 ↓ 0.0 0 327

Index Scan using tblcommissiontypedef_pkey on tblcommissiontypedef tblcommissiontypedef_1 (cost=0.13..0.15 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=327)

  • Index Cond: (key = tc_1.commissiontype)
  • Filter: (commissiongroup = 'RB'::text)
  • Rows Removed by Filter: 1
75. 0.654 0.654 ↑ 1.0 1 109

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

  • Index Cond: (id = pc_1.idpolicyversion)
Planning time : 14.415 ms
Execution time : 1,254.922 ms