explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d9TZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,325.344 ↑ 236.0 1 1

Sort (cost=14,732.47..14,733.06 rows=236 width=50) (actual time=10,325.344..10,325.344 rows=1 loops=1)

  • Sort Key: t.slevel
  • Sort Method: quicksort Memory: 25kB
2.          

CTE slrdepartmentrecurs

3. 13.648 2,775.751 ↓ 77.1 13,176 1

Recursive Union (cost=2.80..1,619.17 rows=171 width=97) (actual time=1.322..2,775.751 rows=13,176 loops=1)

4. 0.423 1.343 ↑ 1.0 1 1

Index Scan using pk_slrdepartment on slrdepartment t_1 (cost=2.80..7.53 rows=1 width=97) (actual time=1.320..1.343 rows=1 loops=1)

  • Index Cond: (id = '11541001'::numeric)
  • Filter: ($1 AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)))
5.          

Initplan (for Index Scan)

6. 0.920 0.920 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.919..0.920 rows=1 loops=1)

7. 16.265 2,760.760 ↓ 96.9 1,647 8

Nested Loop (cost=2.80..160.82 rows=17 width=97) (actual time=0.329..345.095 rows=1,647 loops=8)

8.          

Initplan (for Nested Loop)

9. 0.159 0.159 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.159..0.159 rows=1 loops=1)

10. 3.728 3.728 ↓ 164.7 1,647 8

WorkTable Scan on slrdepartmentrecurs (cost=0.00..0.20 rows=10 width=18) (actual time=0.001..0.466 rows=1,647 loops=8)

11. 2,740.608 2,740.608 ↑ 2.0 1 13,176

Index Scan using ak_slrdepartment_parent_id_br on slrdepartment t_2 (cost=0.29..15.79 rows=2 width=97) (actual time=0.050..0.208 rows=1 loops=13,176)

  • Index Cond: (idparent = slrdepartmentrecurs.id)
  • Filter: ($2 AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)))
  • Rows Removed by Filter: 0
12.          

CTE slrstaffpostemp

13. 14.645 10,307.786 ↑ 236.0 1 1

Nested Loop (cost=6.20..6,159.95 rows=236 width=1,621) (actual time=286.756..10,307.786 rows=1 loops=1)

14.          

Initplan (for Nested Loop)

15. 0.274 0.274 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.273..0.274 rows=1 loops=1)

16. 2,795.723 2,795.723 ↓ 77.1 13,176 1

CTE Scan on slrdepartmentrecurs r (cost=0.00..3.42 rows=171 width=534) (actual time=1.324..2,795.723 rows=13,176 loops=1)

17. 7,496.650 7,497.144 ↓ 0.0 0 13,176

Index Scan using fk_slrstaffpos_department on slrstaffpos t_3 (cost=3.69..35.96 rows=1 width=1,589) (actual time=0.569..0.569 rows=0 loops=13,176)

  • Index Cond: (iddepartment = r.id)
  • Filter: ($6 AND ((NOT (hashed SubPlan 4)) OR (iddepartment IS NULL)) AND (dbegin <= cmn.todate('31.01.2020 00:00:00'::character varying)) AND (instr((upper((smnemocode)::text))::character varying, '50344533'::character varying) <> 0) AND (COALESCE(dend, cmn.todate('31.01.2020 00:00:00'::character varying)) >= cmn.todate('01.01.2020 00:00:00'::character varying)))
  • Rows Removed by Filter: 9
18.          

SubPlan (for Index Scan)

19. 0.493 0.494 ↓ 19.1 1,907 1

ProjectSet (cost=0.00..3.02 rows=100 width=32) (actual time=0.345..0.494 rows=1,907 loops=1)

20. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

21.          

Initplan (for Sort)

22. 0.301 0.301 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.301..0.301 rows=1 loops=1)

23. 0.373 0.373 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.373..0.373 rows=1 loops=1)

24. 0.269 0.269 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.268..0.269 rows=1 loops=1)

25. 0.231 0.231 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.231..0.231 rows=1 loops=1)

26. 0.239 0.239 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.239..0.239 rows=1 loops=1)

27. 6.271 10,324.702 ↑ 236.0 1 1

Nested Loop Left Join (cost=1.24..6,924.74 rows=236 width=50) (actual time=300.555..10,324.702 rows=1 loops=1)

  • Join Filter: ((sl.idtarifnet = slrtariflist.idtarifnet) AND (sl.nrank = slrtarif.nrank))
  • Rows Removed by Join Filter: 267
28. 0.078 10,318.431 ↑ 236.0 1 1

Nested Loop Left Join (cost=0.57..5,485.55 rows=236 width=61) (actual time=294.284..10,318.431 rows=1 loops=1)

  • Join Filter: ((sl.idscalewages = slrscalewageshist.idscalewages) AND (sl.nranksw = slrscalewageslist.nranksw))
  • Rows Removed by Join Filter: 164
29. 0.012 10,309.588 ↑ 236.0 1 1

Nested Loop Left Join (cost=0.42..5,117.38 rows=236 width=71) (actual time=288.549..10,309.588 rows=1 loops=1)

30. 10,307.795 10,307.795 ↑ 236.0 1 1

CTE Scan on slrstaffpostemp t (cost=0.00..4.72 rows=236 width=58) (actual time=286.763..10,307.795 rows=1 loops=1)

31. 0.481 1.781 ↑ 1.0 1 1

