#	exclusive	inclusive	rows x	rows	loops	node
1.	0.037	101.600	↑ 30.7	237	1	→ Subquery Scan on test (cost=956,680.70..956,989.59 rows=7,268 width=328) (actual time=101.484..101.600 rows=237 loops=1)
2.	0.072	101.563	↑ 30.7	237	1	→ Unique (cost=956,680.70..956,916.91 rows=7,268 width=218) (actual time=101.481..101.563 rows=237 loops=1)
3.	1.058	101.491	↑ 30.7	237	1	→ Sort (cost=956,680.70..956,698.87 rows=7,268 width=218) (actual time=101.480..101.491 rows=237 loops=1) Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, ((SubPlan 1)), ((SubPlan 2)), i.ixn_prose_txt, ((SubPlan 3)), ((SubPlan 4)), i.id, ( (...) Sort Method: quicksort Memory: 165kB
4.	1.891	100.433	↑ 30.7	237	1	→ GroupAggregate (cost=452,428.44..956,214.56 rows=7,268 width=218) (actual time=95.523..100.433 rows=237 loops=1) Group Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5))
5.	2.019	95.461	↑ 14.3	510	1	→ Sort (cost=452,428.44..452,446.61 rows=7,268 width=218) (actual time=95.427..95.461 rows=510 loops=1) Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5)) Sort Method: quicksort Memory: 306kB
6.	0.792	93.442	↑ 14.3	510	1	→ Nested Loop (cost=20,087.06..451,962.29 rows=7,268 width=218) (actual time=40.699..93.442 rows=510 loops=1) Join Filter: (ptr.ixn_id = iaxn.ixn_id)
7.	0.194	47.324	↑ 18.0	239	1	→ Nested Loop Left Join (cost=20,086.64..206,072.31 rows=4,293 width=212) (actual time=40.529..47.324 rows=239 loops=1)
8.	0.061	46.652	↑ 18.0	239	1	→ Nested Loop (cost=20,086.21..171,868.77 rows=4,293 width=186) (actual time=40.503..46.652 rows=239 loops=1)
9.	0.104	45.396	↑ 18.0	239	1	→ Nested Loop (cost=20,085.78..137,665.22 rows=4,293 width=156) (actual time=40.482..45.396 rows=239 loops=1)
10.	0.151	44.814	↑ 18.0	239	1	→ Nested Loop (cost=20,085.36..103,461.68 rows=4,293 width=108) (actual time=40.455..44.814 rows=239 loops=1)
11.	0.305	42.751	↑ 18.9	239	1	→ Nested Loop (cost=20,084.93..73,349.34 rows=4,506 width=28) (actual time=40.430..42.751 rows=239 loops=1)
12.	0.056	41.490	↑ 19.8	239	1	→ Nested Loop (cost=20,084.51..48,394.32 rows=4,730 width=24) (actual time=40.405..41.490 rows=239 loops=1)
13.	0.026	0.514	↓ 10.3	31	1	→ HashAggregate (cost=7,354.43..7,354.46 rows=3 width=4) (actual time=0.509..0.514 rows=31 loops=1) Group Key: p.descendant_object_id
14.	0.488	0.488	↑ 25.6	93	1	→ Index Only Scan using ix_dag_path_anc_term on dag_path p (cost=0.56..7,348.48 rows=2,380 width=4) (actual time=0.053..0.488 rows=93 loops=1) Index Cond: (ancestor_object_id = 1053851) Heap Fetches: 93
15.	1.178	40.920	↓ 4.0	8	31	→ Bitmap Heap Scan on phenotype_term_reference ptr (cost=12,730.08..13,679.93 rows=2 width=20) (actual time=1.287..1.320 rows=8 loops=31) Recheck Cond: ((term_id = p.descendant_object_id) AND (term_object_type_id = 2)) Filter: ((source_cd)::text = 'C'::text) Rows Removed by Filter: 46 Heap Blocks: exact=1278
16.	0.145	39.742	↓ 0.0	0	31	→ BitmapAnd (cost=12,730.08..12,730.08 rows=242 width=0) (actual time=1.282..1.282 rows=0 loops=31)
17.	0.372	0.372	↑ 163.6	54	31	→ Bitmap Index Scan on ix_phenotype_term_ref_term_id (cost=0.00..166.71 rows=8,836 width=0) (actual time=0.012..0.012 rows=54 loops=31) Index Cond: (term_id = p.descendant_object_id)
18.	39.225	39.225	↑ 1.1	630,202	1	→ Bitmap Index Scan on ix_phenotype_term_ref_object_type_id (cost=0.00..12,561.73 rows=680,172 width=0) (actual time=39.225..39.225 rows=630,202 loops=1) Index Cond: (term_object_type_id = 2)
19.	0.956	0.956	↑ 1.0	1	239	→ Index Scan using reference_pk on reference r (cost=0.42..5.27 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=239) Index Cond: (id = ptr.reference_id)
20.	1.912	1.912	↑ 1.0	1	239	→ Index Scan using ixn_pk on ixn i (cost=0.43..6.67 rows=1 width=80) (actual time=0.008..0.008 rows=1 loops=239) Index Cond: (id = ptr.ixn_id)
21.	0.478	0.478	↑ 1.0	1	239	→ Index Scan using term_pk on term associatedterm (cost=0.43..7.96 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=239) Index Cond: (id = ptr.term_id)
22.	1.195	1.195	↑ 1.0	1	239	→ Index Scan using term_pk on term phenotypeterm (cost=0.43..7.96 rows=1 width=34) (actual time=0.004..0.005 rows=1 loops=239) Index Cond: (id = ptr.phenotype_id)
23.	0.478	0.478	↑ 1.0	1	239	→ Index Scan using term_pk on term taxonterm (cost=0.43..7.96 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=239) Index Cond: (ptr.taxon_id = id)
24.	0.956	0.956	↑ 1.0	2	239	→ Index Scan using ix_ixn_axn_ixn_id on ixn_axn iaxn (cost=0.42..0.46 rows=2 width=14) (actual time=0.004..0.004 rows=2 loops=239) Index Cond: (ixn_id = i.id)
25.						SubPlan (forNested Loop)
26.	9.180	44.370	↑ 1.0	1	510	→ Aggregate (cost=33.54..33.55 rows=1 width=22) (actual time=0.087..0.087 rows=1 loops=510)
27.	4.436	35.190	↓ 10.0	20	510	→ Nested Loop (cost=0.99..33.53 rows=2 width=22) (actual time=0.007..0.069 rows=20 loops=510)
28.	10.710	10.710	↓ 10.0	20	510	→ Index Scan using ix_phenotype_term_ref_ids on phenotype_term_reference ptr2 (cost=0.56..16.62 rows=2 width=4) (actual time=0.005..0.021 rows=20 loops=510) Index Cond: ((ptr.phenotype_id = phenotype_id) AND (ptr.term_id = term_id)) Filter: ((source_cd)::text = 'I'::text) Rows Removed by Filter: 6
29.	20.044	20.044	↑ 1.0	1	10,022	→ Index Scan using term_pk on term t (cost=0.43..8.45 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=10,022) Index Cond: (id = ptr2.via_term_id)
30.						SubPlan (forGroupAggregate)
31.	0.000	0.000	↑ 1.0	1	237	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)
32.	0.237	3.081	↑ 1.0	1	237	→ Aggregate (cost=35.68..35.69 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=237)
33.	0.474	2.844	↑ 2.0	1	237	→ Sort (cost=35.64..35.64 rows=2 width=34) (actual time=0.011..0.012 rows=1 loops=237) Sort Key: ia.level_seq Sort Method: quicksort Memory: 25kB
34.	0.095	2.370	↑ 2.0	1	237	→ Nested Loop Left Join (cost=1.27..35.63 rows=2 width=34) (actual time=0.009..0.010 rows=1 loops=237)
35.	0.237	1.659	↑ 2.0	1	237	→ Nested Loop Left Join (cost=0.85..18.72 rows=2 width=8) (actual time=0.006..0.007 rows=1 loops=237) Join Filter: (i2.id = ia.ixn_id)
36.	0.474	0.474	↑ 1.0	1	237	→ Index Only Scan using ixn_pk on ixn i2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=237) Index Cond: (id = i.id) Heap Fetches: 237
37.	0.948	0.948	↑ 2.0	1	237	→ Index Scan using ixn_anatomy_pk on ixn_anatomy ia (cost=0.42..10.25 rows=2 width=12) (actual time=0.004..0.004 rows=1 loops=237) Index Cond: (ixn_id = i.id)
38.	0.616	0.616	↑ 1.0	1	308	→ Index Scan using term_pk on term anatomyterm (cost=0.43..8.45 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=308) Index Cond: (ia.anatomy_id = id)
39.	0.000	0.000	↑ 1.0	1	237	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)
40.	0.000	0.000	↑ 1.0	1	237	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)

