explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jpj

Settings
# exclusive inclusive rows x rows loops node
1. 80.979 16,586.053 ↑ 1.0 1 1

Aggregate (cost=1,342,696.33..1,342,696.34 rows=1 width=0) (actual time=16,586.053..16,586.053 rows=1 loops=1)

  • Output: count(0)
2. 199.221 16,505.074 ↓ 9.1 1,237,906 1

Hash Join (cost=517,466.51..1,342,356.74 rows=135,839 width=0) (actual time=7,552.950..16,505.074 rows=1,237,906 loops=1)

  • Hash Cond: (p.brand_id = b.id)
3. 16,304.284 16,304.284 ↓ 9.1 1,237,906 1

Hash Join (cost=517,402.33..1,340,424.76 rows=135,839 width=4) (actual time=7,551.344..16,304.284 rows=1,237,906 loops=1)

4. 0.000 1.569 ↑ 1.0 1,075 1

Hash (cost=50.75..50.75 rows=1,075 width=4) (actual time=1.569..1.569 rows=1,075 loops=1)

  • Output: p.brand_id
  • Output: b.id
  • Hash Cond: (s.product_id = p.id)
  • Buckets: 2048 Batches: 1 Memory Usage: 54kB
5. 1.296 1.296 ↑ 1.0 1,075 1

Seq Scan on public.brands b (cost=0.00..50.75 rows=1,075 width=4) (actual time=0.158..1.296 rows=1,075 loops=1)

6. 15,137.777 15,137.777 ↓ 9.1 1,237,906 1

Hash Join (cost=448,591.56..1,266,695.21 rows=135,839 width=8) (actual time=6,312.899..15,137.777 rows=1,237,906 loops=1)

7. 0.000 663.209 ↑ 1.0 407,323 1

Hash (cost=61,730.23..61,730.23 rows=407,323 width=12) (actual time=663.209..663.209 rows=407,323 loops=1)

  • Output: s.product_id
  • Output: p.id, p.brand_id
  • Output: b.id
  • Hash Cond: (li.sku_id = s.id)
  • Buckets: 131072 Batches: 8 Memory Usage: 3227kB
8. 558.120 558.120 ↑ 1.0 407,323 1

Seq Scan on public.products p (cost=0.00..61,730.23 rows=407,323 width=12) (actual time=0.172..558.120 rows=407,323 loops=1)

9. 13,920.999 13,920.999 ↓ 9.1 1,237,906 1

Hash Join (cost=372,281.26..1,182,734.12 rows=135,839 width=8) (actual time=5,313.655..13,920.999 rows=1,237,906 loops=1)

10. 0.000 546.540 ↓ 1.0 966,839 1

Hash (cost=59,504.69..59,504.69 rows=966,769 width=16) (actual time=546.540..546.540 rows=966,839 loops=1)

  • Output: s.id, s.product_id
  • Output: p.id, p.brand_id
  • Output: li.sku_id
  • Hash Cond: (li.order_id = orders.id)
  • Buckets: 131072 Batches: 16 Memory Usage: 3877kB
11. 372.191 372.191 ↓ 1.0 966,839 1

Seq Scan on public.skus s (cost=0.00..59,504.69 rows=966,769 width=16) (actual time=0.128..372.191 rows=966,839 loops=1)

12. 4,265.975 4,265.975 ↓ 1.0 16,264,477 1

Seq Scan on public.line_items li (cost=0.00..588,849.98 rows=16,263,598 width=16) (actual time=0.126..4,265.975 rows=16,264,477 loops=1)

13. 218.876 5,113.525 ↓ 6.8 750,600 1

Hash (cost=370,476.66..370,476.66 rows=109,968 width=8) (actual time=5,113.525..5,113.525 rows=750,600 loops=1)

  • Output: s.id, s.product_id
  • Output: orders.id
  • Output: li.order_id, li.sku_id
  • Buckets: 131072 (originally 131072) Batches: 16 (originally 2) Memory Usage: 3073kB
14. 4,775.014 4,894.649 ↓ 6.8 750,600 1

Bitmap Heap Scan on public.orders (cost=20,442.57..370,476.66 rows=109,968 width=8) (actual time=130.946..4,894.649 rows=750,600 loops=1)

  • Rows Removed by Index Recheck: 2442229
  • Rows Removed by Filter: 225390
  • Recheck Cond: ((orders.placed_at >= '2018-01-01 00:00:00+00'::timestamp with time zone) AND (orders.placed_at <= '2019-01-09 00:00:00+00'::timestamp with time zone))
  • Output: orders.id
  • Heap Blocks: exact=50487 lossy=79876
  • Filter: ((orders.status IS NOT NULL) AND ((orders.status)::text <> 'CANCELLED'::text) AND ((1 & orders.site_id) > 0) AND ((orders.type)::text = ANY ('{WebOrder,CustomerServiceOrder,StoreOrder,KioskOrder,AmazonOrder}'::text[])))
15. 119.635 119.635 ↓ 1.0 975,990 1

Bitmap Index Scan on idx_orders_placed_at (cost=0.00..20,415.08 rows=973,464 width=0) (actual time=119.635..119.635 rows=975,990 loops=1)

  • Index Cond: ((orders.placed_at >= '2018-01-01 00:00:00+00'::timestamp with time zone) AND (orders.placed_at <= '2019-01-09 00:00:00+00'::timestamp with time zone))