explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RRq

Settings
# exclusive inclusive rows x rows loops node
1. 5,853.250 5,853.250 ↑ 159.0 1 1

CTE Scan on slrstaffpostemp t (cost=3,009.97..3,013.15 rows=159 width=18) (actual time=2,987.712..5,853.250 rows=1 loops=1)

2.          

CTE slrdepartmentrecurs

3. 8.519 2,655.499 ↓ 77.1 13,176 1

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

4. 0.307 0.700 ↑ 1.0 1 1

Index Scan using pk_slrdepartment on slrdepartment t_1 (cost=2.80..7.53 rows=1 width=97) (actual time=0.680..0.700 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.393 0.393 ↑ 1.0 1 1

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

7. 8.119 2,646.280 ↓ 96.9 1,647 8

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

8.          

Initplan (for Nested Loop)

9. 0.121 0.121 ↑ 1.0 1 1

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

10. 2.840 2.840 ↓ 164.7 1,647 8

WorkTable Scan on slrdepartmentrecurs (cost=0.00..0.20 rows=10 width=18) (actual time=0.001..0.355 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.047..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. 10.300 5,853.240 ↑ 159.0 1 1

Nested Loop (cost=12.84..1,390.81 rows=159 width=1,621) (actual time=2,987.704..5,853.240 rows=1 loops=1)

14.          

Initplan (for Nested Loop)

15. 0.145 0.145 ↑ 1.0 1 1

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

16. 0.244 0.244 ↑ 1.0 1 1

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

17. 281.626 5,289.159 ↓ 175.7 13,176 1

Nested Loop (cost=4.13..696.84 rows=75 width=42) (actual time=2,681.680..5,289.159 rows=13,176 loops=1)

18. 20.830 2,688.557 ↓ 77.1 13,176 1

HashAggregate (cost=3.85..5.56 rows=171 width=18) (actual time=2,681.137..2,688.557 rows=13,176 loops=1)

  • Group Key: slrdepartmentrecurs_1.id
19. 2,667.727 2,667.727 ↓ 77.1 13,176 1

CTE Scan on slrdepartmentrecurs slrdepartmentrecurs_1 (cost=0.00..3.42 rows=171 width=18) (actual time=0.683..2,667.727 rows=13,176 loops=1)

20. 2,318.976 2,318.976 ↑ 1.0 1 13,176

Index Scan using pk_slrdepartment on slrdepartment r (cost=0.29..4.08 rows=1 width=24) (actual time=0.176..0.176 rows=1 loops=13,176)

  • Index Cond: (id = slrdepartmentrecurs_1.id)
  • Filter: ($7 AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)))
21. 553.087 553.392 ↓ 0.0 0 13,176

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

  • Index Cond: (iddepartment = r.id)
  • Filter: ($6 AND ((NOT (hashed SubPlan 4)) OR (iddepartment IS NULL)) AND (instr((upper((smnemocode)::text))::character varying, '50344533'::character varying) <> 0))
  • Rows Removed by Filter: 9
22.          

SubPlan (for Index Scan)

23. 0.304 0.305 ↓ 19.1 1,907 1

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

24. 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 : 10.899 ms
Execution time : 5,862.731 ms