#	exclusive	inclusive	rows x	rows	loops	node
1.	0.300	4970.779	↑ 1.0	1	1	→ Aggregate (cost=20.76..7275.90 rows=1 width=227) (actual time=4970.778..4970.779 rows=1 loops=1)
2.	0.013	18.050	↑ 1.0	1	1	→ Nested Loop Left Join (cost=0.00..20.75 rows=1 width=227) (actual time=18.036..18.050 rows=1 loops=1) Join Filter: (d.docid = qdr.docid)
3.	0.013	18.021	↑ 1.0	1	1	→ Nested Loop Left Join (cost=0.00..19.32 rows=1 width=224) (actual time=18.011..18.021 rows=1 loops=1) Join Filter: (d.docid = dm.docid)
4.	17.995	17.995	↑ 1.0	1	1	→ Index Scan using pk_documents on documents d (cost=0.00..10.59 rows=1 width=185) (actual time=17.989..17.995 rows=1 loops=1) Index Cond: (docid = 17125043)
5.	0.013	0.013	↓ 0.0	0	1	→ Index Scan using metadocid_idx on documentmetadata dm (cost=0.00..8.72 rows=1 width=47) (actual time=0.013..0.013 rows=0 loops=1) Index Cond: (dm.docid = 17125043) Filter: (dm.type = 'product_name'::text)
6.	0.016	0.016	↓ 0.0	0	1	→ Seq Scan on querydocumentrelevance qdr (cost=0.00..1.42 rows=1 width=11) (actual time=0.016..0.016 rows=0 loops=1) Filter: ((qdr.queryid = 10094) AND (qdr.docid = 17125043))
7.						SubPlan (forAggregate)
8.	0.409	4952.429	↑ 1.0	1	1	→ Result (cost=7255.13..7255.14 rows=1 width=0) (actual time=4952.426..4952.429 rows=1 loops=1)
9.						Initplan (forResult)
10.	10.121	91.774	↑ 1.0	1	1	→ Aggregate (cost=125.19..125.22 rows=1 width=133) (actual time=91.772..91.774 rows=1 loops=1)
11.	1.144	81.653	↓ 7.7	223	1	→ Nested Loop Left Join (cost=0.00..125.12 rows=29 width=133) (actual time=35.109..81.653 rows=223 loops=1) Join Filter: (s.sentenceid = qspo.sentenceid)
12.	80.063	80.063	↓ 7.7	223	1	→ Index Scan using sentdocs on sentences s (cost=0.00..115.37 rows=29 width=117) (actual time=35.079..80.063 rows=223 loops=1) Index Cond: (docid = $0)
13.	0.435	0.446	↓ 0.0	0	223	→ Materialize (cost=0.00..8.89 rows=2 width=24) (actual time=0.002..0.002 rows=0 loops=223)
14.	0.011	0.011	↓ 0.0	0	1	→ Index Scan using idx_qspoqid on querysentencepolarityoverride qspo (cost=0.00..8.88 rows=2 width=24) (actual time=0.011..0.011 rows=0 loops=1) Index Cond: (queryid = 10094)
15.	0.070	4860.246	↑ 1.0	1	1	→ Result (cost=7129.90..7129.91 rows=1 width=0) (actual time=4860.244..4860.246 rows=1 loops=1)
16.						Initplan (forResult)
17.	1.114	4860.176	↑ 1.0	1	1	→ Aggregate (cost=7129.89..7129.90 rows=1 width=20) (actual time=4860.174..4860.176 rows=1 loops=1)
18.	7.527	4859.062	↑ 9.8	94	1	→ Hash Join (cost=3539.08..7127.59 rows=918 width=20) (actual time=4828.703..4859.062 rows=94 loops=1) Hash Cond: (qecd.queryexecutioncategoryid = qec.queryexecutioncategoryid)
19.	4751.449	4847.401	↓ 3.7	3442	1	→ Bitmap Heap Scan on queryexecutioncategorydocuments qecd (cost=35.76..3610.51 rows=918 width=8) (actual time=96.833..4847.401 rows=3442 loops=1) Recheck Cond: (docid = $0) Filter: (NOT invisible)
20.	95.952	95.952	↓ 3.7	3442	1	→ Bitmap Index Scan on catexecdoci_idx (cost=0.00..35.53 rows=919 width=0) (actual time=95.952..95.952 rows=3442 loops=1) Index Cond: (docid = $0)
21.	2.044	4.134	↑ 1.9	1011	1	→ Hash (cost=3479.26..3479.26 rows=1924 width=28) (actual time=4.134..4.134 rows=1011 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 61kB
22.	2.090	2.090	↑ 1.9	1011	1	→ Index Scan using idx_qexcatqxid on queryexecutioncategories qec (cost=0.00..3479.26 rows=1924 width=28) (actual time=0.035..2.090 rows=1011 loops=1) Index Cond: (queryexecutionid = 34756)

"Aggregate  (cost=20.76..7275.90 rows=1 width=227) (actual time=4970.778..4970.779 rows=1 loops=1)"
"  ->  Nested Loop Left Join  (cost=0.00..20.75 rows=1 width=227) (actual time=18.036..18.050 rows=1 loops=1)"
"        Join Filter: (d.docid = qdr.docid)"
"        ->  Nested Loop Left Join  (cost=0.00..19.32 rows=1 width=224) (actual time=18.011..18.021 rows=1 loops=1)"
"              Join Filter: (d.docid = dm.docid)"
"              ->  Index Scan using pk_documents on documents d  (cost=0.00..10.59 rows=1 width=185) (actual time=17.989..17.995 rows=1 loops=1)"
"                    Index Cond: (docid = 17125043)"
"              ->  Index Scan using metadocid_idx on documentmetadata dm  (cost=0.00..8.72 rows=1 width=47) (actual time=0.013..0.013 rows=0 loops=1)"
"                    Index Cond: (dm.docid = 17125043)"
"                    Filter: (dm.type = 'product_name'::text)"
"        ->  Seq Scan on querydocumentrelevance qdr  (cost=0.00..1.42 rows=1 width=11) (actual time=0.016..0.016 rows=0 loops=1)"
"              Filter: ((qdr.queryid = 10094) AND (qdr.docid = 17125043))"
"  SubPlan 4"
"    ->  Result  (cost=7255.13..7255.14 rows=1 width=0) (actual time=4952.426..4952.429 rows=1 loops=1)"
"          InitPlan 1 (returns $1)"
"            ->  Aggregate  (cost=125.19..125.22 rows=1 width=133) (actual time=91.772..91.774 rows=1 loops=1)"
"                  ->  Nested Loop Left Join  (cost=0.00..125.12 rows=29 width=133) (actual time=35.109..81.653 rows=223 loops=1)"
"                        Join Filter: (s.sentenceid = qspo.sentenceid)"
"                        ->  Index Scan using sentdocs on sentences s  (cost=0.00..115.37 rows=29 width=117) (actual time=35.079..80.063 rows=223 loops=1)"
"                              Index Cond: (docid = $0)"
"                        ->  Materialize  (cost=0.00..8.89 rows=2 width=24) (actual time=0.002..0.002 rows=0 loops=223)"
"                              ->  Index Scan using idx_qspoqid on querysentencepolarityoverride qspo  (cost=0.00..8.88 rows=2 width=24) (actual time=0.011..0.011 rows=0 loops=1)"
"                                    Index Cond: (queryid = 10094)"
"          InitPlan 3 (returns $3)"
"            ->  Result  (cost=7129.90..7129.91 rows=1 width=0) (actual time=4860.244..4860.246 rows=1 loops=1)"
"                  InitPlan 2 (returns $2)"
"                    ->  Aggregate  (cost=7129.89..7129.90 rows=1 width=20) (actual time=4860.174..4860.176 rows=1 loops=1)"
"                          ->  Hash Join  (cost=3539.08..7127.59 rows=918 width=20) (actual time=4828.703..4859.062 rows=94 loops=1)"
"                                Hash Cond: (qecd.queryexecutioncategoryid = qec.queryexecutioncategoryid)"
"                                ->  Bitmap Heap Scan on queryexecutioncategorydocuments qecd  (cost=35.76..3610.51 rows=918 width=8) (actual time=96.833..4847.401 rows=3442 loops=1)"
"                                      Recheck Cond: (docid = $0)"
"                                      Filter: (NOT invisible)"
"                                      ->  Bitmap Index Scan on catexecdoci_idx  (cost=0.00..35.53 rows=919 width=0) (actual time=95.952..95.952 rows=3442 loops=1)"
"                                            Index Cond: (docid = $0)"
"                                ->  Hash  (cost=3479.26..3479.26 rows=1924 width=28) (actual time=4.134..4.134 rows=1011 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 61kB"
"                                      ->  Index Scan using idx_qexcatqxid on queryexecutioncategories qec  (cost=0.00..3479.26 rows=1924 width=28) (actual time=0.035..2.090 rows=1011 loops=1)"
"                                            Index Cond: (queryexecutionid = 34756)"
"Total runtime: 4971.046 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	3	11.535 ms	0.2 %
Bitmap Heap Scan	1	4751.449 ms	95.6 %
Bitmap Index Scan	1	95.952 ms	1.9 %
Hash	1	2.044 ms	0.0 %
Hash Join	1	7.527 ms	0.2 %
Index Scan	5	100.172 ms	2.0 %
Materialize	1	0.435 ms	0.0 %
Nested Loop Left Join	3	1.170 ms	0.0 %
Result	2	0.479 ms	0.0 %
Seq Scan	1	0.016 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
documentmetadata	1	0.013 ms	0.0 %
Index Scan	1	0.013 ms	100.0 %
documents	1	17.995 ms	0.4 %
Index Scan	1	17.995 ms	100.0 %
querydocumentrelevance	1	0.016 ms	0.0 %
Seq Scan	1	0.016 ms	100.0 %
queryexecutioncategories	1	2.090 ms	0.0 %
Index Scan	1	2.090 ms	100.0 %
queryexecutioncategorydocuments	1	4751.449 ms	95.6 %
Bitmap Heap Scan	1	4751.449 ms	100.0 %
querysentencepolarityoverride	1	0.011 ms	0.0 %
Index Scan	1	0.011 ms	100.0 %
sentences	1	80.063 ms	1.6 %
Index Scan	1	80.063 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: yRa

Colorize:

Columns visibility:

Per node type stats

Per table stats