explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qIhU

Settings
# exclusive inclusive rows x rows loops node
1. 0.242 99,922.672 ↓ 220.5 441 1

Sort (cost=168,319.97..168,319.98 rows=2 width=328) (actual time=99,922.653..99,922.672 rows=441 loops=1)

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

CTE acc_entry

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

  • Filter: (type = ANY ('{CREATE_PAYMENT,CANCEL_PAYMENT}'::text[]))
  • Rows Removed by Filter: 68751
4. 1.120 99,922.430 ↓ 220.5 441 1

Result (cost=59,041.39..164,545.45 rows=2 width=328) (actual time=99,823.443..99,922.430 rows=441 loops=1)

5. 0.048 99,921.310 ↓ 220.5 441 1

Append (cost=59,041.39..164,545.30 rows=2 width=203) (actual time=99,823.425..99,921.310 rows=441 loops=1)

6. 0.000 395.939 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 1" (cost=59,041.39..82,240.84 rows=1 width=203) (actual time=395.939..395.939 rows=0 loops=1)

7. 0.001 395.939 ↓ 0.0 0 1

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

8. 0.000 395.938 ↓ 0.0 0 1

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

9. 0.009 395.938 ↓ 0.0 0 1

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

  • Hash Cond: (pc.idpolicyversion = pv_1.id)
10. 0.442 194.268 ↑ 460,452.0 1 1

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

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

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

  • Filter: (role = 'ubezpieczony'::text)
12. 89.498 193.816 ↓ 1.0 463,762 1

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

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

14. 0.000 201.661 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.003 201.661 ↓ 0.0 0 1

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

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

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

18. 0.001 201.658 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.000 201.657 ↓ 0.0 0 1

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

20. 0.001 201.657 ↓ 0.0 0 1

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

21. 0.000 201.656 ↓ 0.0 0 1

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

22. 0.004 201.656 ↓ 0.0 0 1

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

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

24. 0.001 201.637 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 9.368 201.636 ↓ 0.0 0 1

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

26. 0.000 175.174 ↓ 17,094.0 17,094 1

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

27. 8.334 74.601 ↓ 10,094.0 100,940 1

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

28. 5.530 6.347 ↓ 214.0 214 1

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

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

  • Index Cond: (nlevel(path) = 2)
30. 54.142 59.920 ↓ 4.1 472 214

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

  • Recheck Cond: (idagencyunit = u2.id)
  • Filter: (NOT test)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=31642
31. 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)
32. 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)
33. 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
34. 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)
35. 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)
36. 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)
37. 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)
38. 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)
39. 0.000 0.000 ↓ 0.0 0

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

40. 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)
41. 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)
42. 0.076 99,525.323 ↓ 441.0 441 1

Subquery Scan on "*SELECT* 2" (cost=59,105.00..82,304.45 rows=1 width=203) (actual time=99,427.484..99,525.323 rows=441 loops=1)

43. 0.256 99,525.247 ↓ 441.0 441 1

Nested Loop (cost=59,105.00..82,304.44 rows=1 width=203) (actual time=99,427.483..99,525.247 rows=441 loops=1)

44. 0.303 99,524.109 ↓ 441.0 441 1

Nested Loop Left Join (cost=59,104.58..82,303.82 rows=1 width=223) (actual time=99,427.473..99,524.109 rows=441 loops=1)

45. 28.057 99,521.601 ↓ 441.0 441 1

Hash Join (cost=59,103.01..82,301.41 rows=1 width=218) (actual time=99,427.431..99,521.601 rows=441 loops=1)

  • Hash Cond: (pc_1.idpolicyversion = pv_3.id)
46. 126.729 389.832 ↓ 1.0 463,762 1

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

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

  • Filter: (role = 'ubezpieczony'::text)
  • Rows Removed by Filter: 463762
48. 83.686 181.530 ↓ 1.0 463,762 1

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

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

50. 0.217 99,103.712 ↓ 441.0 441 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
51. 28.267 99,103.495 ↓ 441.0 441 1

Hash Join (cost=24,300.51..34,571.40 rows=1 width=176) (actual time=98,882.529..99,103.495 rows=441 loops=1)

  • Hash Cond: (tblriskpremium_1.idpolicyversion = pv_3.id)
52. 410.161 465.720 ↓ 1.3 463,762 1

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

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

54. 1.138 98,609.508 ↓ 441.0 441 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
55. 757.195 98,608.370 ↓ 441.0 441 1

Nested Loop (cost=12.77..2,217.17 rows=1 width=150) (actual time=69.762..98,608.370 rows=441 loops=1)

  • Join Filter: (paym_1.id = acc_entry_1.idpayment)
  • Rows Removed by Join Filter: 15148176
56. 15.142 444.884 ↓ 9,767.0 9,767 1

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

57. 12.920 378.460 ↓ 17,094.0 17,094 1

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

58. 14.533 331.352 ↓ 17,094.0 17,094 1

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

59. 50.300 248.443 ↓ 17,094.0 17,094 1

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

60. 11.605 97.203 ↓ 10,094.0 100,940 1

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

61. 0.083 6.846 ↓ 214.0 214 1

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

62. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (id = 23)
  • Filter: (nlevel(path) = 1)
63. 5.981 6.753 ↓ 214.0 214 1

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

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

  • Index Cond: (nlevel(path) = 2)
65. 72.546 78.752 ↓ 4.1 472 214

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

  • Recheck Cond: (idagencyunit = u2_1.id)
  • Filter: (NOT test)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=31642
66. 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)
67. 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)
68. 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.004..0.004 rows=1 loops=17,094)

  • Index Cond: (idpolicy = p_1.id)
  • Filter: (policyversionstatus = 'CURRENT'::text)
  • Rows Removed by Filter: 0
69. 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)
70. 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.003..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
71. 97,406.291 97,406.291 ↓ 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.617..9.973 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
72. 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)
73. 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)
74. 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
75. 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
76. 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 : 11.316 ms
Execution time : 99,926.426 ms