explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3jK

Settings
# exclusive inclusive rows x rows loops node
1. 103.788 254,008.789 ↓ 2.3 2,108 1

GroupAggregate (cost=360,274.07..360,408.59 rows=912 width=252) (actual time=253,919.530..254,008.789 rows=2,108 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. 1.152 1.578 ↑ 1.1 129 1

Bitmap Heap Scan on users (cost=9.37..310.22 rows=140 width=8) (actual time=1.482..1.578 rows=129 loops=1)

  • Recheck Cond: (company_id = 22292)
  • Heap Blocks: exact=53
4. 0.426 0.426 ↑ 1.1 129 1

Bitmap Index Scan on users_company_id_idx (cost=0.00..9.33 rows=140 width=0) (actual time=0.426..0.426 rows=129 loops=1)

  • Index Cond: (company_id = 22292)
5.          

CTE selected_line_items

6. 147.026 222,000.170 ↑ 1.5 5,389 1

Finalize GroupAggregate (cost=204,823.50..206,079.47 rows=8,330 width=104) (actual time=221,802.111..222,000.170 rows=5,389 loops=1)

  • Group Key: line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
7. 77.291 221,853.144 ↑ 1.3 5,389 1

Gather Merge (cost=204,823.50..205,763.62 rows=6,942 width=104) (actual time=221,800.474..221,853.144 rows=5,389 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 14.500 221,775.853 ↑ 1.9 1,796 3 / 3

Partial GroupAggregate (cost=203,823.47..203,962.31 rows=3,471 width=104) (actual time=221,766.335..221,775.853 rows=1,796 loops=3)

  • Group Key: line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
9. 24.877 221,761.353 ↑ 1.9 1,797 3 / 3

Sort (cost=203,823.47..203,832.15 rows=3,471 width=46) (actual time=221,759.838..221,761.353 rows=1,797 loops=3)

  • Sort Key: line_items.sellable_id, line_items.order_id, orders.created_at, orders.channel_id, orders.currency_code
  • Sort Method: quicksort Memory: 306kB
  • Worker 0: Sort Method: quicksort Memory: 295kB
  • Worker 1: Sort Method: quicksort Memory: 301kB
10. 19.903 221,736.476 ↑ 1.9 1,797 3 / 3

Nested Loop (cost=136,361.68..203,619.36 rows=3,471 width=46) (actual time=142,890.518..221,736.476 rows=1,797 loops=3)

11. 167,751.570 215,290.833 ↑ 1.6 1,527 3 / 3

Parallel Bitmap Heap Scan on orders (cost=136,361.12..158,935.58 rows=2,486 width=20) (actual time=142,879.880..215,290.833 rows=1,527 loops=3)

  • Recheck 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) AND (company_id = 22292))
  • Rows Removed by Index Recheck: 18757
  • Filter: ((status)::text <> ALL ('{cancelled,refunded}'::text[]))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=16594
12. 7.014 47,539.263 ↓ 0.0 0 1 / 3

BitmapAnd (cost=136,361.12..136,361.12 rows=6,033 width=0) (actual time=142,617.788..142,617.789 rows=0 loops=1)

13. 200.502 200.502 ↑ 1.1 66,910 1 / 3

Bitmap Index Scan on index_orders_on_created_at (cost=0.00..2,879.79 rows=75,522 width=0) (actual time=601.505..601.506 rows=66,910 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))
14. 47,331.747 47,331.747 ↑ 1.0 2,848,916 1 / 3

Bitmap Index Scan on index_orders_on_company_id (cost=0.00..133,478.10 rows=2,859,938 width=0) (actual time=141,995.240..141,995.240 rows=2,848,916 loops=1)

  • Index Cond: (company_id = 22292)
15. 6,425.740 6,425.740 ↑ 55.0 1 4,580 / 3

Index Scan using line_items_order_id_idx on line_items (cost=0.56..17.42 rows=55 width=30) (actual time=4.138..4.209 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))
16. 120.810 253,905.001 ↓ 5.9 5,389 1

