#	exclusive	inclusive	rows x	rows	loops	node
1.	57.096	10684.798	↓ 16400.6	82003	1	→ Nested Loop Semi Join (cost=543422.34..571013.45 rows=5 width=4) (actual time=5854.267..10684.798 rows=82003 loops=1) Join Filter: (root.feature_mapping_id = anodeid.parent_feature_mapping_id)
2.	56.036	5953.531	↓ 6833.6	82003	1	→ Merge Join (cost=543422.34..543426.29 rows=12 width=24) (actual time=5854.182..5953.531 rows=82003 loops=1) Merge Cond: ((root.feature_mapping_id = node1.parent_feature_mapping_id) AND (root.payload_id = node2.payload_id))
3.	119.467	380.707	↓ 289.8	82003	1	→ Sort (cost=1150.65..1151.35 rows=283 width=12) (actual time=359.940..380.707 rows=82003 loops=1) Sort Key: root.feature_mapping_id, root.payload_id Sort Method: external merge Disk: 1768kB
4.	261.240	261.240	↓ 289.8	82003	1	→ Index Scan using path_value_idx on path_value root (cost=0.00..1139.12 rows=283 width=12) (actual time=0.073..261.240 rows=82003 loops=1) Index Cond: ((rm_type_name = 'COMPOSITION'::text) AND (feature_name = 'composition'::text))
5.	122.335	5516.788	↓ 332.0	82003	1	→ Sort (cost=542271.69..542272.31 rows=247 width=12) (actual time=5494.236..5516.788 rows=82003 loops=1) Sort Key: node1.parent_feature_mapping_id, node2.payload_id Sort Method: external sort Disk: 2088kB
6.	68.615	5394.453	↓ 332.0	82003	1	→ HashAggregate (cost=542259.40..542261.87 rows=247 width=12) (actual time=5377.761..5394.453 rows=82003 loops=1)
7.	183.712	5325.838	↓ 136.1	164004	1	→ Hash Semi Join (cost=506297.37..542253.38 rows=1205 width=12) (actual time=2788.758..5325.838 rows=164004 loops=1) Hash Cond: ((node1.payload_id = node2.payload_id) AND (node1.feature_mapping_id = node2.parent_feature_mapping_id))
8.	2354.833	2430.473	↓ 99.8	246005	1	→ Bitmap Heap Scan on path_value node1 (cost=77.61..9421.62 rows=2464 width=12) (actual time=76.628..2430.473 rows=246005 loops=1) Recheck Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))
9.	75.640	75.640	↓ 99.8	246005	1	→ Bitmap Index Scan on path_value_idx (cost=0.00..77.00 rows=2464 width=0) (actual time=75.640..75.640 rows=246005 loops=1) Index Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))
10.	104.186	2711.653	↓ 1.0	410015	1	→ Hash (cost=498674.38..498674.38 rows=399092 width=8) (actual time=2711.653..2711.653 rows=410015 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 1013kB
11.	2537.833	2607.467	↓ 1.0	410015	1	→ Bitmap Heap Scan on path_value node2 (cost=10345.32..498674.38 rows=399092 width=8) (actual time=71.718..2607.467 rows=410015 loops=1) Recheck Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)
12.	69.634	69.634	↓ 1.0	410015	1	→ Bitmap Index Scan on path_value_idx (cost=0.00..10245.55 rows=399092 width=0) (actual time=69.634..69.634 rows=410015 loops=1) Index Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)
13.	4674.171	4674.171	↑ 1.0	1	82003	→ Index Scan using path_value_idx2 on path_value anodeid (cost=0.00..2298.91 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=82003) Index Cond: (payload_id = node2.payload_id) Filter: ((feature_name = 'archetypeNodeId'::text) AND (val_string = 'openEHR-EHR-COMPOSITION.discharge.v1'::text))

