#	exclusive	inclusive	rows x	rows	loops	node
1.	209.120	62286.276	↑ 1.0	1	1	→ Aggregate (cost=241744.61..241744.62 rows=1 width=0) (actual time=62286.276..62286.276 rows=1 loops=1)
2.	3169.544	62077.156	↓ 3.2	202106	1	→ GroupAggregate (cost=235875.66..240963.03 rows=62526 width=25) (actual time=56940.834..62077.156 rows=202106 loops=1) Filter: (count(DISTINCT li.product_id) = 1)
3.	46454.441	58907.612	↓ 1.2	665899	1	→ Sort (cost=235875.66..237310.93 rows=574106 width=25) (actual time=56940.681..58907.612 rows=665899 loops=1) Sort Key: orders.email_address Sort Method: external merge Disk: 24024kB
4.	1556.398	12453.171	↓ 1.2	665899	1	→ Hash Join (cost=87352.17..180959.68 rows=574106 width=25) (actual time=2258.658..12453.171 rows=665899 loops=1) Hash Cond: (li.product_id = products.id)
5.	5635.808	10782.706	↓ 1.2	665899	1	→ Hash Join (cost=84049.57..164022.06 rows=574106 width=25) (actual time=2144.564..10782.706 rows=665899 loops=1) Hash Cond: (li.order_id = orders.id)
6.	3003.646	3003.646	↑ 1.0	1844039	1	→ Seq Scan on line_items li (cost=0.00..50783.97 rows=1875797 width=8) (actual time=0.016..3003.646 rows=1844039 loops=1)
7.	688.082	2143.252	↓ 1.0	515850	1	→ Hash (cost=77638.93..77638.93 rows=512851 width=25) (actual time=2143.252..2143.252 rows=515850 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 29676kB
8.	1244.362	1455.170	↓ 1.0	515850	1	→ Bitmap Heap Scan on orders (cost=9755.04..77638.93 rows=512851 width=25) (actual time=219.216..1455.170 rows=515850 loops=1) Recheck Cond: (created_on > (now() - '1 year'::interval))
9.	210.808	210.808	↓ 1.1	553581	1	→ Bitmap Index Scan on orders_created_on_idx (cost=0.00..9626.83 rows=512851 width=0) (actual time=210.808..210.808 rows=553581 loops=1) Index Cond: (created_on > (now() - '1 year'::interval))
10.	16.111	114.067	↑ 1.0	18321	1	→ Hash (cost=3071.71..3071.71 rows=18471 width=4) (actual time=114.067..114.067 rows=18321 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 645kB
11.	97.956	97.956	↑ 1.0	18321	1	→ Seq Scan on products (cost=0.00..3071.71 rows=18471 width=4) (actual time=0.046..97.956 rows=18321 loops=1)

 Aggregate  (cost=241744.61..241744.62 rows=1 width=0) (actual time=62286.276..62286.276 rows=1 loops=1)
   ->  GroupAggregate  (cost=235875.66..240963.03 rows=62526 width=25) (actual time=56940.834..62077.156 rows=202106 loops=1)
         Filter: (count(DISTINCT li.product_id) = 1)
         ->  Sort  (cost=235875.66..237310.93 rows=574106 width=25) (actual time=56940.681..58907.612 rows=665899 loops=1)
               Sort Key: orders.email_address
               Sort Method: external merge  Disk: 24024kB
               ->  Hash Join  (cost=87352.17..180959.68 rows=574106 width=25) (actual time=2258.658..12453.171 rows=665899 loops=1)
                     Hash Cond: (li.product_id = products.id)
                     ->  Hash Join  (cost=84049.57..164022.06 rows=574106 width=25) (actual time=2144.564..10782.706 rows=665899 loops=1)
                           Hash Cond: (li.order_id = orders.id)
                           ->  Seq Scan on line_items li  (cost=0.00..50783.97 rows=1875797 width=8) (actual time=0.016..3003.646 rows=1844039 loops=1)
                           ->  Hash  (cost=77638.93..77638.93 rows=512851 width=25) (actual time=2143.252..2143.252 rows=515850 loops=1)
                                 Buckets: 65536  Batches: 1  Memory Usage: 29676kB
                                 ->  Bitmap Heap Scan on orders  (cost=9755.04..77638.93 rows=512851 width=25) (actual time=219.216..1455.170 rows=515850 loops=1)
                                       Recheck Cond: (created_on > (now() - '1 year'::interval))
                                       ->  Bitmap Index Scan on orders_created_on_idx  (cost=0.00..9626.83 rows=512851 width=0) (actual time=210.808..210.808 rows=553581 loops=1)
                                             Index Cond: (created_on > (now() - '1 year'::interval))
                     ->  Hash  (cost=3071.71..3071.71 rows=18471 width=4) (actual time=114.067..114.067 rows=18321 loops=1)
                           Buckets: 2048  Batches: 1  Memory Usage: 645kB
                           ->  Seq Scan on products  (cost=0.00..3071.71 rows=18471 width=4) (actual time=0.046..97.956 rows=18321 loops=1)
 Total runtime: 62329.055 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	209.120 ms	0.3 %
Bitmap Heap Scan	1	1244.362 ms	2.0 %
Bitmap Index Scan	1	210.808 ms	0.3 %
GroupAggregate	1	3169.544 ms	5.1 %
Hash	2	704.193 ms	1.1 %
Hash Join	2	7192.206 ms	11.5 %
Seq Scan	2	3101.602 ms	5.0 %
Sort	1	46454.441 ms	74.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
line_items	1	3003.646 ms	4.8 %
Seq Scan	1	3003.646 ms	100.0 %
orders	1	1244.362 ms	2.0 %
Bitmap Heap Scan	1	1244.362 ms	100.0 %
products	1	97.956 ms	0.2 %
Seq Scan	1	97.956 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: BnP

Colorize:

Columns visibility:

Per node type stats

Per table stats