explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7sBG : cast filter to date, use fulfillments table instead of jsonb field

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.035 301.717 ↓ 0.0 0 1

Sort (cost=450,682.87..450,682.88 rows=1 width=678) (actual time=301.717..301.717 rows=0 loops=1)

  • Sort Key: orders_document_storage.created_at DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.000 301.682 ↓ 0.0 0 1

Nested Loop Left Join (cost=60,473.16..450,682.86 rows=1 width=678) (actual time=301.682..301.682 rows=0 loops=1)

  • Join Filter: (sos.id = ssli.supplier_order_id)
3. 0.000 301.682 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,098.91..395,308.53 rows=1 width=651) (actual time=301.682..301.682 rows=0 loops=1)

  • Join Filter: (sos.supplier_id = suppliers.id)
4. 0.001 301.682 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,098.91..395,307.31 rows=1 width=139) (actual time=301.682..301.682 rows=0 loops=1)

5. 0.002 301.681 ↓ 0.0 0 1

Nested Loop (cost=5,098.76..395,307.12 rows=1 width=134) (actual time=301.681..301.681 rows=0 loops=1)

6. 0.001 301.679 ↓ 0.0 0 1

Merge Join (cost=5,098.34..395,281.71 rows=3 width=104) (actual time=301.679..301.679 rows=0 loops=1)

  • Merge Cond: (shopify_supplier_line_items.supplier_order_id = sos.id)
7. 0.478 0.489 ↑ 628,303.0 1 1

GroupAggregate (cost=0.43..382,329.95 rows=628,303 width=80) (actual time=0.489..0.489 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))
8. 0.011 0.011 ↑ 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.008..0.011 rows=3 loops=1)

9. 0.140 301.189 ↓ 0.0 0 1

Sort (cost=5,097.91..5,097.92 rows=6 width=32) (actual time=301.189..301.189 rows=0 loops=1)

  • Sort Key: sos.id
  • Sort Method: quicksort Memory: 25kB
10. 301.049 301.049 ↓ 0.0 0 1

Index Scan using supplier_orders_shopify_created_at_date_index on supplier_orders_shopify sos (cost=0.43..5,097.83 rows=6 width=32) (actual time=301.049..301.049 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 ((('now'::cstring)::date - (created_at)::date) >= 13))
  • Rows Removed by Filter: 9,268
11. 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))
12. 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)
13. 0.000 0.000 ↓ 0.0 0

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

14. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=55,374.25..55,374.28 rows=1 width=25) (never executed)

  • Group Key: ssli.supplier_order_id
15. 0.000 0.000 ↓ 0.0 0

Sort (cost=55,374.25..55,374.26 rows=1 width=12) (never executed)

  • Sort Key: ssli.supplier_order_id
16. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.72..55,374.24 rows=1 width=12) (never executed)

  • Join Filter: (spv.inventory_item_id = sil.inventory_item_id)
17. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.72..55,310.30 rows=1 width=16) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..47,146.40 rows=1 width=16) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.86..47,136.24 rows=1 width=8) (never executed)

  • Filter: (sfs.id IS NULL)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using supplier_orders_shopify_created_at_date_index on supplier_orders_shopify sos_1 (cost=0.43..6,862.59 rows=5,723 width=8) (never executed)

  • Index Cond: ((created_at)::date > (('now'::cstring)::date - 10))
21. 0.000 0.000 ↓ 0.0 0

Index Scan using supplier_fulfillments_shopify_supplier_order_id_index on supplier_fulfillments_shopify sfs (cost=0.42..7.03 rows=1 width=16) (never executed)

  • Index Cond: (sos_1.id = supplier_order_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using shopify_supplier_line_items_supplier_order_id_index on shopify_supplier_line_items ssli (cost=0.43..10.14 rows=2 width=16) (never executed)

  • Index Cond: (supplier_order_id = sos_1.id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using supplier_product_variants_supplier_id_variant_id_unique on supplier_product_variants spv (cost=0.43..8,163.89 rows=1 width=16) (never executed)

  • Index Cond: (ssli.variant_id = variant_id)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on shopify_inventory_levels sil (cost=0.00..44.53 rows=1,553 width=12) (never executed)

Planning time : 40.811 ms
Execution time : 302.518 ms