explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tm60 : Optimization for: Optimization for: plan #MOJO; plan #qaIa

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 239.759 10,223.442 ↓ 4.4 143,869 1

Unique (cost=232,842.50..233,005.01 rows=32,502 width=16) (actual time=9,822.997..10,223.442 rows=143,869 loops=1)

2. 417.659 9,983.683 ↓ 6.5 211,517 1

Sort (cost=232,842.50..232,923.75 rows=32,502 width=16) (actual time=9,822.994..9,983.683 rows=211,517 loops=1)

  • Sort Key: p.f_link2reestr, t_2.priority DESC, edu.f_enroll_date DESC
  • Sort Method: quicksort Memory: 16059kB
3. 347.064 9,566.024 ↓ 6.5 211,517 1

Hash Join (cost=110,016.85..230,406.76 rows=32,502 width=16) (actual time=2,184.655..9,566.024 rows=211,517 loops=1)

  • Hash Cond: (edu.f_data_source = t_2.id)
4. 270.912 9,218.939 ↓ 6.5 211,517 1

Nested Loop (cost=110,015.73..229,958.75 rows=32,502 width=20) (actual time=2,184.622..9,218.939 rows=211,517 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. 467.569 8,948.004 ↓ 6.5 211,517 1

Hash Join (cost=110,015.73..229,631.30 rows=32,502 width=28) (actual time=2,184.609..8,948.004 rows=211,517 loops=1)

  • Hash Cond: ((p.cid)::numeric = t_1.catalog)
7. 839.358 8,479.902 ↓ 3.3 211,517 1

Nested Loop (cost=110,010.05..229,296.02 rows=65,004 width=36) (actual time=2,184.052..8,479.902 rows=211,517 loops=1)

8. 2,942.310 6,380.099 ↓ 3.9 252,089 1

Hash Join (cost=110,009.63..194,411.84 rows=65,004 width=28) (actual time=2,183.790..6,380.099 rows=252,089 loops=1)

  • Hash Cond: (edu2cd.f_edu_oo = edu.id)
9. 2,467.893 2,467.893 ↑ 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.007..2,467.893 rows=3,209,394 loops=1)

  • Filter: (NOT deleted)
10. 154.459 969.896 ↓ 4.4 144,012 1

Hash (cost=109,596.58..109,596.58 rows=33,044 width=24) (actual time=969.896..969.896 rows=144,012 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 11049kB
11. 699.045 815.437 ↓ 4.4 144,012 1

Bitmap Heap Scan on m_mainchildeducation edu (cost=20,554.41..109,596.58 rows=33,044 width=24) (actual time=120.402..815.437 rows=144,012 loops=1)

  • Recheck Cond: (f_study_year = 6)
  • 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: 8576
  • Heap Blocks: exact=17263
12. 3.146 116.392 ↓ 0.0 0 1

BitmapAnd (cost=20,554.41..20,554.41 rows=99,552 width=0) (actual time=116.392..116.392 rows=0 loops=1)

13. 17.831 17.831 ↑ 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=17.831..17.831 rows=171,931 loops=1)

  • Index Cond: (f_study_year = 6)
14. 95.415 95.415 ↑ 1.0 934,094 1

Bitmap Index Scan on m_mainchildeducation_f_uptodate_idx (cost=0.00..17,339.31 rows=938,518 width=0) (actual time=95.415..95.415 rows=934,094 loops=1)

  • Index Cond: (f_uptodate = true)
15. 1,260.445 1,260.445 ↑ 1.0 1 252,089

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=252,089)

  • Index Cond: (id = edu2cd.f_child)
16. 0.025 0.533 ↓ 4.0 24 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.050 0.508 ↓ 4.0 24 1

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

  • Group Key: t_1.catalog
18. 0.040 0.458 ↓ 4.0 24 1

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

  • Join Filter: (t_1.roleid = t1.roleid)
19. 0.370 0.370 ↑ 1.0 1 1

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

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

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

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 142
21. 0.006 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
22. 0.015 0.015 ↑ 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.015 rows=5 loops=1)

Planning time : 2.908 ms
Execution time : 10,311.066 ms