explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MJGG

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

GroupAggregate (cost=62.07..62.22 rows=1 width=252) (actual time=0.126..0.126 rows=0 loops=1)

  • Group Key: users_cte.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.017 0.017 ↑ 127.0 1 1

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

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

CTE selected_line_items

5. 0.000 0.050 ↓ 0.0 0 1

GroupAggregate (cost=17.38..17.42 rows=1 width=104) (actual time=0.050..0.050 rows=0 loops=1)

  • Group Key: line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
6. 0.027 0.050 ↓ 0.0 0 1

Sort (cost=17.38..17.38 rows=1 width=46) (actual time=0.050..0.050 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 0.023 ↓ 0.0 0 1

Nested Loop (cost=0.86..17.37 rows=1 width=46) (actual time=0.023..0.023 rows=0 loops=1)

8. 0.023 0.023 ↓ 0.0 0 1

Index Scan using index_orders_on_company_id on orders (cost=0.43..8.45 rows=1 width=20) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: (company_id = 22292)
  • Filter: (((status)::text <> ALL ('{cancelled,refunded}'::text[])) 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))
9. 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))
10. 0.034 0.125 ↓ 0.0 0 1

Sort (cost=34.31..34.32 rows=1 width=117) (actual time=0.125..0.125 rows=0 loops=1)

  • Sort Key: users_cte.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
11. 0.000 0.091 ↓ 0.0 0 1

Nested Loop Left Join (cost=21.09..34.30 rows=1 width=117) (actual time=0.091..0.091 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))
12. 0.000 0.091 ↓ 0.0 0 1

Nested Loop Left Join (cost=9.05..22.20 rows=1 width=117) (actual time=0.091..0.091 rows=0 loops=1)

  • Join Filter: ((selected_line_items.order_currency_code)::text = (daily_currency_rates.order_currency_code)::text)
13. 0.000 0.091 ↓ 0.0 0 1

Nested Loop (cost=8.63..11.81 rows=1 width=128) (actual time=0.091..0.091 rows=0 loops=1)

14. 0.017 0.091 ↓ 0.0 0 1

Hash Join (cost=8.34..11.37 rows=1 width=132) (actual time=0.090..0.091 rows=0 loops=1)

  • Hash Cond: (users_cte.id = sellables.created_by_id)
15. 0.020 0.020 ↑ 127.0 1 1

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

16. 0.003 0.054 ↓ 0.0 0 1

Hash (cost=8.33..8.33 rows=1 width=132) (actual time=0.052..0.054 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 0.000 0.051 ↓ 0.0 0 1

Nested Loop (cost=0.29..8.33 rows=1 width=132) (actual time=0.051..0.051 rows=0 loops=1)

18. 0.051 0.051 ↓ 0.0 0 1

CTE Scan on selected_line_items (cost=0.00..0.02 rows=1 width=116) (actual time=0.051..0.051 rows=0 loops=1)

19. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (id = sellables.product_id)
  • Heap Fetches: 0
21. 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))
22. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=12.04..12.07 rows=1 width=20) (never executed)

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

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

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

Nested Loop (cost=8.92..12.03 rows=1 width=16) (never executed)

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

Hash Join (cost=8.49..11.52 rows=1 width=16) (never executed)

  • Hash Cond: (users_cte_1.id = allocations.allocated_by_id)
26. 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)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.48..8.48 rows=1 width=20) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..8.48 rows=1 width=20) (never executed)

29. 0.000 0.000 ↓ 0.0 0

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

30. 0.000 0.000 ↓ 0.0 0

Index Scan using allocations_order_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)
  • Filter: ((deleted_at IS NULL) AND (reason IS NULL))
31. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (allocation_id = allocations.id)
  • Filter: (deleted_at IS NULL)
Planning time : 7.125 ms
Execution time : 0.659 ms