explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BV9H : not fulfilled in time

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.029 4,726.953 ↓ 0.0 0 1

Sort (cost=613,408.67..613,408.68 rows=1 width=678) (actual time=4,726.953..4,726.953 rows=0 loops=1)

  • Sort Key: orders_document_storage.created_at DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.002 4,726.924 ↓ 0.0 0 1

Merge Right Join (cost=223,068.83..613,408.66 rows=1 width=678) (actual time=4,726.924..4,726.924 rows=0 loops=1)

  • Merge Cond: (ssli.supplier_order_id = sos.id)
3. 0.032 3,151.983 ↑ 3,924.0 1 1

GroupAggregate (cost=217,939.94..218,038.04 rows=3,924 width=25) (actual time=3,151.983..3,151.983 rows=1 loops=1)

  • Group Key: ssli.supplier_order_id
4. 13.930 3,151.951 ↑ 1,962.0 2 1

Sort (cost=217,939.94..217,949.75 rows=3,924 width=12) (actual time=3,151.949..3,151.951 rows=2 loops=1)

  • Sort Key: ssli.supplier_order_id
  • Sort Method: quicksort Memory: 1,690kB
5. 10.704 3,138.021 ↓ 5.0 19,651 1

Hash Left Join (cost=143,179.73..217,705.72 rows=3,924 width=12) (actual time=1,602.855..3,138.021 rows=19,651 loops=1)

  • Hash Cond: (spv.inventory_item_id = sil.inventory_item_id)
6. 243.008 3,120.649 ↓ 5.0 19,651 1

Hash Left Join (cost=143,115.79..217,627.00 rows=3,924 width=16) (actual time=1,596.142..3,120.649 rows=19,651 loops=1)

  • Hash Cond: (ssli.variant_id = spv.variant_id)
7. 20.123 1,451.041 ↓ 5.1 19,647 1

Nested Loop (cost=0.43..68,754.90 rows=3,888 width=16) (actual time=164.890..1,451.041 rows=19,647 loops=1)

8. 1,309.103 1,309.103 ↓ 4.7 13,535 1

Seq Scan on supplier_orders_shopify sos_1 (cost=0.00..37,696.72 rows=2,857 width=8) (actual time=164.831..1,309.103 rows=13,535 loops=1)

  • Filter: (created_at > (('now'::cstring)::date - 10))
  • Rows Removed by Filter: 1,044,727
9. 121.815 121.815 ↑ 2.0 1 13,535

Index Scan using shopify_supplier_line_items_supplier_order_id_index on shopify_supplier_line_items ssli (cost=0.43..10.85 rows=2 width=16) (actual time=0.008..0.009 rows=1 loops=13,535)

  • Index Cond: (supplier_order_id = sos_1.id)
10. 376.548 1,426.600 ↑ 1.0 1,084,263 1

Hash (cost=124,195.16..124,195.16 rows=1,088,416 width=16) (actual time=1,426.599..1,426.600 rows=1,084,263 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,439kB
11. 1,050.052 1,050.052 ↑ 1.0 1,084,263 1

Seq Scan on supplier_product_variants spv (cost=0.00..124,195.16 rows=1,088,416 width=16) (actual time=0.027..1,050.052 rows=1,084,263 loops=1)

12. 0.689 6.668 ↑ 1.0 1,553 1

Hash (cost=44.53..44.53 rows=1,553 width=12) (actual time=6.668..6.668 rows=1,553 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 89kB
13. 5.979 5.979 ↑ 1.0 1,553 1

Seq Scan on shopify_inventory_levels sil (cost=0.00..44.53 rows=1,553 width=12) (actual time=0.830..5.979 rows=1,553 loops=1)

14. 0.004 1,574.939 ↓ 0.0 0 1

Materialize (cost=5,128.89..395,321.53 rows=1 width=651) (actual time=1,574.939..1,574.939 rows=0 loops=1)

15. 0.000 1,574.935 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,128.89..395,321.53 rows=1 width=651) (actual time=1,574.935..1,574.935 rows=0 loops=1)

  • Join Filter: (sos.supplier_id = suppliers.id)
16. 0.002 1,574.935 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,128.89..395,320.30 rows=1 width=139) (actual time=1,574.934..1,574.935 rows=0 loops=1)

