explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kkZt

Settings
# exclusive inclusive rows x rows loops node
1. 17.957 197.704 ↓ 11.8 2,108 1

GroupAggregate (cost=6,166.44..6,192.84 rows=179 width=252) (actual time=178.352..197.704 rows=2,108 loops=1)

  • Group Key: selected_line_items.company_id, sellables.id, allocations.warehouse_id, selected_line_items.order_channel_id, (((selected_line_items.created_at + '-05:00:00'::interval second))::date), (((selected_line_items.order_created_at + '-05:00:00'::interval second))::date)
2.          

CTE selected_line_items

3. 8.674 45.692 ↓ 30.1 5,389 1

GroupAggregate (cost=2,872.68..2,880.28 rows=179 width=108) (actual time=35.457..45.692 rows=5,389 loops=1)

  • Group Key: line_items.sellable_id, line_items.order_id, users_1.company_id, orders.created_at, orders.channel_id, orders.currency_code
4. 5.754 37.018 ↓ 30.1 5,392 1

Sort (cost=2,872.68..2,873.12 rows=179 width=50) (actual time=35.442..37.018 rows=5,392 loops=1)

  • Sort Key: line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
  • Sort Method: quicksort Memory: 951kB
5. 3.743 31.264 ↓ 30.1 5,392 1

Nested Loop (cost=10.41..2,865.98 rows=179 width=50) (actual time=0.296..31.264 rows=5,392 loops=1)

6. 2.351 9.201 ↓ 35.8 4,580 1

Nested Loop (cost=9.85..1,405.67 rows=128 width=24) (actual time=0.279..9.201 rows=4,580 loops=1)

7. 0.129 0.142 ↑ 1.0 129 1

Bitmap Heap Scan on users users_1 (cost=9.28..296.27 rows=129 width=8) (actual time=0.022..0.142 rows=129 loops=1)

  • Recheck Cond: (company_id = 22292)
  • Heap Blocks: exact=53
8. 0.013 0.013 ↑ 1.0 129 1

Bitmap Index Scan on users_company_id_idx (cost=0.00..9.25 rows=129 width=0) (actual time=0.013..0.013 rows=129 loops=1)

  • Index Cond: (company_id = 22292)
9. 6.708 6.708 ↓ 36.0 36 129

Index Scan using orders_created_by_id_created_at_idx on orders (cost=0.56..8.59 rows=1 width=24) (actual time=0.004..0.052 rows=36 loops=129)

  • Index Cond: ((created_by_id = users_1.id) AND (created_at >= '2019-12-05 05:00:00'::timestamp without time zone) AND (created_at < '2019-12-06 05:00:00'::timestamp without time zone))
  • Filter: ((status)::text <> ALL ('{cancelled,refunded}'::text[]))
  • Rows Removed by Filter: 0
10. 18.320 18.320 ↑ 55.0 1 4,580

Index Scan using line_items_order_id_idx on line_items (cost=0.56..10.86 rows=55 width=30) (actual time=0.003..0.004 rows=1 loops=4,580)

  • Index Cond: (order_id = orders.id)
  • Filter: ((deleted_at > '2019-12-05 00:00:00'::timestamp without time zone) OR (deleted_at IS NULL))
11. 6.413 179.747 ↓ 30.1 5,389 1

Sort (cost=3,286.15..3,286.60 rows=179 width=118) (actual time=178.312..179.747 rows=5,389 loops=1)

  • Sort Key: selected_line_items.company_id, sellables.id, allocations.warehouse_id, selected_line_items.order_channel_id, (((selected_line_items.created_at + '-05:00:00'::interval second))::date), (((selected_line_items.order_created_at + '-05:00:00'::interval second))::date)
  • Sort Method: quicksort Memory: 950kB
12. 6.120 173.334 ↓ 30.1 5,389 1

Nested Loop (cost=1,594.48..3,279.45 rows=179 width=118) (actual time=137.931..173.334 rows=5,389 loops=1)

13. 5.323 156.436 ↓ 30.1 5,389 1

Nested Loop (cost=1,593.92..3,154.90 rows=179 width=130) (actual time=137.904..156.436 rows=5,389 loops=1)

14. 2.441 140.335 ↓ 30.1 5,389 1

Hash Right Join (cost=1,593.49..1,641.90 rows=179 width=123) (actual time=137.891..140.335 rows=5,389 loops=1)

  • Hash Cond: ((daily_currency_rates.order_currency_code)::text = (selected_line_items.order_currency_code)::text)
15. 0.031 0.031 ↑ 1.7 9 1

