explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MOJO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 203.921 13,400.247 ↓ 19.6 143,869 1

Unique (cost=230,179.20..230,215.97 rows=7,355 width=24) (actual time=13,082.299..13,400.247 rows=143,869 loops=1)

2. 335.808 13,196.326 ↓ 19.6 144,012 1

Sort (cost=230,179.20..230,197.59 rows=7,355 width=24) (actual time=13,082.297..13,196.326 rows=144,012 loops=1)

  • Sort Key: p.f_link2reestr, t_5.priority DESC, t_3.f_enroll_date DESC
  • Sort Method: quicksort Memory: 17395kB
3. 248.911 12,860.518 ↓ 19.6 144,012 1

Hash Join (cost=68,710.76..229,706.84 rows=7,355 width=24) (actual time=3,137.982..12,860.518 rows=144,012 loops=1)

  • Hash Cond: (t_3.f_data_source = t_5.id)
4. 192.451 12,611.578 ↓ 19.6 144,012 1

Nested Loop (cost=68,709.65..229,604.60 rows=7,355 width=28) (actual time=3,137.942..12,611.578 rows=144,012 loops=1)

5. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on m_years t (cost=0.00..2.42 rows=1 width=4) (actual time=0.008..0.023 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 113
6. 386.147 12,419.104 ↓ 19.6 144,012 1

Hash Join (cost=68,709.65..229,528.62 rows=7,355 width=36) (actual time=3,137.930..12,419.104 rows=144,012 loops=1)

  • Hash Cond: ((p.cid)::numeric = t_1.catalog)
7. 469.198 12,032.420 ↓ 9.8 144,012 1

Nested Loop (cost=68,703.97..229,448.36 rows=14,709 width=44) (actual time=3,137.372..12,032.420 rows=144,012 loops=1)

8. 510.443 10,197.710 ↓ 11.2 170,689 1

Nested Loop (cost=68,703.55..221,243.03 rows=15,290 width=36) (actual time=2,254.194..10,197.710 rows=170,689 loops=1)

9. 388.246 7,376.499 ↓ 2.4 192,564 1

Hash Join (cost=68,703.12..157,936.96 rows=80,075 width=36) (actual time=2,243.102..7,376.499 rows=192,564 loops=1)

  • Hash Cond: (t_2.f_org = t_4.id)
10. 3,076.191 6,982.596 ↑ 1.2 290,234 1

Hash Join (cost=67,748.91..154,905.39 rows=340,431 width=36) (actual time=2,236.457..6,982.596 rows=290,234 loops=1)

  • Hash Cond: (t_2.f_edu_oo = _edu.id)
11. 3,436.880 3,436.880 ↑ 1.0 3,209,394 1

Seq Scan on m_mainchildeducation2child t_2 (cost=0.00..71,716.94 rows=3,209,394 width=24) (actual time=0.009..3,436.880 rows=3,209,394 loops=1)

12. 146.848 469.525 ↑ 1.0 171,931 1

Hash (cost=65,585.75..65,585.75 rows=173,053 width=12) (actual time=469.525..469.525 rows=171,931 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9436kB
13. 245.561 322.677 ↑ 1.0 171,931 1

Bitmap Heap Scan on m_mainchildeducation _edu (cost=3,241.59..65,585.75 rows=173,053 width=12) (actual time=81.396..322.677 rows=171,931 loops=1)

  • Recheck Cond: (f_study_year = 6)
  • Heap Blocks: exact=18307
14. 77.116 77.116 ↑ 1.0 171,931 1

Bitmap Index Scan on m_mainchildeducation_f_study_year_idx (cost=0.00..3,198.32 rows=173,053 width=0) (actual time=77.116..77.116 rows=171,931 loops=1)

  • Index Cond: (f_study_year = 6)
15. 0.877 5.657 ↑ 1.0 1,046 1

Hash (cost=940.98..940.98 rows=1,058 width=16) (actual time=5.657..5.657 rows=1,046 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
16. 4.780 4.780 ↑ 1.0 1,046 1

Seq Scan on m_organization t_4 (cost=0.00..940.98 rows=1,058 width=16) (actual time=0.011..4.780 rows=1,046 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 3447
17. 2,310.768 2,310.768 ↑ 1.0 1 192,564

Index Scan using m_mainchildeducation_id_idx on m_mainchildeducation t_3 (cost=0.43..0.78 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=192,564)

  • Index Cond: (id = t_2.f_edu_oo)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE))
  • Rows Removed by Filter: 0
18. 1,365.512 1,365.512 ↑ 1.0 1 170,689

Index Scan using m_person_id_idx on m_person p (cost=0.42..0.53 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=170,689)

  • Index Cond: (id = t_2.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
19. 0.024 0.537 ↓ 4.0 24 1

Hash (cost=5.60..5.60 rows=6 width=5) (actual time=0.537..0.537 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.495..0.513 rows=24 loops=1)

  • Group Key: t_1.catalog
21. 0.039 0.464 ↓ 4.0 24 1

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

  • Join Filter: (t_1.roleid = t1.roleid)
22. 0.376 0.376 ↑ 1.0 1 1

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

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

Seq Scan on urprivs t_1 (cost=0.00..4.08 rows=6 width=10) (actual time=0.019..0.049 rows=24 loops=1)

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

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.029..0.029 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.023 0.023 ↑ 1.0 5 1

Seq Scan on m_dataresource t_5 (cost=0.00..1.05 rows=5 width=8) (actual time=0.018..0.023 rows=5 loops=1)

Planning time : 6.250 ms
Execution time : 13,488.971 ms