Sort (cost=153,884.38..153,886.66 rows=912 width=118) (actual time=253,903.845..253,905.001 rows=5,389 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: 950kB
17. 11.350 253,784.191 ↓ 5.9 5,389 1

Hash Left Join (cost=81,646.84..153,839.54 rows=912 width=118) (actual time=248,017.608..253,784.191 rows=5,389 loops=1)

  • Hash Cond: ((selected_line_items.order_currency_code)::text = (daily_currency_rates.order_currency_code)::text)
18. 17.749 253,771.803 ↓ 5.9 5,389 1

Hash Left Join (cost=81,598.04..153,779.16 rows=912 width=135) (actual time=248,011.301..253,771.803 rows=5,389 loops=1)

  • Hash Cond: ((selected_line_items.sellable_id = allocations.sellable_id) AND (selected_line_items.order_id = allocations.order_id))
19. 94.060 227,581.834 ↓ 5.9 5,389 1

Nested Loop (cost=5.54..72,179.82 rows=912 width=127) (actual time=221,838.146..227,581.834 rows=5,389 loops=1)

20. 9.241 225,569.290 ↓ 5.9 5,389 1

Hash Join (cost=4.99..68,228.83 rows=912 width=131) (actual time=221,822.220..225,569.290 rows=5,389 loops=1)

  • Hash Cond: (sellables.created_by_id = users_cte.id)
21. 65.029 225,558.298 ↑ 1.5 5,389 1

Nested Loop (cost=0.43..68,183.93 rows=8,330 width=131) (actual time=221,815.711..225,558.298 rows=5,389 loops=1)

22. 221,807.193 221,807.193 ↑ 1.5 5,389 1

CTE Scan on selected_line_items (cost=0.00..166.60 rows=8,330 width=116) (actual time=221,803.033..221,807.193 rows=5,389 loops=1)

23. 3,686.076 3,686.076 ↑ 1.0 1 5,389

Index Scan using product_variants_pkey on sellables (cost=0.43..8.17 rows=1 width=15) (actual time=0.684..0.684 rows=1 loops=5,389)

  • Index Cond: (id = selected_line_items.sellable_id)
24. 0.048 1.751 ↑ 1.1 129 1

Hash (cost=2.80..2.80 rows=140 width=8) (actual time=1.751..1.751 rows=129 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
25. 1.703 1.703 ↑ 1.1 129 1

CTE Scan on users_cte (cost=0.00..2.80 rows=140 width=8) (actual time=1.488..1.703 rows=129 loops=1)

26. 1,918.484 1,918.484 ↑ 1.0 1 5,389

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

  • Index Cond: (id = sellables.product_id)
  • Heap Fetches: 4627
27. 26.724 26,172.220 ↓ 5,389.0 5,389 1

Hash (cost=81,592.48..81,592.48 rows=1 width=20) (actual time=26,172.220..26,172.220 rows=5,389 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 359kB
28. 2.293 26,145.496 ↓ 5,389.0 5,389 1

Subquery Scan on allocations (cost=81,592.45..81,592.48 rows=1 width=20) (actual time=26,094.584..26,145.496 rows=5,389 loops=1)

29. 58.327 26,143.203 ↓ 5,389.0 5,389 1

GroupAggregate (cost=81,592.45..81,592.47 rows=1 width=20) (actual time=26,094.356..26,143.203 rows=5,389 loops=1)

  • Group Key: allocation_line_items.sellable_id, allocations_1.order_id, allocations_1.warehouse_id
30. 73.411 26,084.876 ↓ 5,389.0 5,389 1

Sort (cost=81,592.45..81,592.45 rows=1 width=16) (actual time=26,083.796..26,084.876 rows=5,389 loops=1)

  • Sort Key: allocation_line_items.sellable_id, allocations_1.order_id, allocations_1.warehouse_id
  • Sort Method: quicksort Memory: 445kB
31. 221.138 26,011.465 ↓ 5,389.0 5,389 1

Nested Loop (cost=5.55..81,592.44 rows=1 width=16) (actual time=5.447..26,011.465 rows=5,389 loops=1)

  • Join Filter: (selected_line_items_1.sellable_id = allocation_line_items.sellable_id)
  • Rows Removed by Join Filter: 3474
32. 38.636 13,482.674 ↓ 4.7 5,391 1

Hash Join (cost=4.99..68,680.66 rows=1,141 width=16) (actual time=2.814..13,482.674 rows=5,391 loops=1)

  • Hash Cond: (allocations_1.allocated_by_id = users_cte_1.id)
33. 11.664 13,443.944 ↑ 1.6 5,391 1

Nested Loop (cost=0.44..68,632.05 rows=8,707 width=20) (actual time=2.024..13,443.944 rows=5,391 loops=1)

34. 245.397 245.397 ↑ 1.5 5,389 1

CTE Scan on selected_line_items selected_line_items_1 (cost=0.00..166.60 rows=8,330 width=8) (actual time=0.003..245.397 rows=5,389 loops=1)

35. 13,186.883 13,186.883 ↑ 1.0 1 5,389

Index Scan using allocations_order_id_idx on allocations allocations_1 (cost=0.44..8.21 rows=1 width=16) (actual time=2.440..2.447 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
36. 0.053 0.094 ↑ 1.1 129 1

Hash (cost=2.80..2.80 rows=140 width=4) (actual time=0.094..0.094 rows=129 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
37. 0.041 0.041 ↑ 1.1 129 1

CTE Scan on users_cte users_cte_1 (cost=0.00..2.80 rows=140 width=4) (actual time=0.003..0.041 rows=129 loops=1)

38. 12,307.653 12,307.653 ↑ 18.5 2 5,391

Index Scan using allocation_line_items_allocation_id_idx on allocation_line_items (cost=0.56..10.85 rows=37 width=12) (actual time=2.175..2.283 rows=2 loops=5,391)

  • Index Cond: (allocation_id = allocations_1.id)
  • Filter: (deleted_at IS NULL)
39. 0.013 1.038 ↑ 1.7 9 1

Hash (cost=48.62..48.62 rows=15 width=11) (actual time=1.038..1.038 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 1.025 1.025 ↑ 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=1.011..1.025 rows=9 loops=1)

  • Index Cond: ((date = '2019-12-05'::date) AND ((company_currency_code)::text = 'GBP'::text))
Planning time : 18.942 ms
Execution time : 254,043.185 ms