#	exclusive	inclusive	rows x	rows	loops	node
1.	0.064	45.150	↑ 1.0	29	1	→ Sort (cost=29846.61..29846.68 rows=29 width=676) (actual time=45.135..45.150 rows=29 loops=1) Sort Key: spec.somme Sort Method: quicksort Memory: 41kB
2.						CTE spec
3.	0.024	44.577	↑ 1.0	29	1	→ Limit (cost=29608.85..29608.92 rows=29 width=16) (actual time=44.540..44.577 rows=29 loops=1)
4.						CTE rivet
5.	12.792	13.378	↓ 2.0	1534	1	→ Bitmap Heap Scan on rivet (cost=24.68..2824.50 rows=769 width=8) (actual time=0.820..13.378 rows=1534 loops=1) Recheck Cond: ((first_word = 'photonic'::text) OR (second_word = 'photonic'::text))
6.	0.003	0.586	↓ 0.0	0	1	→ BitmapOr (cost=24.68..24.68 rows=769 width=0) (actual time=0.586..0.586 rows=0 loops=1)
7.	0.294	0.294	↓ 1.8	704	1	→ Bitmap Index Scan on rivet_first_word_idx (cost=0.00..12.22 rows=394 width=0) (actual time=0.294..0.294 rows=704 loops=1) Index Cond: (first_word = 'photonic'::text)
8.	0.289	0.289	↓ 2.2	831	1	→ Bitmap Index Scan on rivet_second_word_idx (cost=0.00..12.08 rows=375 width=0) (actual time=0.289..0.289 rows=831 loops=1) Index Cond: (second_word = 'photonic'::text)
9.	0.301	44.553	↑ 278.2	29	1	→ Sort (cost=26784.35..26804.52 rows=8067 width=16) (actual time=44.538..44.553 rows=29 loops=1) Sort Key: (sum(rs1.weight)) Sort Method: top-N heapsort Memory: 18kB
10.	4.246	44.252	↑ 20.2	400	1	→ HashAggregate (cost=26447.23..26548.07 rows=8067 width=16) (actual time=43.997..44.252 rows=400 loops=1)
11.	5.803	40.006	↑ 2.3	3476	1	→ Nested Loop (cost=0.00..26406.90 rows=8067 width=16) (actual time=0.837..40.006 rows=3476 loops=1)
12.	15.795	15.795	↓ 2.0	1534	1	→ CTE Scan on rivet (cost=0.00..15.38 rows=769 width=8) (actual time=0.823..15.795 rows=1534 loops=1)
13.	18.408	18.408	↑ 5.0	2	1534	→ Index Scan using rivet_id_idx on rivet_specimen rs1 (cost=0.00..34.19 rows=10 width=24) (actual time=0.008..0.012 rows=2 loops=1534) Index Cond: (rs1.rivet_id = rivet.rivet_id)
14.	0.069	45.086	↑ 1.0	29	1	→ Nested Loop (cost=0.00..236.99 rows=29 width=676) (actual time=44.573..45.086 rows=29 loops=1)
15.	44.611	44.611	↑ 1.0	29	1	→ CTE Scan on spec (cost=0.00..0.58 rows=29 width=16) (actual time=44.542..44.611 rows=29 loops=1)
16.	0.406	0.406	↑ 1.0	1	29	→ Index Scan using specimen_pkey on specimen s (cost=0.00..8.14 rows=1 width=668) (actual time=0.013..0.014 rows=1 loops=29) Index Cond: (s.specimen_id = spec.specimen_id)

"Sort  (cost=29846.61..29846.68 rows=29 width=676) (actual time=45.135..45.150 rows=29 loops=1)"
"  Sort Key: spec.somme"
"  Sort Method:  quicksort  Memory: 41kB"
"  CTE spec"
"    ->  Limit  (cost=29608.85..29608.92 rows=29 width=16) (actual time=44.540..44.577 rows=29 loops=1)"
"          CTE rivet"
"            ->  Bitmap Heap Scan on rivet  (cost=24.68..2824.50 rows=769 width=8) (actual time=0.820..13.378 rows=1534 loops=1)"
"                  Recheck Cond: ((first_word = 'photonic'::text) OR (second_word = 'photonic'::text))"
"                  ->  BitmapOr  (cost=24.68..24.68 rows=769 width=0) (actual time=0.586..0.586 rows=0 loops=1)"
"                        ->  Bitmap Index Scan on rivet_first_word_idx  (cost=0.00..12.22 rows=394 width=0) (actual time=0.294..0.294 rows=704 loops=1)"
"                              Index Cond: (first_word = 'photonic'::text)"
"                        ->  Bitmap Index Scan on rivet_second_word_idx  (cost=0.00..12.08 rows=375 width=0) (actual time=0.289..0.289 rows=831 loops=1)"
"                              Index Cond: (second_word = 'photonic'::text)"
"          ->  Sort  (cost=26784.35..26804.52 rows=8067 width=16) (actual time=44.538..44.553 rows=29 loops=1)"
"                Sort Key: (sum(rs1.weight))"
"                Sort Method:  top-N heapsort  Memory: 18kB"
"                ->  HashAggregate  (cost=26447.23..26548.07 rows=8067 width=16) (actual time=43.997..44.252 rows=400 loops=1)"
"                      ->  Nested Loop  (cost=0.00..26406.90 rows=8067 width=16) (actual time=0.837..40.006 rows=3476 loops=1)"
"                            ->  CTE Scan on rivet  (cost=0.00..15.38 rows=769 width=8) (actual time=0.823..15.795 rows=1534 loops=1)"
"                            ->  Index Scan using rivet_id_idx on rivet_specimen rs1  (cost=0.00..34.19 rows=10 width=24) (actual time=0.008..0.012 rows=2 loops=1534)"
"                                  Index Cond: (rs1.rivet_id = rivet.rivet_id)"
"  ->  Nested Loop  (cost=0.00..236.99 rows=29 width=676) (actual time=44.573..45.086 rows=29 loops=1)"
"        ->  CTE Scan on spec  (cost=0.00..0.58 rows=29 width=16) (actual time=44.542..44.611 rows=29 loops=1)"
"        ->  Index Scan using specimen_pkey on specimen s  (cost=0.00..8.14 rows=1 width=668) (actual time=0.013..0.014 rows=1 loops=29)"
"              Index Cond: (s.specimen_id = spec.specimen_id)"
"Total runtime: 45.277 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	12.792 ms	28.3 %
Bitmap Index Scan	2	0.583 ms	1.3 %
BitmapOr	1	0.003 ms	0.0 %
CTE Scan	2	60.406 ms	133.8 %
HashAggregate	1	4.246 ms	9.4 %
Index Scan	2	18.814 ms	41.7 %
Limit	1	0.024 ms	0.1 %
Nested Loop	2	5.872 ms	13.0 %
Sort	2	0.365 ms	0.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
rivet	1	12.792 ms	28.3 %
Bitmap Heap Scan	1	12.792 ms	100.0 %
rivet_specimen	1	18.408 ms	40.8 %
Index Scan	1	18.408 ms	100.0 %
specimen	1	0.406 ms	0.9 %
Index Scan	1	0.406 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Lrj

Colorize:

Columns visibility:

Per node type stats

Per table stats