explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2nF : Optimization for: cast filter to date, use fulfillments table instead of jsonb field; plan #7sBG

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 182.486 59,678.490 ↓ 15,962.5 31,925 1

Sort (cost=19,853.46..19,853.47 rows=2 width=629) (actual time=59,658.712..59,678.490 rows=31,925 loops=1)

  • Sort Key: orders_document_storage.created_at DESC
  • Sort Method: external merge Disk: 3,104kB
2. 21,316.501 59,496.004 ↓ 15,962.5 31,925 1

Nested Loop Left Join (cost=1,998.62..19,853.45 rows=2 width=629) (actual time=259.472..59,496.004 rows=31,925 loops=1)

3. 77.098 38,051.803 ↓ 15,962.5 31,925 1

Nested Loop Left Join (cost=1,998.48..19,852.74 rows=2 width=67) (actual time=252.032..38,051.803 rows=31,925 loops=1)

4. 61.663 37,815.080 ↓ 15,962.5 31,925 1

Nested Loop (cost=1,998.34..19,852.39 rows=2 width=62) (actual time=252.013..37,815.080 rows=31,925 loops=1)

5. 7,222.364 7,469.906 ↓ 480.2 33,611 1

Bitmap Heap Scan on supplier_orders_shopify sos (cost=1,997.91..19,259.49 rows=70 width=32) (actual time=249.281..7,469.906 rows=33,611 loops=1)

  • Recheck Cond: ((created_at)::date >= '2020-08-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: 18,308
  • Heap Blocks: exact=5,652
6. 247.542 247.542 ↓ 1.3 54,849 1

Bitmap Index Scan on supplier_orders_shopify_created_at_date_index (cost=0.00..1,997.89 rows=43,395 width=0) (actual time=247.541..247.542 rows=54,849 loops=1)

  • Index Cond: ((created_at)::date >= '2020-08-01'::date)
7. 30,283.511 30,283.511 ↑ 1.0 1 33,611

Index Scan using orders_document_storage_pkey on orders_document_storage (cost=0.43..8.46 rows=1 width=38) (actual time=0.900..0.901 rows=1 loops=33,611)

  • Index Cond: (id = sos.store_order_id)
  • Filter: (((created_at)::date >= '2020-08-01'::date) AND ((created_at)::date <= '2020-09-08'::date) AND ((shipping_address ->> 'country_code'::text) <> 'HK'::text))
  • Rows Removed by Filter: 0
8. 159.625 159.625 ↑ 1.0 1 31,925

Index Scan using stores_pkey on stores s (cost=0.14..0.16 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=31,925)

  • Index Cond: (orders_document_storage.store_id = id)
9. 127.700 127.700 ↑ 1.0 1 31,925

Index Scan using suppliers_pkey on suppliers (cost=0.14..0.32 rows=1 width=520) (actual time=0.003..0.004 rows=1 loops=31,925)

  • Index Cond: (sos.supplier_id = id)
Planning time : 20.900 ms
Execution time : 59,694.170 ms