"Nested Loop Semi Join  (cost=543422.34..571013.45 rows=5 width=4) (actual time=5854.267..10684.798 rows=82003 loops=1)"
"  Join Filter: (root.feature_mapping_id = anodeid.parent_feature_mapping_id)"
"  ->  Merge Join  (cost=543422.34..543426.29 rows=12 width=24) (actual time=5854.182..5953.531 rows=82003 loops=1)"
"        Merge Cond: ((root.feature_mapping_id = node1.parent_feature_mapping_id) AND (root.payload_id = node2.payload_id))"
"        ->  Sort  (cost=1150.65..1151.35 rows=283 width=12) (actual time=359.940..380.707 rows=82003 loops=1)"
"              Sort Key: root.feature_mapping_id, root.payload_id"
"              Sort Method: external merge  Disk: 1768kB"
"              ->  Index Scan using path_value_idx on path_value root  (cost=0.00..1139.12 rows=283 width=12) (actual time=0.073..261.240 rows=82003 loops=1)"
"                    Index Cond: ((rm_type_name = 'COMPOSITION'::text) AND (feature_name = 'composition'::text))"
"        ->  Sort  (cost=542271.69..542272.31 rows=247 width=12) (actual time=5494.236..5516.788 rows=82003 loops=1)"
"              Sort Key: node1.parent_feature_mapping_id, node2.payload_id"
"              Sort Method: external sort  Disk: 2088kB"
"              ->  HashAggregate  (cost=542259.40..542261.87 rows=247 width=12) (actual time=5377.761..5394.453 rows=82003 loops=1)"
"                    ->  Hash Semi Join  (cost=506297.37..542253.38 rows=1205 width=12) (actual time=2788.758..5325.838 rows=164004 loops=1)"
"                          Hash Cond: ((node1.payload_id = node2.payload_id) AND (node1.feature_mapping_id = node2.parent_feature_mapping_id))"
"                          ->  Bitmap Heap Scan on path_value node1  (cost=77.61..9421.62 rows=2464 width=12) (actual time=76.628..2430.473 rows=246005 loops=1)"
"                                Recheck Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))"
"                                ->  Bitmap Index Scan on path_value_idx  (cost=0.00..77.00 rows=2464 width=0) (actual time=75.640..75.640 rows=246005 loops=1)"
"                                      Index Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))"
"                          ->  Hash  (cost=498674.38..498674.38 rows=399092 width=8) (actual time=2711.653..2711.653 rows=410015 loops=1)"
"                                Buckets: 4096  Batches: 16  Memory Usage: 1013kB"
"                                ->  Bitmap Heap Scan on path_value node2  (cost=10345.32..498674.38 rows=399092 width=8) (actual time=71.718..2607.467 rows=410015 loops=1)"
"                                      Recheck Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)"
"                                      ->  Bitmap Index Scan on path_value_idx  (cost=0.00..10245.55 rows=399092 width=0) (actual time=69.634..69.634 rows=410015 loops=1)"
"                                            Index Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)"
"  ->  Index Scan using path_value_idx2 on path_value anodeid  (cost=0.00..2298.91 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=82003)"
"        Index Cond: (payload_id = node2.payload_id)"
"        Filter: ((feature_name = 'archetypeNodeId'::text) AND (val_string = 'openEHR-EHR-COMPOSITION.discharge.v1'::text))"
"Total runtime: 10692.277 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	4892.666 ms	45.8 %
Bitmap Index Scan	2	145.274 ms	1.4 %
Hash	1	104.186 ms	1.0 %
Hash Semi Join	1	183.712 ms	1.7 %
HashAggregate	1	68.615 ms	0.6 %
Index Scan	2	4935.411 ms	46.2 %
Merge Join	1	56.036 ms	0.5 %
Nested Loop Semi Join	1	57.096 ms	0.5 %
Sort	2	241.802 ms	2.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
path_value	4	9828.077 ms	92.0 %
Bitmap Heap Scan	2	4892.666 ms	49.8 %
Index Scan	2	4935.411 ms	50.2 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: BLk

Colorize:

Columns visibility:

Per node type stats

Per table stats