explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xtHW

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 283.534 ↑ 798.0 1 1

Sort (cost=42,976.61..42,978.61 rows=798 width=65) (actual time=283.533..283.534 rows=1 loops=1)

  • Output: product.code, product.name, product.currency, (sum(order_item.value)), (count(DISTINCT order_item.id))
  • Sort Key: (sum(order_item.value)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=51,415, temp read=266 written=267
2. 15.922 283.527 ↑ 798.0 1 1

GroupAggregate (cost=41,614.74..42,938.15 rows=798 width=65) (actual time=283.527..283.527 rows=1 loops=1)

  • Output: product.code, product.name, product.currency, sum(order_item.value), count(DISTINCT order_item.id)
  • Group Key: product.code, product.name, product.currency
  • Buffers: shared hit=51,415, temp read=266 written=267
3. 25.942 267.605 ↑ 2.0 43,362 1

Sort (cost=41,614.74..41,833.65 rows=87,562 width=38) (actual time=258.580..267.605 rows=43,362 loops=1)

  • Output: product.code, product.name, product.currency, order_item.value, order_item.id
  • Sort Key: product.code, product.name, product.currency
  • Sort Method: external merge Disk: 2,128kB
  • Buffers: shared hit=51,415, temp read=266 written=267
4. 8.760 241.663 ↑ 2.0 43,362 1

Hash Join (cost=4,805.71..32,029.27 rows=87,562 width=38) (actual time=205.033..241.663 rows=43,362 loops=1)

  • Output: product.code, product.name, product.currency, order_item.value, order_item.id
  • Inner Unique: true
  • Hash Cond: (order_item.product_id = product.id)
  • Buffers: shared hit=51,415
5. 86.828 232.389 ↑ 2.0 43,362 1

Hash Join (cost=4,636.75..31,629.24 rows=87,562 width=21) (actual time=204.511..232.389 rows=43,362 loops=1)

  • Output: order_item.value, order_item.id, order_item.product_id
  • Inner Unique: true
  • Hash Cond: (order_item.order_id = "order".id)
  • Buffers: shared hit=51,264
6. 111.406 111.406 ↑ 1.0 667,403 1

Seq Scan on public.order_item (cost=0.00..25,232.99 rows=670,278 width=29) (actual time=0.003..111.406 rows=667,403 loops=1)

  • Output: order_item.id, order_item.status, order_item.order_id, order_item.brand_id, order_item.product_id, order_item.value, order_item.cost, order_item.price, order_item.error_code, order_item.error, order_item.internal_error, order_item.delivery_email, order_item.batch_id, order_item.e_code_id, order_item.delivery_format, order_item.attempts, order_item.order_email_template_id, order_item.price_discount, order_item.subject_line, order_item.action, order_item.encrypted_card_number, order_item.encrypted_card_serial_number, order_item.encrypted_card_pin, order_item.due_date, order_item.cost_discount, order_item.stock_lock_token, order_item.extra_recon_fields, order_item.description, order_item.wrap_primary_id, order_item.wrap_secondary_id, order_item.order_account_used, order_item.billing_model_used, order_item.select_template_id, order_item.select_link_id, order_item.delayed_product_id, order_item.breakage_link_expiry_in_days, order_item.cost_multiplier, order_item.price_multiplier
  • Filter: (order_item.status = 'SUCCESS'::orderitemstatusenum)
  • Rows Removed by Filter: 72,562
  • Buffers: shared hit=15,975
7. 7.256 34.155 ↓ 1.1 43,362 1

Hash (cost=4,125.76..4,125.76 rows=40,879 width=8) (actual time=34.155..34.155 rows=43,362 loops=1)

  • Output: "order".id
  • Buckets: 65,536 Batches: 1 Memory Usage: 2,206kB
  • Buffers: shared hit=35,289
8. 26.899 26.899 ↓ 1.1 43,362 1

Index Scan using ix_order_created on public."order" (cost=0.42..4,125.76 rows=40,879 width=8) (actual time=0.017..26.899 rows=43,362 loops=1)

  • Output: "order".id
  • Index Cond: (("order".created >= '2020-09-01'::date) AND ("order".created <= '2020-09-09'::date))
  • Filter: ("order".status = 'DONE'::orderstatusenum)
  • Buffers: shared hit=35,289
9. 0.162 0.514 ↑ 1.0 798 1

Hash (cost=158.98..158.98 rows=798 width=33) (actual time=0.514..0.514 rows=798 loops=1)

  • Output: product.code, product.name, product.currency, product.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=151
10. 0.352 0.352 ↑ 1.0 798 1

Seq Scan on public.product (cost=0.00..158.98 rows=798 width=33) (actual time=0.005..0.352 rows=798 loops=1)

  • Output: product.code, product.name, product.currency, product.id
  • Buffers: shared hit=151