#	exclusive	inclusive	rows x	rows	loops	node
1.	2.076	11442.937	↑ 1.0	1	1	→ Aggregate (cost=17727.23..17727.24 rows=1 width=0) (actual time=11442.937..11442.937 rows=1 loops=1)
2.	40.422	11440.861	↓ 35875.0	35875	1	→ HashAggregate (cost=17727.20..17727.21 rows=1 width=8) (actual time=11434.465..11440.861 rows=35875 loops=1)
3.	154.647	11400.439	↓ 36009.0	36009	1	→ Nested Loop (cost=280.53..17727.20 rows=1 width=8) (actual time=21.299..11400.439 rows=36009 loops=1)
4.	152.595	10691.390	↓ 277201.0	277201	1	→ Nested Loop (cost=280.53..17573.31 rows=1 width=12) (actual time=21.228..10691.390 rows=277201 loops=1)
5.	684.470	9984.393	↓ 277201.0	277201	1	→ Nested Loop (cost=280.53..17572.87 rows=1 width=8) (actual time=21.218..9984.393 rows=277201 loops=1)
6.	340.606	7080.995	↓ 1109464.0	1109464	1	→ Nested Loop (cost=280.53..17418.98 rows=1 width=12) (actual time=21.156..7080.995 rows=1109464 loops=1)
7.	398.799	4521.461	↓ 1109464.0	1109464	1	→ Nested Loop (cost=280.53..17418.54 rows=1 width=8) (actual time=21.122..4521.461 rows=1109464 loops=1) Join Filter: (component1.id = rank1.component_ref)
8.	267.252	1806.302	↓ 1158180.0	1158180	1	→ Nested Loop (cost=280.53..17409.99 rows=1 width=20) (actual time=21.088..1806.302 rows=1158180 loops=1)
9.	45.978	175.002	↓ 48716.0	48716	1	→ Nested Loop (cost=49.80..122.36 rows=1 width=8) (actual time=21.052..175.002 rows=48716 loops=1)
10.	10.703	31.592	↓ 8119.3	48716	1	→ Bitmap Heap Scan on component component1 (cost=49.80..72.51 rows=6 width=4) (actual time=21.023..31.592 rows=48716 loops=1) Recheck Cond: ((name IS NULL) AND (type = 'd'::bpchar))
11.	0.098	20.889	↓ 0.0	0	1	→ BitmapAnd (cost=49.80..49.80 rows=6 width=0) (actual time=20.889..20.889 rows=0 loops=1)
12.	11.522	11.522	↓ 152.9	173257	1	→ Bitmap Index Scan on idx_component_name (cost=0.00..24.77 rows=1133 width=0) (actual time=11.522..11.522 rows=173257 loops=1) Index Cond: (name IS NULL)
13.	9.269	9.269	↓ 90.1	102136	1	→ Bitmap Index Scan on idx_component_type (cost=0.00..24.77 rows=1133 width=0) (actual time=9.269..9.269 rows=102136 loops=1) Index Cond: (type = 'd'::bpchar)
14.	97.432	97.432	↑ 1.0	1	48716	→ Index Scan using "PK_component" on component component2 (cost=0.00..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=48716) Index Cond: (id = component1.id) Filter: ((name IS NULL) AND (type = 'd'::bpchar))
15.	1120.468	1364.048	↑ 506.5	24	48716	→ Bitmap Heap Scan on rank rank2 (cost=230.73..17135.66 rows=12157 width=12) (actual time=0.008..0.028 rows=24 loops=48716) Recheck Cond: (component_ref = component1.id)
16.	243.580	243.580	↑ 506.5	24	48716	→ Bitmap Index Scan on idx_rank_component_ref (cost=0.00..227.69 rows=12157 width=0) (actual time=0.005..0.005 rows=24 loops=48716) Index Cond: (component_ref = component1.id)
17.	2316.360	2316.360	↑ 1.0	1	1158180	→ Index Scan using "UNIQ_rank_pre" on rank rank1 (cost=0.00..8.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1158180) Index Cond: (pre = rank2.parent)
18.	2218.928	2218.928	↑ 1.0	1	1109464	→ Index Scan using "PK_node" on node node1 (cost=0.00..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1109464) Index Cond: (id = rank1.node_ref)
19.	2218.928	2218.928	↓ 0.0	0	1109464	→ Index Scan using "UNIQ_node_annotation" on node_annotation node_annotation1 (cost=0.00..153.87 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1109464) Index Cond: ((node_ref = node1.id) AND ((name)::text = 'cat'::text)) Filter: ((value)::text = 'S'::text)
20.	554.402	554.402	↑ 1.0	1	277201	→ Index Scan using "PK_node" on node node2 (cost=0.00..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=277201) Index Cond: (id = rank2.node_ref)
21.	554.402	554.402	↓ 0.0	0	277201	→ Index Scan using "UNIQ_node_annotation" on node_annotation node_annotation2 (cost=0.00..153.87 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=277201) Index Cond: ((node_ref = node2.id) AND ((name)::text = 'pos'::text)) Filter: ((value)::text = 'VVFIN'::text)

 Aggregate  (cost=17727.23..17727.24 rows=1 width=0) (actual time=11442.937..11442.937 rows=1 loops=1)
   ->  HashAggregate  (cost=17727.20..17727.21 rows=1 width=8) (actual time=11434.465..11440.861 rows=35875 loops=1)
         ->  Nested Loop  (cost=280.53..17727.20 rows=1 width=8) (actual time=21.299..11400.439 rows=36009 loops=1)
               ->  Nested Loop  (cost=280.53..17573.31 rows=1 width=12) (actual time=21.228..10691.390 rows=277201 loops=1)
                     ->  Nested Loop  (cost=280.53..17572.87 rows=1 width=8) (actual time=21.218..9984.393 rows=277201 loops=1)
                           ->  Nested Loop  (cost=280.53..17418.98 rows=1 width=12) (actual time=21.156..7080.995 rows=1109464 loops=1)
                                 ->  Nested Loop  (cost=280.53..17418.54 rows=1 width=8) (actual time=21.122..4521.461 rows=1109464 loops=1)
                                       Join Filter: (component1.id = rank1.component_ref)
                                       ->  Nested Loop  (cost=280.53..17409.99 rows=1 width=20) (actual time=21.088..1806.302 rows=1158180 loops=1)
                                             ->  Nested Loop  (cost=49.80..122.36 rows=1 width=8) (actual time=21.052..175.002 rows=48716 loops=1)
                                                   ->  Bitmap Heap Scan on component component1  (cost=49.80..72.51 rows=6 width=4) (actual time=21.023..31.592 rows=48716 loops=1)
                                                         Recheck Cond: ((name IS NULL) AND (type = 'd'::bpchar))
                                                         ->  BitmapAnd  (cost=49.80..49.80 rows=6 width=0) (actual time=20.889..20.889 rows=0 loops=1)
                                                               ->  Bitmap Index Scan on idx_component_name  (cost=0.00..24.77 rows=1133 width=0) (actual time=11.522..11.522 rows=173257 loops=1)
                                                                     Index Cond: (name IS NULL)
                                                               ->  Bitmap Index Scan on idx_component_type  (cost=0.00..24.77 rows=1133 width=0) (actual time=9.269..9.269 rows=102136 loops=1)
                                                                     Index Cond: (type = 'd'::bpchar)
                                                   ->  Index Scan using "PK_component" on component component2  (cost=0.00..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=48716)
                                                         Index Cond: (id = component1.id)
                                                         Filter: ((name IS NULL) AND (type = 'd'::bpchar))
                                             ->  Bitmap Heap Scan on rank rank2  (cost=230.73..17135.66 rows=12157 width=12) (actual time=0.008..0.028 rows=24 loops=48716)
                                                   Recheck Cond: (component_ref = component1.id)
                                                   ->  Bitmap Index Scan on idx_rank_component_ref  (cost=0.00..227.69 rows=12157 width=0) (actual time=0.005..0.005 rows=24 loops=48716)
                                                         Index Cond: (component_ref = component1.id)
                                       ->  Index Scan using "UNIQ_rank_pre" on rank rank1  (cost=0.00..8.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1158180)
                                             Index Cond: (pre = rank2.parent)
                                 ->  Index Scan using "PK_node" on node node1  (cost=0.00..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1109464)
                                       Index Cond: (id = rank1.node_ref)
                           ->  Index Scan using "UNIQ_node_annotation" on node_annotation node_annotation1  (cost=0.00..153.87 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1109464)
                                 Index Cond: ((node_ref = node1.id) AND ((name)::text = 'cat'::text))
                                 Filter: ((value)::text = 'S'::text)
                     ->  Index Scan using "PK_node" on node node2  (cost=0.00..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=277201)
                           Index Cond: (id = rank2.node_ref)
               ->  Index Scan using "UNIQ_node_annotation" on node_annotation node_annotation2  (cost=0.00..153.87 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=277201)
                     Index Cond: ((node_ref = node2.id) AND ((name)::text = 'pos'::text))
                     Filter: ((value)::text = 'VVFIN'::text)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	2.076 ms	0.0 %
Bitmap Heap Scan	2	1131.171 ms	9.9 %
Bitmap Index Scan	3	264.371 ms	2.3 %
BitmapAnd	1	0.098 ms	0.0 %
HashAggregate	1	40.422 ms	0.4 %
Index Scan	6	7960.452 ms	69.6 %
Nested Loop	7	2044.347 ms	17.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
component	2	108.135 ms	0.9 %
Bitmap Heap Scan	1	10.703 ms	9.9 %
Index Scan	1	97.432 ms	90.1 %
node	2	2773.330 ms	24.2 %
Index Scan	2	2773.330 ms	100.0 %
node_annotation	2	2773.330 ms	24.2 %
Index Scan	2	2773.330 ms	100.0 %
rank	2	3436.828 ms	30.0 %
Bitmap Heap Scan	1	1120.468 ms	32.6 %
Index Scan	1	2316.360 ms	67.4 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: W9r : cat="S" & pos="VVFIN" & #1 > #2

Colorize:

Columns visibility:

Per node type stats

Per table stats