explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BSdf

Settings
# exclusive inclusive rows x rows loops node
1. 292.854 8,923.102 ↓ 4,700.5 122,214 1

Hash Join (cost=188,755.97..188,877.52 rows=26 width=25) (actual time=7,601.217..8,923.102 rows=122,214 loops=1)

  • Hash Cond: (edu.edu_level_id = t.id)
2.          

CTE person_data

3. 404.888 8,262.282 ↓ 49.3 251,843 1

Unique (cost=188,729.35..188,754.88 rows=5,107 width=12) (actual time=7,601.151..8,262.282 rows=251,843 loops=1)

4.          

Initplan (for Unique)

5. 0.139 0.139 ↑ 10.0 1 1

Seq Scan on m_years t_1 (cost=0.00..3.28 rows=10 width=4) (actual time=0.029..0.139 rows=1 loops=1)

  • Filter: ((to_date('20.03.2020'::text, 'dd.mm.yyyy'::text) >= begin) AND (to_date('20.03.2020'::text, 'dd.mm.yyyy'::text) <= "end"))
  • Rows Removed by Filter: 113
6. 626.670 7,857.255 ↓ 60.0 306,535 1

Sort (cost=188,726.07..188,738.83 rows=5,107 width=12) (actual time=7,601.148..7,857.255 rows=306,535 loops=1)

  • Sort Key: p.childreestr_id
  • Sort Method: quicksort Memory: 26657kB
7. 1,416.936 7,230.585 ↓ 60.0 306,535 1

Hash Join (cost=66,480.30..188,411.52 rows=5,107 width=12) (actual time=1,185.354..7,230.585 rows=306,535 loops=1)

  • Hash Cond: ((p_1.cid)::numeric = t_3.catalog)
8. 1,145.418 5,813.103 ↓ 30.0 306,535 1

Nested Loop (cost=66,474.62..188,277.44 rows=10,215 width=20) (actual time=1,184.775..5,813.103 rows=306,535 loops=1)

9. 682.745 2,521.940 ↓ 30.0 306,535 1

Hash Join (cost=66,474.19..177,027.08 rows=10,215 width=16) (actual time=1,184.748..2,521.940 rows=306,535 loops=1)

  • Hash Cond: (p.childreestr_id = t_2.id)
10. 660.396 1,023.169 ↓ 30.0 306,535 1

Bitmap Heap Scan on m_person_education p (cost=45,677.77..156,090.20 rows=10,215 width=8) (actual time=368.182..1,023.169 rows=306,535 loops=1)

  • Recheck Cond: (((f_exclude_date > to_date('20.03.2020'::text, 'dd.mm.yyyy'::text)) OR (f_exclude_date IS NULL)) AND (orgreestr_id = ANY (get_orgreestr_ids_by_permision_and_filter(1352, ('{1481}'::text[])::integer[], '{}'::integer[], ('{}'::text[])::integer[]))))
  • Filter: ((f_enroll_date <= to_date('20.03.2020'::text, 'dd.mm.yyyy'::text)) AND ($0 = ANY (learning_years)))
  • Rows Removed by Filter: 20780
  • Heap Blocks: exact=24018
11. 3.559 362.773 ↓ 0.0 0 1

BitmapAnd (cost=45,677.77..45,677.77 rows=209,067 width=0) (actual time=362.773..362.773 rows=0 loops=1)

12. 0.004 30.288 ↓ 0.0 0 1

BitmapOr (cost=10,599.28..10,599.28 rows=329,241 width=0) (actual time=30.288..30.288 rows=0 loops=1)

13. 0.033 0.033 ↑ 521.0 1 1

Bitmap Index Scan on m_person_education_f_exclude_date_idx (cost=0.00..20.34 rows=521 width=0) (actual time=0.033..0.033 rows=1 loops=1)

  • Index Cond: (f_exclude_date > to_date('20.03.2020'::text, 'dd.mm.yyyy'::text))
14. 30.251 30.251 ↑ 1.0 327,314 1

Bitmap Index Scan on m_person_education_f_exclude_date_idx (cost=0.00..10,573.83 rows=328,721 width=0) (actual time=30.251..30.251 rows=327,314 loops=1)

  • Index Cond: (f_exclude_date IS NULL)
15. 328.926 328.926 ↓ 1.6 2,066,820 1

Bitmap Index Scan on m_person_education_orgreestr_id_idx (cost=0.00..35,075.68 rows=1,312,424 width=0) (actual time=328.926..328.926 rows=2,066,820 loops=1)

  • Index Cond: (orgreestr_id = ANY (get_orgreestr_ids_by_permision_and_filter(1352, ('{1481}'::text[])::integer[], '{}'::integer[], ('{}'::text[])::integer[])))
16. 390.738 816.026 ↑ 1.0 436,921 1

Hash (cost=15,323.41..15,323.41 rows=437,841 width=12) (actual time=816.026..816.026 rows=436,921 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 24577kB
17. 425.288 425.288 ↑ 1.0 436,921 1

Seq Scan on m_childreestr t_2 (cost=0.00..15,323.41 rows=437,841 width=12) (actual time=0.011..425.288 rows=436,921 loops=1)

18. 2,145.745 2,145.745 ↑ 1.0 1 306,535

Index Scan using m_person_id_idx on m_person p_1 (cost=0.42..1.09 rows=1 width=20) (actual time=0.006..0.007 rows=1 loops=306,535)

  • Index Cond: (id = t_2.f_max_priority)
  • Filter: (f_personbirthday IS NOT NULL)
19. 0.022 0.546 ↓ 4.0 24 1

Hash (cost=5.60..5.60 rows=6 width=5) (actual time=0.546..0.546 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.049 0.524 ↓ 4.0 24 1

HashAggregate (cost=5.54..5.60 rows=6 width=5) (actual time=0.503..0.524 rows=24 loops=1)

  • Group Key: t_3.catalog
21. 0.042 0.475 ↓ 4.0 24 1

Nested Loop (cost=0.00..5.53 rows=6 width=5) (actual time=0.073..0.475 rows=24 loops=1)

  • Join Filter: (t_3.roleid = t1.roleid)
22. 0.387 0.387 ↑ 1.0 1 1

Seq Scan on userroles t1 (cost=0.00..1.38 rows=1 width=5) (actual time=0.051..0.387 rows=1 loops=1)

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
  • Rows Removed by Filter: 18
23. 0.046 0.046 ↓ 4.0 24 1

Seq Scan on urprivs t_3 (cost=0.00..4.08 rows=6 width=10) (actual time=0.017..0.046 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
24. 8,630.215 8,630.215 ↓ 49.3 251,843 1

CTE Scan on person_data edu (cost=0.00..102.14 rows=5,107 width=12) (actual time=7,601.155..8,630.215 rows=251,843 loops=1)

25. 0.005 0.033 ↑ 1.0 1 1

Hash (cost=1.07..1.07 rows=1 width=13) (actual time=0.033..0.033 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on m_actionsedulevels t (cost=0.00..1.07 rows=1 width=13) (actual time=0.026..0.028 rows=1 loops=1)

  • Filter: ((code)::text = '1'::text)
  • Rows Removed by Filter: 5
Planning time : 59.673 ms
Execution time : 9,005.117 ms