explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nMoT

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 6.632 ↓ 0.0 0 1

GroupAggregate (cost=1,336.18..1,336.33 rows=1 width=252) (actual time=6.632..6.632 rows=0 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 users_cte

3. 0.023 0.023 ↑ 127.0 1 1

Seq Scan on users (cost=0.00..10.34 rows=127 width=8) (actual time=0.023..0.023 rows=1 loops=1)

  • Filter: (company_id = 22292)
  • Rows Removed by Filter: 1
4.          

CTE selected_line_items

5. 0.001 6.568 ↓ 0.0 0 1

GroupAggregate (cost=431.61..433.78 rows=51 width=108) (actual time=6.568..6.568 rows=0 loops=1)

  • Group Key: users_1.company_id, line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
6. 0.023 6.567 ↓ 0.0 0 1

Sort (cost=431.61..431.74 rows=51 width=50) (actual time=6.567..6.567 rows=0 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: 25kB
7. 0.000 6.544 ↓ 0.0 0 1

Nested Loop (cost=12.78..430.17 rows=51 width=50) (actual time=6.544..6.544 rows=0 loops=1)

8. 0.033 6.544 ↓ 0.0 0 1

Hash Join (cost=12.35..37.81 rows=44 width=24) (actual time=6.544..6.544 rows=0 loops=1)

  • Hash Cond: (orders.created_by_id = users_1.id)
9. 6.217 6.217 ↓ 1.7 111 1

Index Scan using index_orders_on_created_at on orders (cost=0.43..25.72 rows=65 width=24) (actual time=0.064..6.217 rows=111 loops=1)

  • Index Cond: ((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: 4
10. 0.032 0.294 ↑ 1.0 127 1

Hash (cost=10.34..10.34 rows=127 width=8) (actual time=0.294..0.294 rows=127 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
11. 0.262 0.262 ↑ 1.0 127 1

Seq Scan on users users_1 (cost=0.00..10.34 rows=127 width=8) (actual time=0.003..0.262 rows=127 loops=1)

  • Filter: (company_id = 22292)
  • Rows Removed by Filter: 60
12. 0.000 0.000 ↓ 0.0 0

Index Scan using line_items_order_id_idx on line_items (cost=0.43..8.90 rows=2 width=30) (never executed)

  • Index Cond: (order_id = orders.id)
  • Filter: ((deleted_at > '2019-12-05 00:00:00'::timestamp without time zone) OR (deleted_at IS NULL))
13. 0.022 6.631 ↓ 0.0 0 1

Sort (cost=892.06..892.07 rows=1 width=117) (actual time=6.631..6.631 rows=0 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: 25kB
14. 0.000 6.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=470.30..892.05 rows=1 width=117) (actual time=6.609..6.609 rows=0 loops=1)

  • Join Filter: ((allocation_line_items.sellable_id = selected_line_items.sellable_id) AND (allocations.order_id = selected_line_items.order_id))
15. 0.000 6.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.65..424.35 rows=1 width=117) (actual time=6.609..6.609 rows=0 loops=1)

  • Join Filter: ((selected_line_items.order_currency_code)::text = (daily_currency_rates.order_currency_code)::text)
16. 0.001 6.609 ↓ 0.0 0 1

Nested Loop (cost=2.24..413.96 rows=1 width=128) (actual time=6.608..6.609 rows=0 loops=1)

17. 0.000 6.608 ↓ 0.0 0 1

Nested Loop (cost=1.95..413.50 rows=1 width=140) (actual time=6.608..6.608 rows=0 loops=1)

  • Join Filter: (users_cte.id = sellables.created_by_id)
18. 0.013 6.608 ↓ 0.0 0 1

Hash Join (cost=1.66..5.18 rows=51 width=124) (actual time=6.608..6.608 rows=0 loops=1)

  • Hash Cond: (users_cte.company_id = selected_line_items.company_id)
19. 0.026 0.026 ↑ 127.0 1 1

CTE Scan on users_cte (cost=0.00..2.54 rows=127 width=8) (actual time=0.026..0.026 rows=1 loops=1)

20. 0.001 6.569 ↓ 0.0 0 1

Hash (cost=1.02..1.02 rows=51 width=120) (actual time=6.568..6.569 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 6.568 6.568 ↓ 0.0 0 1

CTE Scan on selected_line_items (cost=0.00..1.02 rows=51 width=120) (actual time=6.568..6.568 rows=0 loops=1)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using sellables_pkey on sellables (cost=0.29..7.99 rows=1 width=16) (never executed)

  • Index Cond: (id = selected_line_items.sellable_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using products_pkey on products (cost=0.29..0.45 rows=1 width=8) (never executed)

  • Index Cond: (id = sellables.product_id)
  • Filter: (sellables.created_by_id = created_by_id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using uniq_index_reports_daily_currency_rates on daily_currency_rates (cost=0.42..10.36 rows=2 width=8) (never executed)

  • Index Cond: ((date = '2019-12-05'::date) AND ((company_currency_code)::text = 'GBP'::text))
25. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=467.65..467.67 rows=1 width=20) (never executed)

  • Group Key: allocation_line_items.sellable_id, allocations.order_id, allocations.warehouse_id
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=467.65..467.65 rows=1 width=16) (never executed)

  • Sort Key: allocation_line_items.sellable_id, allocations.order_id, allocations.warehouse_id
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..467.64 rows=1 width=16) (never executed)

  • Join Filter: (allocations.allocated_by_id = users_cte_1.id)
28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..463.51 rows=1 width=20) (never executed)

  • Join Filter: (selected_line_items_1.sellable_id = allocation_line_items.sellable_id)
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..432.35 rows=53 width=20) (never executed)

30. 0.000 0.000 ↓ 0.0 0

CTE Scan on selected_line_items selected_line_items_1 (cost=0.00..1.02 rows=51 width=8) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using allocations_order_id_warehouse_id_idx on allocations (cost=0.43..8.45 rows=1 width=16) (never executed)

  • Index Cond: (order_id = selected_line_items_1.order_id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using allocation_line_items_allocation_id_idx on allocation_line_items (cost=0.43..0.56 rows=2 width=12) (never executed)

  • Index Cond: (allocation_id = allocations.id)
  • Filter: (deleted_at IS NULL)
33. 0.000 0.000 ↓ 0.0 0

CTE Scan on users_cte users_cte_1 (cost=0.00..2.54 rows=127 width=4) (never executed)

Planning time : 12.787 ms
Execution time : 7.110 ms