Index Scan using uniq_index_reports_daily_currency_rates on daily_currency_rates (cost=0.41..48.62 rows=15 width=11) (actual time=0.012..0.031 rows=9 loops=1)

  • Index Cond: ((date = '2019-12-05'::date) AND ((company_currency_code)::text = 'GBP'::text))
16. 2.593 137.863 ↓ 30.1 5,389 1

Hash (cost=1,590.84..1,590.84 rows=179 width=132) (actual time=137.862..137.863 rows=5,389 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 621kB
17. 5.358 135.270 ↓ 30.1 5,389 1

Merge Left Join (cost=1,589.44..1,590.84 rows=179 width=132) (actual time=121.649..135.270 rows=5,389 loops=1)

  • Merge Cond: ((selected_line_items.sellable_id = allocation_line_items.sellable_id) AND (selected_line_items.order_id = allocations.order_id))
18. 4.313 55.027 ↓ 30.1 5,389 1

Sort (cost=10.28..10.73 rows=179 width=120) (actual time=53.881..55.027 rows=5,389 loops=1)

  • Sort Key: selected_line_items.sellable_id, selected_line_items.order_id
  • Sort Method: quicksort Memory: 950kB
19. 50.714 50.714 ↓ 30.1 5,389 1

CTE Scan on selected_line_items (cost=0.00..3.58 rows=179 width=120) (actual time=35.461..50.714 rows=5,389 loops=1)

20. 2.618 74.885 ↓ 5,389.0 5,389 1

Materialize (cost=1,579.16..1,579.20 rows=1 width=20) (actual time=67.761..74.885 rows=5,389 loops=1)

21. 3.305 72.267 ↓ 5,389.0 5,389 1

GroupAggregate (cost=1,579.16..1,579.19 rows=1 width=20) (actual time=67.758..72.267 rows=5,389 loops=1)

  • Group Key: allocation_line_items.sellable_id, allocations.order_id, allocations.warehouse_id
22. 3.318 68.962 ↓ 5,389.0 5,389 1

Sort (cost=1,579.16..1,579.17 rows=1 width=16) (actual time=67.752..68.962 rows=5,389 loops=1)

  • Sort Key: allocation_line_items.sellable_id, allocations.order_id, allocations.warehouse_id
  • Sort Method: quicksort Memory: 445kB
23. 6.622 65.644 ↓ 5,389.0 5,389 1

Nested Loop (cost=1.28..1,579.15 rows=1 width=16) (actual time=0.043..65.644 rows=5,389 loops=1)

  • Join Filter: (selected_line_items_1.sellable_id = allocation_line_items.sellable_id)
  • Rows Removed by Join Filter: 3474
24. 6.793 37.458 ↓ 1,347.8 5,391 1

Nested Loop (cost=0.72..1,572.07 rows=4 width=16) (actual time=0.030..37.458 rows=5,391 loops=1)

25. 7.127 25.274 ↓ 30.6 5,391 1

Nested Loop (cost=0.44..1,518.82 rows=176 width=20) (actual time=0.020..25.274 rows=5,391 loops=1)

26. 1.980 1.980 ↓ 30.1 5,389 1

CTE Scan on selected_line_items selected_line_items_1 (cost=0.00..3.58 rows=179 width=8) (actual time=0.001..1.980 rows=5,389 loops=1)

27. 16.167 16.167 ↑ 1.0 1 5,389

Index Scan using allocations_order_id_idx on allocations (cost=0.44..8.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=5,389)

  • Index Cond: (order_id = selected_line_items_1.order_id)
  • Filter: ((deleted_at IS NULL) AND (reason IS NULL))
  • Rows Removed by Filter: 0
28. 5.391 5.391 ↑ 1.0 1 5,391

Index Scan using users_pkey on users (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5,391)

  • Index Cond: (id = allocations.allocated_by_id)
  • Filter: (company_id = 22292)
29. 21.564 21.564 ↑ 18.5 2 5,391

Index Scan using allocation_line_items_allocation_id_idx on allocation_line_items (cost=0.56..1.31 rows=37 width=12) (actual time=0.003..0.004 rows=2 loops=5,391)

  • Index Cond: (allocation_id = allocations.id)
  • Filter: (deleted_at IS NULL)
30. 10.778 10.778 ↑ 1.0 1 5,389

Index Scan using product_variants_pkey on sellables (cost=0.43..8.45 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=5,389)

  • Index Cond: (id = selected_line_items.sellable_id)
31. 10.778 10.778 ↑ 1.0 1 5,389

Index Only Scan using products_pkey on products (cost=0.56..0.69 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,389)

  • Index Cond: (id = sellables.product_id)
  • Heap Fetches: 4555