explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WRVc

Settings
# exclusive inclusive rows x rows loops node
1. 1,122.557 18,729.717 ↓ 335.0 9,716 1

Result (cost=408,576.48..408,591.41 rows=29 width=397) (actual time=17,717.098..18,729.717 rows=9,716 loops=1)

2.          

CTE cdepfilterrecurs

3. 26.593 12,396.325 ↓ 1.2 58,376 1

Recursive Union (cost=5.56..367,420.21 rows=49,068 width=12) (actual time=0.715..12,396.325 rows=58,376 loops=1)

4. 3,104.151 3,104.421 ↓ 8.8 9,716 1

Index Scan using ix_slrdepartment_f_dt_lst_hd on slrdepartment t (cost=5.56..46,654.29 rows=1,098 width=12) (actual time=0.713..3,104.421 rows=9,716 loops=1)

  • Index Cond: ((idfactory = '1'::numeric) AND (dbegin <= cmn.todate('31.01.2020 00-00-00'::character varying)))
  • Filter: ($2 AND (id IS NOT NULL) AND $1 AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)) AND (COALESCE(dend, cmn.todate('01.01.2020 00-00-00'::character varying)) >= cmn.todate('01.01.2020 00-00-00'::character varying)))
  • Rows Removed by Filter: 5366
5.          

Initplan (for Index Scan)

6. 0.122 0.122 ↑ 1.0 1 1

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

7. 0.148 0.148 ↑ 1.0 1 1

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

8. 1,024.451 9,265.311 ↓ 1.1 5,407 9

Nested Loop (cost=2.80..31,978.46 rows=4,797 width=12) (actual time=0.311..1,029.479 rows=5,407 loops=9)

9.          

Initplan (for Nested Loop)

10. 0.250 0.250 ↑ 1.0 1 1

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

11. 9.594 9.594 ↑ 1.7 6,486 9

WorkTable Scan on cdepfilterrecurs pr (cost=0.00..219.60 rows=10,980 width=18) (actual time=0.001..1.066 rows=6,486 loops=9)

12. 8,231.016 8,231.016 ↑ 1.0 1 58,376

Index Scan using pk_slrdepartment on slrdepartment r (cost=0.29..2.89 rows=1 width=12) (actual time=0.141..0.141 rows=1 loops=58,376)

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

CTE cdepfilter

14. 58.653 12,495.694 ↓ 48.6 9,716 1

HashAggregate (cost=1,104.03..1,106.03 rows=200 width=18) (actual time=12,488.811..12,495.694 rows=9,716 loops=1)

  • Group Key: cdepfilterrecurs.id
15. 12,437.041 12,437.041 ↓ 1.2 58,376 1

CTE Scan on cdepfilterrecurs (cost=0.00..981.36 rows=49,068 width=18) (actual time=0.717..12,437.041 rows=58,376 loops=1)

16.          

Initplan (for Result)

17. 0.342 0.342 ↑ 1.0 1 1

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

18. 105.643 17,606.818 ↓ 335.0 9,716 1

Sort (cost=40,047.72..40,047.80 rows=29 width=371) (actual time=17,603.905..17,606.818 rows=9,716 loops=1)

  • Sort Key: slrdepartment.slevel
  • Sort Method: quicksort Memory: 6152kB
19. 12.686 17,501.175 ↓ 335.0 9,716 1

Merge Right Join (cost=966.13..40,046.24 rows=29 width=371) (actual time=14,716.164..17,501.175 rows=9,716 loops=1)

  • Merge Cond: (slrdepartment_1.idparent = slrdepartment.id)
20. 6.930 2,759.802 ↑ 1.0 3,370 1

Unique (cost=2.80..39,035.52 rows=3,449 width=42) (actual time=0.514..2,759.802 rows=3,370 loops=1)

21.          

Initplan (for Unique)

22. 0.223 0.223 ↑ 1.0 1 1

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

23. 2,752.649 2,752.649 ↓ 2.0 13,178 1

Index Only Scan using ak_slrdepartment_parent_id_br on slrdepartment slrdepartment_1 (cost=0.29..39,016.53 rows=6,589 width=42) (actual time=0.512..2,752.649 rows=13,178 loops=1)

  • Filter: ($8 AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)))
  • Rows Removed by Filter: 1904
  • Heap Fetches: 15082
24. 4.372 14,728.687 ↓ 335.0 9,716 1

Materialize (cost=963.33..967.40 rows=29 width=268) (actual time=14,715.292..14,728.687 rows=9,716 loops=1)

25. 5.803 14,724.315 ↓ 335.0 9,716 1

Merge Left Join (cost=963.33..967.32 rows=29 width=268) (actual time=14,715.288..14,724.315 rows=9,716 loops=1)

  • Merge Cond: (slrdepartment.id = slrclose.iddepartment)
26. 23.099 14,718.448 ↓ 335.0 9,716 1

Sort (cost=962.21..962.28 rows=29 width=266) (actual time=14,715.215..14,718.448 rows=9,716 loops=1)

  • Sort Key: slrdepartment.id
  • Sort Method: quicksort Memory: 4729kB
27. 202.469 14,695.349 ↓ 335.0 9,716 1

Nested Loop (cost=0.29..961.50 rows=29 width=266) (actual time=12,489.600..14,695.349 rows=9,716 loops=1)

28. 12,501.100 12,501.100 ↓ 48.6 9,716 1

CTE Scan on cdepfilter (cost=0.00..4.00 rows=200 width=18) (actual time=12,488.814..12,501.100 rows=9,716 loops=1)

29. 1,991.780 1,991.780 ↑ 1.0 1 9,716

Index Scan using pk_slrdepartment on slrdepartment (cost=0.29..4.79 rows=1 width=266) (actual time=0.205..0.205 rows=1 loops=9,716)

  • Index Cond: (id = cdepfilter.id)
  • Filter: ($7 AND (idfactory = '1'::numeric) AND ((lhide)::text = 'N'::text) AND ((id <> ALL (adm_pfuncafi.gettblaccessforattr_notin(SESSION_USER, 'slrdepartment'::name, '-122001'::numeric))) OR (id IS NULL)) AND (dbegin <= cmn.todate('31.01.2020 00-00-00'::character varying)) AND (COALESCE(dend, cmn.todate('01.01.2020 00-00-00'::character varying)) >= cmn.todate('01.01.2020 00-00-00'::character varying)))
30. 0.064 0.064 ↓ 0.0 0 1

Index Scan using ak_slrclose_department_period on slrclose (cost=0.40..4.23 rows=1 width=9) (actual time=0.064..0.064 rows=0 loops=1)

  • Index Cond: (dperiod = cmn.todate('01.12.2019 00-00-00'::character varying))
Planning time : 5.764 ms
Execution time : 18,750.582 ms