Subquery Scan on test  (cost=956680.70..956989.59 rows=7268 width=328) (actual time=101.484..101.600 rows=237 loops=1)
  ->  Unique  (cost=956680.70..956916.91 rows=7268 width=218) (actual time=101.481..101.563 rows=237 loops=1)
        ->  Sort  (cost=956680.70..956698.87 rows=7268 width=218) (actual time=101.480..101.491 rows=237 loops=1)
              Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, ((SubPlan 1)), ((SubPlan 2)), i.ixn_prose_txt, ((SubPlan 3)), ((SubPlan 4)), i.id, ( (...)
              Sort Method: quicksort  Memory: 165kB
              ->  GroupAggregate  (cost=452428.44..956214.56 rows=7268 width=218) (actual time=95.523..100.433 rows=237 loops=1)
                    Group Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5))
                    ->  Sort  (cost=452428.44..452446.61 rows=7268 width=218) (actual time=95.427..95.461 rows=510 loops=1)
                          Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5))
                          Sort Method: quicksort  Memory: 306kB
                          ->  Nested Loop  (cost=20087.06..451962.29 rows=7268 width=218) (actual time=40.699..93.442 rows=510 loops=1)
                                Join Filter: (ptr.ixn_id = iaxn.ixn_id)
                                ->  Nested Loop Left Join  (cost=20086.64..206072.31 rows=4293 width=212) (actual time=40.529..47.324 rows=239 loops=1)
                                      ->  Nested Loop  (cost=20086.21..171868.77 rows=4293 width=186) (actual time=40.503..46.652 rows=239 loops=1)
                                            ->  Nested Loop  (cost=20085.78..137665.22 rows=4293 width=156) (actual time=40.482..45.396 rows=239 loops=1)
                                                  ->  Nested Loop  (cost=20085.36..103461.68 rows=4293 width=108) (actual time=40.455..44.814 rows=239 loops=1)
                                                        ->  Nested Loop  (cost=20084.93..73349.34 rows=4506 width=28) (actual time=40.430..42.751 rows=239 loops=1)
                                                              ->  Nested Loop  (cost=20084.51..48394.32 rows=4730 width=24) (actual time=40.405..41.490 rows=239 loops=1)
                                                                    ->  HashAggregate  (cost=7354.43..7354.46 rows=3 width=4) (actual time=0.509..0.514 rows=31 loops=1)
                                                                          Group Key: p.descendant_object_id
                                                                          ->  Index Only Scan using ix_dag_path_anc_term on dag_path p  (cost=0.56..7348.48 rows=2380 width=4) (actual time=0.053..0.488 rows=93 loops=1)
                                                                                Index Cond: (ancestor_object_id = 1053851)
                                                                                Heap Fetches: 93
                                                                    ->  Bitmap Heap Scan on phenotype_term_reference ptr  (cost=12730.08..13679.93 rows=2 width=20) (actual time=1.287..1.320 rows=8 loops=31)
                                                                          Recheck Cond: ((term_id = p.descendant_object_id) AND (term_object_type_id = 2))
                                                                          Filter: ((source_cd)::text = 'C'::text)
                                                                          Rows Removed by Filter: 46
                                                                          Heap Blocks: exact=1278
                                                                          ->  BitmapAnd  (cost=12730.08..12730.08 rows=242 width=0) (actual time=1.282..1.282 rows=0 loops=31)
                                                                                ->  Bitmap Index Scan on ix_phenotype_term_ref_term_id  (cost=0.00..166.71 rows=8836 width=0) (actual time=0.012..0.012 rows=54 loops=31)
                                                                                      Index Cond: (term_id = p.descendant_object_id)
                                                                                ->  Bitmap Index Scan on ix_phenotype_term_ref_object_type_id  (cost=0.00..12561.73 rows=680172 width=0) (actual time=39.225..39.225 rows=630202 loops=1)
                                                                                      Index Cond: (term_object_type_id = 2)
                                                              ->  Index Scan using reference_pk on reference r  (cost=0.42..5.27 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=239)
                                                                    Index Cond: (id = ptr.reference_id)
                                                        ->  Index Scan using ixn_pk on ixn i  (cost=0.43..6.67 rows=1 width=80) (actual time=0.008..0.008 rows=1 loops=239)
                                                              Index Cond: (id = ptr.ixn_id)
                                                  ->  Index Scan using term_pk on term associatedterm  (cost=0.43..7.96 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=239)
                                                        Index Cond: (id = ptr.term_id)
                                            ->  Index Scan using term_pk on term phenotypeterm  (cost=0.43..7.96 rows=1 width=34) (actual time=0.004..0.005 rows=1 loops=239)
                                                  Index Cond: (id = ptr.phenotype_id)
                                      ->  Index Scan using term_pk on term taxonterm  (cost=0.43..7.96 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=239)
                                            Index Cond: (ptr.taxon_id = id)
                                ->  Index Scan using ix_ixn_axn_ixn_id on ixn_axn iaxn  (cost=0.42..0.46 rows=2 width=14) (actual time=0.004..0.004 rows=2 loops=239)
                                      Index Cond: (ixn_id = i.id)
                                SubPlan 5
                                  ->  Aggregate  (cost=33.54..33.55 rows=1 width=22) (actual time=0.087..0.087 rows=1 loops=510)
                                        ->  Nested Loop  (cost=0.99..33.53 rows=2 width=22) (actual time=0.007..0.069 rows=20 loops=510)
                                              ->  Index Scan using ix_phenotype_term_ref_ids on phenotype_term_reference ptr2  (cost=0.56..16.62 rows=2 width=4) (actual time=0.005..0.021 rows=20 loops=510)
                                                    Index Cond: ((ptr.phenotype_id = phenotype_id) AND (ptr.term_id = term_id))
                                                    Filter: ((source_cd)::text = 'I'::text)
                                                    Rows Removed by Filter: 6
                                              ->  Index Scan using term_pk on term t  (cost=0.43..8.45 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=10022)
                                                    Index Cond: (id = ptr2.via_term_id)
                    SubPlan 1
                      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)
                    SubPlan 2
                      ->  Aggregate  (cost=35.68..35.69 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=237)
                            ->  Sort  (cost=35.64..35.64 rows=2 width=34) (actual time=0.011..0.012 rows=1 loops=237)
                                  Sort Key: ia.level_seq
                                  Sort Method: quicksort  Memory: 25kB
                                  ->  Nested Loop Left Join  (cost=1.27..35.63 rows=2 width=34) (actual time=0.009..0.010 rows=1 loops=237)
                                        ->  Nested Loop Left Join  (cost=0.85..18.72 rows=2 width=8) (actual time=0.006..0.007 rows=1 loops=237)
                                              Join Filter: (i2.id = ia.ixn_id)
                                              ->  Index Only Scan using ixn_pk on ixn i2  (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=237)
                                                    Index Cond: (id = i.id)
                                                    Heap Fetches: 237
                                              ->  Index Scan using ixn_anatomy_pk on ixn_anatomy ia  (cost=0.42..10.25 rows=2 width=12) (actual time=0.004..0.004 rows=1 loops=237)
                                                    Index Cond: (ixn_id = i.id)
                                        ->  Index Scan using term_pk on term anatomyterm  (cost=0.43..8.45 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=308)
                                              Index Cond: (ia.anatomy_id = id)
                    SubPlan 3
                      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)
                    SubPlan 4
                      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)
