explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nPin

Settings
# exclusive inclusive rows x rows loops node
1. 24.003 19,193.883 ↑ 236.0 1 1

Result (cost=24,695.52..26,738.10 rows=236 width=12,363) (actual time=19,193.881..19,193.883 rows=1 loops=1)

2.          

CTE slrdepartmentrecurs

3. 10.983 2,536.805 ↓ 77.1 13,176 1

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

4. 0.388 1.262 ↑ 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.239..1.262 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.874 0.874 ↑ 1.0 1 1

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

7. 17.827 2,524.560 ↓ 96.9 1,647 8

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

8.          

Initplan (for Nested Loop)

9. 0.197 0.197 ↑ 1.0 1 1

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

10. 3.096 3.096 ↓ 164.7 1,647 8

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

11. 2,503.440 2,503.440 ↑ 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.045..0.190 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. 33.007 4,878.103 ↓ 111.7 26,462 1

Nested Loop (cost=6.20..5,418.67 rows=237 width=1,621) (actual time=41.894..4,878.103 rows=26,462 loops=1)

14.          

Initplan (for Nested Loop)

15. 0.248 0.248 ↑ 1.0 1 1

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

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

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

17. 2,292.201 2,292.624 ↓ 2.0 2 13,176

Index Scan using fk_slrstaffpos_department on slrstaffpos t_3 (cost=3.69..31.63 rows=1 width=1,589) (actual time=0.124..0.174 rows=2 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 (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: 7
18.          

SubPlan (for Index Scan)

19. 0.422 0.423 ↓ 19.1 1,907 1

ProjectSet (cost=0.00..3.02 rows=100 width=32) (actual time=0.254..0.423 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 Result)

22. 0.275 0.275 ↑ 1.0 1 1

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

23. 0.208 0.208 ↑ 1.0 1 1

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

24. 0.191 0.191 ↑ 1.0 1 1

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

25. 0.195 0.195 ↑ 1.0 1 1

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

26. 0.218 0.218 ↑ 1.0 1 1

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

27. 0.532 19,168.793 ↑ 236.0 1 1

Sort (cost=17,645.11..17,645.70 rows=236 width=1,925) (actual time=19,168.792..19,168.793 rows=1 loops=1)

  • Sort Key: t.slevel
  • Sort Method: quicksort Memory: 25kB
28. 14,156.205 19,168.261 ↑ 236.0 1 1

Hash Left Join (cost=10,630.28..17,629.06 rows=236 width=1,925) (actual time=19,099.246..19,168.261 rows=1 loops=1)

  • Hash Cond: (t.id = people.idstaffpos)
29. 4,752.967 5,012.056 ↑ 236.0 1 1

Hash Left Join (cost=13.32..6,997.91 rows=236 width=1,632) (actual time=4,943.038..5,012.056 rows=1 loops=1)

  • Hash Cond: (t.id = tempfree.idstaffpos)
30. 4.023 259.089 ↑ 236.0 1 1

Nested Loop Left Join (cost=1.24..6,985.19 rows=236 width=1,600) (actual time=190.080..259.089 rows=1 loops=1)

  • Join Filter: ((sl.idtarifnet = slrtariflist.idtarifnet) AND (sl.nrank = slrtarif.nrank))
  • Rows Removed by Join Filter: 267
31. 0.057 255.066 ↑ 236.0 1 1

Nested Loop Left Join (cost=0.57..5,546.01 rows=236 width=1,596) (actual time=186.058..255.066 rows=1 loops=1)

  • Join Filter: ((sl.idscalewages = slrscalewageshist.idscalewages) AND (sl.nranksw = slrscalewageslist.nranksw))
  • Rows Removed by Join Filter: 164
32. 0.013 248.453 ↑ 236.0 1 1

Nested Loop Left Join (cost=0.42..5,177.84 rows=236 width=1,584) (actual time=181.664..248.453 rows=1 loops=1)

33. 246.788 246.788 ↑ 236.0 1 1

CTE Scan on slrstaffpostemp t (cost=0.00..65.18 rows=236 width=1,532) (actual time=180.003..246.788 rows=1 loops=1)

  • Filter: (instr((upper((smnemocode)::text))::character varying, '50344533'::character varying) <> 0)
  • Rows Removed by Filter: 26461
34. 0.436 1.652 ↑ 1.0 1 1

Index Scan using fk_slrstaffposlist_staffpos on slrstaffposlist sl (cost=0.42..21.65 rows=1 width=76) (actual time=1.650..1.652 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))))
35.          

SubPlan (for Index Scan)

36. 0.670 1.216 ↑ 1.0 1 1

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

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

Initplan (for Index Scan)

38. 0.182 0.182 ↑ 1.0 1 1

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

39.          

SubPlan (for Index Scan)

40. 0.363 0.364 ↓ 19.1 1,907 1

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

41. 0.001 0.001 ↑ 1.0 1 1

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

42. 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)))
43.          

Initplan (for Seq Scan)

44. 0.000 0.000 ↓ 0.0 0

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

45.          

SubPlan (for Seq Scan)

46. 0.000 0.000 ↓ 0.0 0

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

47. 0.000 0.000 ↓ 0.0 0

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

48. 0.069 6.556 ↓ 41.2 165 1

Materialize (cost=0.15..351.66 rows=4 width=34) (actual time=1.476..6.556 rows=165 loops=1)

49. 0.052 6.487 ↓ 41.2 165 1

Nested Loop (cost=0.15..351.64 rows=4 width=34) (actual time=1.473..6.487 rows=165 loops=1)

50. 0.782 1.530 ↓ 9.0 9 1

Seq Scan on slrscalewageshist (cost=0.00..139.47 rows=1 width=36) (actual time=0.741..1.530 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
51.          

SubPlan (for Seq Scan)

52. 0.577 0.748 ↑ 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.044..0.044 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)))
53.          

Initplan (for Index Scan)

54. 0.171 0.171 ↑ 1.0 1 1

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

55. 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)))
56.          

Initplan (for Seq Scan)

57. 0.000 0.000 ↓ 0.0 0

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

58. 0.450 4.905 ↓ 4.5 18 9

Index Scan using fk_slrscalewageslist_sclwshist on slrscalewageslist (cost=0.15..212.14 rows=4 width=22) (actual time=0.107..0.545 rows=18 loops=9)

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

SubPlan (for Index Scan)

60. 4.298 4.455 ↑ 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.027..0.027 rows=1 loops=165)

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

Initplan (for Index Scan)

62. 0.157 0.157 ↑ 1.0 1 1

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