#	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: 17,220kB
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: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12,527kB
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: 4,271 Heap Blocks: exact=18,307
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: 1,024 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: 1,024 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

Unique  (cost=262602.09..262885.78 rows=56738 width=16) (actual time=10060.748..10512.878 rows=145641 loops=1)
  ->  Sort  (cost=262602.09..262743.94 rows=56738 width=16) (actual time=10060.745..10250.615 rows=236280 loops=1)
        Sort Key: p.f_link2reestr, t_2.priority DESC, edu.f_enroll_date DESC
        Sort Method: quicksort  Memory: 17220kB
        ->  Hash Join  (cost=110413.69..258122.05 rows=56738 width=16) (actual time=2155.777..9772.505 rows=236280 loops=1)
              Hash Cond: (edu.f_data_source = t_2.id)
              ->  Nested Loop  (cost=110412.58..257340.79 rows=56738 width=20) (actual time=2155.746..9401.764 rows=236280 loops=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
                    ->  Hash Join  (cost=110412.58..256770.98 rows=56738 width=28) (actual time=2155.734..9089.688 rows=236280 loops=1)
                          Hash Cond: ((p.cid)::numeric = t_1.catalog)
                          ->  Nested Loop  (cost=110406.90..256189.92 rows=113476 width=36) (actual time=2155.171..8585.047 rows=236280 loops=1)
                                ->  Hash Join  (cost=110406.47..195293.40 rows=113476 width=28) (actual time=2155.105..6235.689 rows=283995 loops=1)
                                      Hash Cond: (edu2cd.f_edu_oo = edu.id)
                                      ->  Seq Scan on m_mainchildeducation2child edu2cd  (cost=0.00..71716.94 rows=3209394 width=16) (actual time=0.008..2301.522 rows=3209394 loops=1)
                                      ->  Hash  (cost=109685.42..109685.42 rows=57684 width=24) (actual time=963.059..963.059 rows=167660 loops=1)
                                            Buckets: 262144 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 12527kB
                                            ->  Bitmap Heap Scan on m_mainchildeducation edu  (cost=3212.75..109685.42 rows=57684 width=24) (actual time=23.299..790.075 rows=167660 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
                                                  ->  Bitmap Index Scan on m_mainchildeducation_f_study_year_idx  (cost=0.00..3198.32 rows=173053 width=0) (actual time=19.036..19.036 rows=171931 loops=1)
                                                        Index Cond: (f_study_year = 6)
                                ->  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=283995)
                                      Index Cond: (id = edu2cd.f_child)
                          ->  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
                                ->  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
                                      ->  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)
                                            ->  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
                                            ->  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
              ->  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
                    ->  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: 10602.280 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	771.039 ms	7.3 %
Bitmap Index Scan	1	19.036 ms	0.2 %
Hash	3	173.020 ms	1.6 %
Hash Join	3	3,845.930 ms	36.6 %
HashAggregate	1	0.051 ms	0.0 %
Index Scan	1	1,419.975 ms	13.5 %
Nested Loop	3	1,241.480 ms	11.8 %
Seq Scan	5	2,301.974 ms	21.9 %
Sort	1	478.110 ms	4.5 %
Unique	1	262.263 ms	2.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
m_dataresource	1	0.013 ms	0.0 %
Seq Scan	1	0.013 ms	100.0 %
m_mainchildeducation	1	771.039 ms	7.3 %
Bitmap Heap Scan	1	771.039 ms	100.0 %
m_mainchildeducation2child	1	2,301.522 ms	21.9 %
Seq Scan	1	2,301.522 ms	100.0 %
m_person	1	1,419.975 ms	13.5 %
Index Scan	1	1,419.975 ms	100.0 %
m_years	1	0.023 ms	0.0 %
Seq Scan	1	0.023 ms	100.0 %
urprivs	1	0.049 ms	0.0 %
Seq Scan	1	0.049 ms	100.0 %
userroles	1	0.367 ms	0.0 %
Seq Scan	1	0.367 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qaIa : Optimization for: plan #MOJO

Color mode:

Visible columns:

Optimization path:

Optimization(s) for this plan:

Per node type stats

Per table stats