explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pJMm

Settings
# exclusive inclusive rows x rows loops node
1. 150.895 14,057.695 ↓ 2.0 63,069 1

HashAggregate (cost=146,242.23..146,556.62 rows=31,439 width=16) (actual time=14,002.127..14,057.695 rows=63,069 loops=1)

  • Group Key: t_3.f_link2reestr, p.f_link2reestr
2. 230.228 13,906.800 ↓ 2.0 63,098 1

Nested Loop (cost=35,819.63..146,085.03 rows=31,439 width=16) (actual time=1,593.262..13,906.800 rows=63,098 loops=1)

3. 181.375 13,246.516 ↓ 28.9 71,676 1

Hash Join (cost=35,819.49..143,522.24 rows=2,482 width=20) (actual time=1,593.246..13,246.516 rows=71,676 loops=1)

  • Hash Cond: ((p.cid)::numeric = t.catalog)
4. 203.634 13,064.597 ↓ 14.4 71,676 1

Nested Loop (cost=35,813.93..143,491.81 rows=4,964 width=28) (actual time=1,592.682..13,064.597 rows=71,676 loops=1)

5. 3,921.786 12,267.795 ↓ 3.7 74,146 1

Nested Loop (cost=35,813.50..131,879.26 rows=19,957 width=20) (actual time=1,592.657..12,267.795 rows=74,146 loops=1)

  • Join Filter: (((now() >= t_4.begin) AND (now() <= t_4."end") AND (f_get_learning_status_by_date(t_2.f_enroll_date, t_2.f_exclude_date, (now())::date) IS TRUE)) OR (((now() < t_4.begin) OR (now() > t_4."end")) AND (t_2.f_enroll_date < t_4."end") AND ((t_2.f_exclude_date IS NULL) OR (t_2.f_exclude_date > t_4.begin))))
  • Rows Removed by Join Filter: 710,456
6. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on m_years t_4 (cost=0.00..4.71 rows=1 width=8) (actual time=0.015..0.029 rows=1 loops=1)

  • Filter: ((id = 6) AND (((now() >= begin) AND (now() <= "end")) OR (now() < begin) OR (now() > "end")))
  • Rows Removed by Filter: 113
7. 1,855.731 8,345.980 ↓ 6.3 784,602 1

Hash Join (cost=35,813.50..95,808.70 rows=124,365 width=24) (actual time=1,529.684..8,345.980 rows=784,602 loops=1)

  • Hash Cond: (t_1.f_org = t_3.id)
8. 3,550.720 6,485.317 ↓ 3.5 1,753,153 1

Hash Join (cost=34,987.38..91,873.93 rows=497,332 width=24) (actual time=1,524.718..6,485.317 rows=1,753,153 loops=1)

  • Hash Cond: (t_1.f_edu_do = t_2.id)
9. 1,410.249 1,410.249 ↑ 1.0 1,989,326 1

Seq Scan on m_preschoolchildeducation2child t_1 (cost=0.00..44,453.26 rows=1,989,326 width=24) (actual time=0.013..1,410.249 rows=1,989,326 loops=1)

10. 671.332 1,524.348 ↓ 3.2 773,704 1

Hash (cost=31,994.43..31,994.43 rows=239,436 width=12) (actual time=1,524.348..1,524.348 rows=773,704 loops=1)

  • Buckets: 1,048,576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 41,157kB
11. 853.016 853.016 ↓ 3.2 773,704 1

Seq Scan on m_preschoolchildeducation t_2 (cost=0.00..31,994.43 rows=239,436 width=12) (actual time=0.064..853.016 rows=773,704 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 184,039
12. 0.860 4.932 ↓ 1.1 1,097 1

Hash (cost=813.95..813.95 rows=974 width=16) (actual time=4.932..4.932 rows=1,097 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
13. 4.072 4.072 ↓ 1.1 1,097 1

Seq Scan on m_organization t_3 (cost=0.00..813.95 rows=974 width=16) (actual time=0.006..4.072 rows=1,097 loops=1)

  • Filter: (f_uptodate AND (NOT deleted))
  • Rows Removed by Filter: 2,798
14. 593.168 593.168 ↑ 1.0 1 74,146

Index Scan using m_person_id_idx on m_person p (cost=0.42..0.57 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=74,146)

  • Index Cond: (id = t_1.f_child)
  • Filter: (f_uptodate AND (NOT deleted) AND (f_link2reestr IS NOT NULL))
15. 0.025 0.544 ↓ 24.0 24 1

Hash (cost=5.54..5.54 rows=1 width=5) (actual time=0.544..0.544 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.045 0.519 ↓ 24.0 24 1

HashAggregate (cost=5.53..5.54 rows=1 width=5) (actual time=0.504..0.519 rows=24 loops=1)

  • Group Key: t.catalog
17. 0.036 0.474 ↓ 24.0 24 1

Nested Loop (cost=0.00..5.53 rows=1 width=5) (actual time=0.084..0.474 rows=24 loops=1)

  • Join Filter: (t.roleid = t1.roleid)
18. 0.391 0.391 ↑ 1.0 1 1

Seq Scan on userroles t1 (cost=0.00..1.38 rows=1 width=18) (actual time=0.061..0.391 rows=1 loops=1)

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

Seq Scan on urprivs t (cost=0.00..4.08 rows=6 width=10) (actual time=0.020..0.047 rows=24 loops=1)

  • Filter: ((catalog IS NOT NULL) AND ((unitcode)::text = 'orgreestr'::text))
  • Rows Removed by Filter: 144
20. 430.056 430.056 ↑ 13.0 1 71,676

Index Scan using m_years_end_idx on m_years t_5 (cost=0.14..0.90 rows=13 width=8) (actual time=0.003..0.006 rows=1 loops=71,676)

  • Index Cond: (t_2.f_enroll_date <= "end")
  • Filter: (t_2.f_enroll_date >= begin)
  • Rows Removed by Filter: 12
Planning time : 3.518 ms
Execution time : 14,096.169 ms