explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NSTS

Settings
# exclusive inclusive rows x rows loops node
1. 9,813.503 9,813.503 ↑ 236.0 1 1

CTE Scan on slrstaffpostemp t (cost=7,779.12..7,783.84 rows=236 width=18) (actual time=290.670..9,813.503 rows=1 loops=1)

2.          

CTE slrdepartmentrecurs

3. 12.683 2,674.287 ↓ 77.1 13,176 1

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

4. 0.413 1.308 ↑ 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.284..1.308 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.895 0.895 ↑ 1.0 1 1

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

7. 21.410 2,660.296 ↓ 96.9 1,647 8

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

8.          

Initplan (for Nested Loop)

9. 0.158 0.158 ↑ 1.0 1 1

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

10. 3.528 3.528 ↓ 164.7 1,647 8

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

11. 2,635.200 2,635.200 ↑ 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.048..0.200 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. 17.326 9,813.496 ↑ 236.0 1 1

Nested Loop (cost=6.20..6,159.95 rows=236 width=1,621) (actual time=290.664..9,813.496 rows=1 loops=1)

14.          

Initplan (for Nested Loop)

15. 0.374 0.374 ↑ 1.0 1 1

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

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

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

17. 7,101.345 7,101.864 ↓ 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.539..0.539 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.518 0.519 ↓ 19.1 1,907 1

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

Planning time : 11.858 ms
Execution time : 9,825.790 ms