17. 0.001 1,574.933 ↓ 0.0 0 1

Nested Loop (cost=5,128.74..395,320.12 rows=1 width=134) (actual time=1,574.933..1,574.933 rows=0 loops=1)

18. 0.002 1,574.932 ↓ 0.0 0 1

Merge Join (cost=5,128.31..395,311.65 rows=1 width=104) (actual time=1,574.932..1,574.932 rows=0 loops=1)

  • Merge Cond: (shopify_supplier_line_items.supplier_order_id = sos.id)
19. 1.072 1.090 ↑ 628,303.0 1 1

GroupAggregate (cost=0.43..382,329.95 rows=628,303 width=80) (actual time=1.090..1.090 rows=1 loops=1)

  • Group Key: shopify_supplier_line_items.supplier_order_id
  • Filter: ((count(*) FILTER (WHERE ((shopify_supplier_line_items.sku)::text ~~ 'cusdes-%'::text)) > 0) OR (count(*) FILTER (WHERE (lower((shopify_supplier_line_items.sku)::text) ~~ 'cusdes_hp%'::text)) > 0) OR (count(*) FILTER (WHERE (lower((shopify_supplier_line_items.sku)::text) ~~ 'cusdes_dp%'::text)) > 0) OR (count(*) FILTER (WHERE (lower((shopify_supplier_line_items.sku)::text) ~~ 'cusdes_em%'::text)) > 0) OR (count(*) FILTER (WHERE (lower((shopify_supplier_line_items.sku)::text) !~~ 'cusdes%'::text)) > 0))
20. 0.018 0.018 ↑ 496,741.0 3 1

Index Scan using shopify_supplier_line_items_supplier_order_id_index on shopify_supplier_line_items (cost=0.43..251,532.77 rows=1,490,223 width=61) (actual time=0.013..0.018 rows=3 loops=1)

21. 0.014 1,573.840 ↓ 0.0 0 1

Sort (cost=5,127.89..5,127.89 rows=1 width=32) (actual time=1,573.840..1,573.840 rows=0 loops=1)

  • Sort Key: sos.id
  • Sort Method: quicksort Memory: 25kB
22. 1,573.826 1,573.826 ↓ 0.0 0 1

Index Scan using supplier_orders_shopify_created_at_date_index on supplier_orders_shopify sos (cost=0.43..5,127.88 rows=1 width=32) (actual time=1,573.826..1,573.826 rows=0 loops=1)

  • Index Cond: ((created_at)::date >= '2020-09-01'::date)
  • Filter: (((data ->> 'cancelled_at'::text) IS NULL) AND (supplier_id = ANY ('{3,10,2}'::integer[])) AND (jsonb_array_length((data -> 'fulfillments'::text)) = 0) AND ((('now'::cstring)::date - (created_at)::date) >= 13))
  • Rows Removed by Filter: 9,268
23. 0.000 0.000 ↓ 0.0 0

Index Scan using orders_document_storage_pkey on orders_document_storage (cost=0.43..8.46 rows=1 width=38) (never executed)

  • Index Cond: (id = sos.store_order_id)
  • Filter: (((created_at)::date >= '2020-09-01'::date) AND ((created_at)::date <= '2020-09-08'::date) AND ((shipping_address ->> 'country_code'::text) <> 'HK'::text))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using stores_pkey on stores s (cost=0.14..0.18 rows=1 width=13) (never executed)

  • Index Cond: (orders_document_storage.store_id = id)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on suppliers (cost=0.00..1.10 rows=10 width=520) (never executed)

Planning time : 52.779 ms
Execution time : 4,727.712 ms