explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Evs

Settings
# exclusive inclusive rows x rows loops node
1. 251.016 9,205.827 ↓ 218.4 54,611 1

Nested Loop (cost=188,920.86..189,138.98 rows=250 width=53) (actual time=7,600.896..9,205.827 rows=54,611 loops=1)

  • Join Filter: (t.id = ANY (edu.edu_level_ids))
  • Rows Removed by Join Filter: 197232
2.          

CTE person_data

3. 599.618 8,466.877 ↓ 49.3 251,843 1

GroupAggregate (cost=188,729.35..188,920.86 rows=5,107 width=40) (actual time=7,600.821..8,466.877 rows=251,843 loops=1)

  • Group Key: p.childreestr_id, ((date_part('year'::text, age((to_date('20.03.2020'::text, 'dd.mm.yyyy'::text))::timestamp with time zone, (p_1.f_personbirthday)::timestamp with time zone)))::integer)
4.          

Initplan (for GroupAggregate)

5. 0.141 0.141 ↑ 10.0 1 1

Seq Scan on m_years t_1 (cost=0.00..3.28 rows=10 width=4) (actual time=0.030..0.141 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. 682.055 7,867.118 ↓ 60.0 306,535 1

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

  • Sort Key: p.childreestr_id, ((date_part('year'::text, age((to_date('20.03.2020'::text, 'dd.mm.yyyy'::text))::timestamp with time zone, (p_1.f_personbirthday)::timestamp with time zone)))::integer)
  • Sort Method: quicksort Memory: 26657kB
7. 1,380.064 7,185.063 ↓ 60.0 306,535 1

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

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

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

9. 671.380 2,507.054 ↓ 30.0 306,535 1

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

  • Hash Cond: (p.childreestr_id = t_2.id)
10. 663.031 1,035.360 ↓ 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=377.771..1,035.360 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.745 372.329 ↓ 0.0 0 1

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

12. 0.005 32.662 ↓ 0.0 0 1

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

13. 0.040 0.040 ↑ 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.040..0.040 rows=1 loops=1)

  • Index Cond: (f_exclude_date > to_date('20.03.2020'::text, 'dd.mm.yyyy'::text))
14. 32.617 32.617 ↑ 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=32.617..32.617 rows=327,314 loops=1)

  • Index Cond: (f_exclude_date IS NULL)
15. 335.922 335.922 ↓ 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=335.922..335.922 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. 385.892 800.314 ↑ 1.0 436,921 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 24577kB
17. 414.422 414.422 ↑ 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.010..414.422 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.027 0.540 ↓ 4.0 24 1

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

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

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

  • Group Key: t_3.catalog
21. 0.045 0.464 ↓ 4.0 24 1

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

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

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

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

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

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
24. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: ((code)::text = '2'::text)
  • Rows Removed by Filter: 5
25. 8,954.784 8,954.784 ↓ 49.3 251,843 1

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

Planning time : 59.316 ms
Execution time : 9,246.424 ms