explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lUC

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 116.197 ↑ 798.0 1 1

Sort (cost=29,326.41..29,328.40 rows=798 width=65) (actual time=116.196..116.197 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=49,656
2. 4.635 116.175 ↑ 798.0 1 1

GroupAggregate (cost=28,818.97..29,287.94 rows=798 width=65) (actual time=116.175..116.175 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=49,653
3. 5.523 111.540 ↑ 2.3 13,366 1

Sort (cost=28,818.97..28,895.47 rows=30,600 width=38) (actual time=110.755..111.540 rows=13,366 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: quicksort Memory: 1,429kB
  • Buffers: shared hit=49,653
4. 12.217 106.017 ↑ 2.3 13,366 1

Gather (cost=4,224.19..26,539.08 rows=30,600 width=38) (actual time=97.386..106.017 rows=13,366 loops=1)

  • Output: product.code, product.name, product.currency, order_item.value, order_item.id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=49,653
5. 1.424 93.800 ↑ 2.9 4,455 3 / 3

Hash Join (cost=3,224.19..22,479.08 rows=12,750 width=38) (actual time=86.994..93.800 rows=4,455 loops=3)

  • 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=49,653
  • Worker 0: actual time=84.302..97.551 rows=7,354 loops=1
  • Buffers: shared hit=18,803
  • Worker 1: actual time=79.660..85.355 rows=5,946 loops=1
  • Buffers: shared hit=15,152
6. 31.226 90.259 ↑ 2.9 4,455 3 / 3

Hash Join (cost=3,055.24..22,276.48 rows=12,750 width=21) (actual time=84.813..90.259 rows=4,455 loops=3)

  • 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=49,142
  • Worker 0: actual time=79.435..90.053 rows=7,354 loops=1
  • Buffers: shared hit=18,623
  • Worker 1: actual time=78.734..83.013 rows=5,946 loops=1
  • Buffers: shared hit=14,972
7. 43.971 43.971 ↑ 1.3 212,469 3 / 3

Parallel Seq Scan on public.order_item (cost=0.00..18,520.27 rows=267,033 width=29) (actual time=0.005..43.971 rows=212,469 loops=3)

  • 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: 24,187
  • Buffers: shared hit=14,805
  • Worker 0: actual time=0.005..47.066 rows=292,623 loops=1
  • Buffers: shared hit=7,176
  • Worker 1: actual time=0.005..37.960 rows=154,824 loops=1
  • Buffers: shared hit=3,525
8. 2.895 15.062 ↑ 1.0 13,366 3 / 3

Hash (cost=2,887.19..2,887.19 rows=13,444 width=8) (actual time=15.061..15.062 rows=13,366 loops=3)

  • Output: "order".id
  • Buckets: 16,384 Batches: 1 Memory Usage: 651kB
  • Buffers: shared hit=34,337
  • Worker 0: actual time=12.958..12.958 rows=13,366 loops=1
  • Buffers: shared hit=11,447
  • Worker 1: actual time=16.218..16.219 rows=13,366 loops=1
  • Buffers: shared hit=11,447
9. 12.167 12.167 ↑ 1.0 13,366 3 / 3

Index Scan using ix_order_created on public."order" (cost=0.42..2,887.19 rows=13,444 width=8) (actual time=0.043..12.167 rows=13,366 loops=3)

  • Output: "order".id
  • Index Cond: (("order".created >= '2020-08-25'::date) AND ("order".created <= '2020-09-02'::date))
  • Filter: ("order".status = 'DONE'::orderstatusenum)
  • Buffers: shared hit=34,337
  • Worker 0: actual time=0.056..10.590 rows=13,366 loops=1
  • Buffers: shared hit=11,447
  • Worker 1: actual time=0.051..12.907 rows=13,366 loops=1
  • Buffers: shared hit=11,447
10. 0.301 2.117 ↑ 1.0 798 3 / 3

Hash (cost=158.98..158.98 rows=798 width=33) (actual time=2.117..2.117 rows=798 loops=3)

  • Output: product.code, product.name, product.currency, product.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=453
  • Worker 0: actual time=4.773..4.774 rows=798 loops=1
  • Buffers: shared hit=151
  • Worker 1: actual time=0.833..0.834 rows=798 loops=1
  • Buffers: shared hit=151
11. 1.816 1.816 ↑ 1.0 798 3 / 3

Seq Scan on public.product (cost=0.00..158.98 rows=798 width=33) (actual time=0.007..1.816 rows=798 loops=3)

  • Output: product.code, product.name, product.currency, product.id
  • Buffers: shared hit=453
  • Worker 0: actual time=0.008..4.511 rows=798 loops=1
  • Buffers: shared hit=151
  • Worker 1: actual time=0.008..0.443 rows=798 loops=1
  • Buffers: shared hit=151