explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qaIa : Optimization for: plan #MOJO

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 262.263 10,512.878 ↓ 2.6 145,641 1

Unique (cost=262,602.09..262,885.78 rows=56,738 width=16) (actual time=10,060.748..10,512.878 rows=145,641 loops=1)

2. 478.110 10,250.615 ↓ 4.2 236,280 1

Sort (cost=262,602.09..262,743.94 rows=56,738 width=16) (actual time=10,060.745..10,250.615 rows=236,280 loops=1)

  • Sort Key: p.f_link2reestr, t_2.priority DESC, edu.f_enroll_date DESC
  • Sort Method: quicksort Memory: 17220kB
3. 370.720 9,772.505 ↓ 4.2 236,280 1

Hash Join (cost=110,413.69..258,122.05 rows=56,738 width=16) (actual time=2,155.777..9,772.505 rows=236,280 loops=1)

  • Hash Cond: (edu.f_data_source = t_2.id)
4. 312.053 9,401.764 ↓ 4.2 236,280 1

Nested Loop (cost=110,412.58..257,340.79 rows=56,738 width=20) (actual time=2,155.746..9,401.764 rows=236,280 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. 504.102 9,089.688 ↓ 4.2 236,280 1

Hash Join (cost=110,412.58..256,770.98 rows=56,738 width=28) (actual time=2,155.734..9,089.688 rows=236,280 loops=1)

  • Hash Cond: ((p.cid)::numeric = t_1.catalog)
7. 929.383 8,585.047 ↓ 2.1 236,280 1

Nested Loop (cost=110,406.90..256,189.92 rows=113,476 width=36) (actual time=2,155.171..8,585.047 rows=236,280 loops=1)

8. 2,971.108 6,235.689 ↓ 2.5 283,995 1

Hash Join (cost=110,406.47..195,293.40 rows=113,476 width=28) (actual time=2,155.105..6,235.689 rows=283,995 loops=1)

  • Hash Cond: (edu2cd.f_edu_oo = edu.id)
9. 2,301.522 2,301.522 ↑ 1.0 3,209,394 1

Seq Scan on m_mainchildeducation2child edu2cd (cost=0.00..71,716.94 rows=3,209,394 width=16) (actual time=0.008..2,301.522 rows=3,209,394 loops=1)

10. 172.984 963.059 ↓ 2.9 167,660 1

Hash (cost=109,685.42..109,685.42 rows=57,684 width=24) (actual time=963.059..963.059 rows=167,660 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12527kB
11. 771.039 790.075 ↓ 2.9 167,660 1

Bitmap Heap Scan on m_mainchildeducation edu (cost=3,212.75..109,685.42 rows=57,684 width=24) (actual time=23.299..790.075 rows=167,660 loops=1)

  • Recheck Cond: (f_study_year = 6)
  • Filter: (f_get_learning_status_by_date(f_enroll_date, f_exclude_date, (now())::date) IS TRUE)
  • Rows Removed by Filter: 4271
  • Heap Blocks: exact=18307
12. 19.036 19.036 ↑ 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=19.036..19.036 rows=171,931 loops=1)

  • Index Cond: (f_study_year = 6)
13. 1,419.975 1,419.975 ↑ 1.0 1 283,995

Index Scan using m_person_id_idx on m_person p (cost=0.42..0.53 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=283,995)

  • Index Cond: (id = edu2cd.f_child)
14. 0.028 0.539 ↓ 4.0 24 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.051 0.511 ↓ 4.0 24 1

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

  • Group Key: t_1.catalog
16. 0.044 0.460 ↓ 4.0 24 1

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

  • Join Filter: (t_1.roleid = t1.roleid)
17. 0.367 0.367 ↑ 1.0 1 1

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

  • Filter: (sysuser = (core.f_sys_get_config('sysuser'::text))::numeric)
  • Rows Removed by Filter: 18
18. 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
19. 0.008 0.021 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.013 0.013 ↑ 1.0 5 1

Seq Scan on m_dataresource t_2 (cost=0.00..1.05 rows=5 width=8) (actual time=0.009..0.013 rows=5 loops=1)

Planning time : 2.913 ms
Execution time : 10,602.280 ms