Index Scan using fk_slrstaffposlist_staffpos on slrstaffposlist sl (cost=0.42..21.65 rows=1 width=45) (actual time=1.778..1.781 rows=1 loops=1)

  • Index Cond: (t.id = idstaffpos)
  • Filter: ($9 AND (alternatives: SubPlan 10 or hashed SubPlan 13) AND (COALESCE(t.dend, cmn.todate('31.01.2020 00:00:00'::character varying)) >= dbegin) AND (COALESCE(t.dend, cmn.todate('31.01.2020 00:00:00'::character varying)) <= COALESCE(dend, cmn.todate('31.01.2020 00:00:00'::character varying))))
32.          

SubPlan (for Index Scan)

33. 0.736 1.300 ↑ 1.0 1 1

Index Scan using pk_slrstaffpos on slrstaffpos (cost=6.20..8.42 rows=1 width=0) (actual time=1.300..1.300 rows=1 loops=1)

  • Index Cond: (id = sl.idstaffpos)
  • Filter: ($12 AND ((NOT (hashed SubPlan 8)) OR (iddepartment IS NULL)))
34.          

Initplan (for Index Scan)

35. 0.159 0.159 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.159..0.159 rows=1 loops=1)

36.          

SubPlan (for Index Scan)

37. 0.404 0.405 ↓ 19.1 1,907 1

ProjectSet (cost=0.00..3.02 rows=100 width=32) (actual time=0.236..0.405 rows=1,907 loops=1)

38. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on slrstaffpos slrstaffpos_1 (cost=5.78..5,830.57 rows=32,316 width=8) (never executed)

  • Filter: ($15 AND ((NOT (hashed SubPlan 11)) OR (iddepartment IS NULL)))
40.          

Initplan (for Seq Scan)

41. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..2.51 rows=1 width=1) (never executed)

42.          

SubPlan (for Seq Scan)

43. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..3.02 rows=100 width=32) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

45. 0.076 8.765 ↓ 41.2 165 1

Materialize (cost=0.15..351.66 rows=4 width=22) (actual time=1.782..8.765 rows=165 loops=1)

46. 0.061 8.689 ↓ 41.2 165 1

Nested Loop (cost=0.15..351.64 rows=4 width=22) (actual time=1.779..8.689 rows=165 loops=1)

47. 0.961 1.896 ↓ 9.0 9 1

Seq Scan on slrscalewageshist (cost=0.00..139.47 rows=1 width=36) (actual time=0.884..1.896 rows=9 loops=1)

  • Filter: ($47 AND (alternatives: SubPlan 47 or hashed SubPlan 49) AND (cmn.todate('31.01.2020 00:00:00'::character varying) >= dperiodbegin) AND (cmn.todate('31.01.2020 00:00:00'::character varying) <= COALESCE(dperiodend, cmn.todate('31.01.2020 00:00:00'::character varying))))
  • Rows Removed by Filter: 8
48.          

SubPlan (for Seq Scan)

49. 0.720 0.935 ↑ 1.0 1 17

Index Scan using pk_slrscalewages on slrscalewages slrscalewages_4 (cost=2.65..7.38 rows=1 width=0) (actual time=0.055..0.055 rows=1 loops=17)

  • Index Cond: (id = slrscalewageshist.idscalewages)
  • Filter: ($49 AND ((idgroupwages <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrscalewages'::name, '1402001'::numeric))) OR (idgroupwages IS NULL)))
50.          

Initplan (for Index Scan)

51. 0.215 0.215 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.215..0.215 rows=1 loops=1)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on slrscalewages slrscalewages_5 (cost=2.51..26.24 rows=4 width=18) (never executed)

  • Filter: ($51 AND ((idgroupwages <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrscalewages'::name, '1402001'::numeric))) OR (idgroupwages IS NULL)))
53.          

Initplan (for Seq Scan)

54. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..2.51 rows=1 width=1) (never executed)

55. 0.627 6.732 ↓ 4.5 18 9

Index Scan using fk_slrscalewageslist_sclwshist on slrscalewageslist (cost=0.15..212.14 rows=4 width=10) (actual time=0.140..0.748 rows=18 loops=9)

  • Index Cond: (idscalewageshist = slrscalewageshist.id)
  • Filter: ($34 AND (alternatives: SubPlan 38 or hashed SubPlan 44))
56.          

SubPlan (for Index Scan)

57. 0.464 6.105 ↑ 1.0 1 165

Index Scan using pk_slrscalewageshist on slrscalewageshist slrscalewageshist_1 (cost=2.65..12.25 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=165)

  • Index Cond: (id = slrscalewageslist.idscalewageshist)
  • Filter: ($36 AND (alternatives: SubPlan 35 or hashed SubPlan 37))
58.          

Initplan (for Index Scan)

59. 0.196 0.196 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.196..0.196 rows=1 loops=1)

60.          

SubPlan (for Index Scan)

61. 5.232 5.445 ↑ 1.0 1 165

Index Scan using pk_slrscalewages on slrscalewages (cost=2.65..7.38 rows=1 width=0) (actual time=0.033..0.033 rows=1 loops=165)

  • Index Cond: (id = slrscalewageshist_1.idscalewages)
  • Filter: ($38 AND ((idgroupwages <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrscalewages'::name, '1402001'::numeric))) OR (idgroupwages IS NULL)))
62.          

Initplan (for Index Scan)

63. 0.213 0.213 ↑ 1.0 1 1

Result (cost=0.00..2.51 rows=1 width=1) (actual time=0.212..0.213 rows=1 loops=1)