#	exclusive	inclusive	rows x	rows	loops	node
1.	50.044	115830.044	↑ 1.0	10000	1	→ Seq Scan on public.author a (cost=0.00..15448695.48 rows=10000 width=55) (actual time=12.592..115830.044 rows=10000 loops=1) Output: a.lastname, a.firstname, a.secondname, (SubPlan 1), (SubPlan 2), 0, NULL::text[] Buffers: shared hit=4502345
2.						SubPlan (forSeq Scan)
3.	110.000	230.000	↑ 1.0	1	10000	→ Aggregate (cost=26.31..26.32 rows=1 width=60) (actual time=0.023..0.023 rows=1 loops=10000) Output: count(ab.*) Buffers: shared hit=41118
4.	40.000	120.000	↑ 1.0	6	10000	→ Bitmap Heap Scan on public.authorbook ab (cost=4.32..26.29 rows=6 width=60) (actual time=0.011..0.012 rows=6 loops=10000) Output: ab.* Recheck Cond: (ab.authorid = a.id) Buffers: shared hit=41118
5.	80.000	80.000	↑ 1.0	6	10000	→ Bitmap Index Scan on pk_authorbook (cost=0.00..4.31 rows=6 width=0) (actual time=0.008..0.008 rows=6 loops=10000) Index Cond: (ab.authorid = a.id) Buffers: shared hit=30530
6.	190.000	115550.000	↑ 1.0	1	10000	→ Aggregate (cost=1518.52..1518.53 rows=1 width=16) (actual time=11.555..11.555 rows=1 loops=10000) Output: (count(DISTINCT t2.authorid) - 1) Buffers: shared hit=4461118
7.	70600.000	115360.000	↑ 1.0	12	10000	→ Hash Join (cost=26.37..1518.49 rows=12 width=16) (actual time=5.712..11.536 rows=12 loops=10000) Output: t2.authorid Hash Cond: (t2.bookid = t1.bookid) Buffers: shared hit=4461118
8.	44650.000	44650.000	↑ 1.0	60000	10000	→ Seq Scan on public.authorbook t2 (cost=0.00..1042.00 rows=60000 width=32) (actual time=0.003..4.465 rows=60000 loops=10000) Output: t2.authorid, t2.bookid, t2.pos Buffers: shared hit=4420000
9.	40.000	110.000	↑ 1.0	6	10000	→ Hash (cost=26.29..26.29 rows=6 width=16) (actual time=0.011..0.011 rows=6 loops=10000) Output: t1.bookid Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=41118
10.	30.000	70.000	↑ 1.0	6	10000	→ Bitmap Heap Scan on public.authorbook t1 (cost=4.32..26.29 rows=6 width=16) (actual time=0.006..0.007 rows=6 loops=10000) Output: t1.bookid Recheck Cond: (t1.authorid = a.id) Buffers: shared hit=41118
11.	40.000	40.000	↑ 1.0	6	10000	→ Bitmap Index Scan on pk_authorbook (cost=0.00..4.31 rows=6 width=0) (actual time=0.004..0.004 rows=6 loops=10000) Index Cond: (t1.authorid = a.id) Buffers: shared hit=30530

"Seq Scan on public.author a  (cost=0.00..15448695.48 rows=10000 width=55) (actual time=12.592..115830.044 rows=10000 loops=1)"
"  Output: a.lastname, a.firstname, a.secondname, (SubPlan 1), (SubPlan 2), 0, NULL::text[]"
"  Buffers: shared hit=4502345"
"  SubPlan 1"
"    ->  Aggregate  (cost=26.31..26.32 rows=1 width=60) (actual time=0.023..0.023 rows=1 loops=10000)"
"          Output: count(ab.*)"
"          Buffers: shared hit=41118"
"          ->  Bitmap Heap Scan on public.authorbook ab  (cost=4.32..26.29 rows=6 width=60) (actual time=0.011..0.012 rows=6 loops=10000)"
"                Output: ab.*"
"                Recheck Cond: (ab.authorid = a.id)"
"                Buffers: shared hit=41118"
"                ->  Bitmap Index Scan on pk_authorbook  (cost=0.00..4.31 rows=6 width=0) (actual time=0.008..0.008 rows=6 loops=10000)"
"                      Index Cond: (ab.authorid = a.id)"
"                      Buffers: shared hit=30530"
"  SubPlan 2"
"    ->  Aggregate  (cost=1518.52..1518.53 rows=1 width=16) (actual time=11.555..11.555 rows=1 loops=10000)"
"          Output: (count(DISTINCT t2.authorid) - 1)"
"          Buffers: shared hit=4461118"
"          ->  Hash Join  (cost=26.37..1518.49 rows=12 width=16) (actual time=5.712..11.536 rows=12 loops=10000)"
"                Output: t2.authorid"
"                Hash Cond: (t2.bookid = t1.bookid)"
"                Buffers: shared hit=4461118"
"                ->  Seq Scan on public.authorbook t2  (cost=0.00..1042.00 rows=60000 width=32) (actual time=0.003..4.465 rows=60000 loops=10000)"
"                      Output: t2.authorid, t2.bookid, t2.pos"
"                      Buffers: shared hit=4420000"
"                ->  Hash  (cost=26.29..26.29 rows=6 width=16) (actual time=0.011..0.011 rows=6 loops=10000)"
"                      Output: t1.bookid"
"                      Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                      Buffers: shared hit=41118"
"                      ->  Bitmap Heap Scan on public.authorbook t1  (cost=4.32..26.29 rows=6 width=16) (actual time=0.006..0.007 rows=6 loops=10000)"
"                            Output: t1.bookid"
"                            Recheck Cond: (t1.authorid = a.id)"
"                            Buffers: shared hit=41118"
"                            ->  Bitmap Index Scan on pk_authorbook  (cost=0.00..4.31 rows=6 width=0) (actual time=0.004..0.004 rows=6 loops=10000)"
"                                  Index Cond: (t1.authorid = a.id)"
"                                  Buffers: shared hit=30530"
"Total runtime: 115832.796 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	300.000 ms	0.3 %
Bitmap Heap Scan	2	70.000 ms	0.1 %
Bitmap Index Scan	2	120.000 ms	0.1 %
Hash	1	40.000 ms	0.0 %
Hash Join	1	70600.000 ms	61.0 %
Seq Scan	2	44700.044 ms	38.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
public.author	1	50.044 ms	0.0 %
Seq Scan	1	50.044 ms	100.0 %
public.authorbook	3	44720.000 ms	38.6 %
Bitmap Heap Scan	2	70.000 ms	0.2 %
Seq Scan	1	44650.000 ms	99.8 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 140

Colorize:

Columns visibility:

Per node type stats

Per table stats