explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XIij7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 301.391 ↓ 0.0 0 1

GroupAggregate (cost=70.46..70.61 rows=1 width=252) (actual time=301.391..301.391 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.020 0.020 ↑ 127.0 1 1

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

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

CTE selected_line_items

5. 0.002 301.326 ↓ 0.0 0 1

GroupAggregate (cost=25.67..25.71 rows=1 width=108) (actual time=301.326..301.326 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.020 301.324 ↓ 0.0 0 1

Sort (cost=25.67..25.67 rows=1 width=50) (actual time=301.324..301.324 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.001 301.304 ↓ 0.0 0 1

Nested Loop (cost=1.00..25.66 rows=1 width=50) (actual time=301.304..301.304 rows=0 loops=1)

8. 0.001 301.303 ↓ 0.0 0 1

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

9. 301.302 301.302 ↓ 0.0 0 1

Index Scan using index_orders_on_company_id on orders (cost=0.43..8.45 rows=1 width=24) (actual time=301.302..301.302 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))
10. 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))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = orders.created_by_id)
  • Filter: (company_id = 22292)
12. 0.022 301.391 ↓ 0.0 0 1

Sort (cost=34.41..34.41 rows=1 width=117) (actual time=301.391..301.391 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
13. 0.001 301.369 ↓ 0.0 0 1

Nested Loop Left Join (cost=13.14..34.40 rows=1 width=117) (actual time=301.369..301.369 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))
14. 0.000 301.368 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.03..22.22 rows=1 width=117) (actual time=301.368..301.368 rows=0 loops=1)

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

Nested Loop (cost=0.61..11.84 rows=1 width=128) (actual time=301.368..301.368 rows=0 loops=1)

16. 0.000 301.367 ↓ 0.0 0 1

Nested Loop (cost=0.32..11.38 rows=1 width=140) (actual time=301.367..301.367 rows=0 loops=1)

  • Join Filter: (users_cte.id = sellables.created_by_id)
17. 0.016 301.367 ↓ 0.0 0 1

Hash Join (cost=0.03..3.06 rows=1 width=124) (actual time=301.367..301.367 rows=0 loops=1)

  • Hash Cond: (users_cte.company_id = selected_line_items.company_id)
18. 0.022 0.022 ↑ 127.0 1 1

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

19. 0.003 301.329 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=120) (actual time=301.327..301.329 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 301.326 301.326 ↓ 0.0 0 1

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

21. 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)
22. 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)
23. 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))
24. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=12.11..12.14 rows=1 width=20) (never executed)

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

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

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

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

  • Join Filter: (selected_line_items_1.sellable_id = allocation_line_items.sellable_id)
27. 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)
28. 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)

29. 0.000 0.000 ↓ 0.0 0

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

30. 0.000 0.000 ↓ 0.0 0

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

31. 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)

32. 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)
33. 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)
Planning time : 18.639 ms
Execution time : 301.806 ms