Planning time: 176.608 ms
Execution time: 102.128 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	9.417 ms	9.3 %
Bitmap Heap Scan	1	1.178 ms	1.2 %
Bitmap Index Scan	2	39.597 ms	39.0 %
BitmapAnd	1	0.145 ms	0.1 %
GroupAggregate	1	1.891 ms	1.9 %
HashAggregate	1	0.026 ms	0.0 %
Index Only Scan	2	0.962 ms	0.9 %
Index Scan	10	38.293 ms	37.7 %
Nested Loop	7	5.905 ms	5.8 %
Nested Loop Left Join	3	0.526 ms	0.5 %
Result	3	0.000 ms	0.0 %
Sort	3	3.551 ms	3.5 %
Subquery Scan	1	0.037 ms	0.0 %
Unique	1	0.072 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dag_path	1	0.488 ms	0.5 %
Index Only Scan	1	0.488 ms	100.0 %
ixn	2	2.386 ms	2.3 %
Index Only Scan	1	0.474 ms	19.9 %
Index Scan	1	1.912 ms	80.1 %
ixn_anatomy	1	0.948 ms	0.9 %
Index Scan	1	0.948 ms	100.0 %
ixn_axn	1	0.956 ms	0.9 %
Index Scan	1	0.956 ms	100.0 %
phenotype_term_reference	2	11.888 ms	11.7 %
Bitmap Heap Scan	1	1.178 ms	9.9 %
Index Scan	1	10.710 ms	90.1 %
reference	1	0.956 ms	0.9 %
Index Scan	1	0.956 ms	100.0 %
term	5	22.811 ms	22.5 %
Index Scan	5	22.811 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oqR3 : ctdtom1

Color mode:

Visible columns:

Per node type stats

Per table stats