explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E4rM : Optimization for: plan #DK18

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.563 12,257.057 ↑ 14.7 37 1

Hash Left Join (cost=498,147.95..756,942.10 rows=543 width=69) (actual time=11,248.184..12,257.057 rows=37 loops=1)

  • Hash Cond: (o.store_id = s.id)
2. 112.029 12,256.338 ↑ 14.7 37 1

Merge Right Join (cost=498,090.06..756,861.80 rows=543 width=63) (actual time=11,247.998..12,256.338 rows=37 loops=1)

  • Merge Cond: (order_line_items.order_id = o.id)
  • Filter: ((o.fulfillment_status IS NULL) OR (((o.fulfillment_status)::text = 'partial'::text) AND (COALESCE(refunds.refunded_line_items_count, '0'::bigint) <> (count(*) FILTER (WHERE ((order_line_items.fulfillment_status IS NULL) OR ((order_line_items.fulfillment_status)::text <> 'fulfilled'::text)))))))
  • Rows Removed by Filter: 9
3. 1,267.779 6,758.989 ↓ 2.3 1,561,788 1

GroupAggregate (cost=0.43..249,872.18 rows=687,181 width=24) (actual time=0.029..6,758.989 rows=1,561,788 loops=1)

  • Group Key: order_line_items.order_id
4. 5,491.210 5,491.210 ↑ 1.0 2,306,022 1

Index Scan using order_line_items_order_id_index on order_line_items (cost=0.43..219,817.17 rows=2,318,320 width=18) (actual time=0.015..5,491.210 rows=2,306,022 loops=1)

5. 0.048 5,385.320 ↑ 200.2 46 1

Materialize (cost=498,089.63..498,315.70 rows=9,211 width=47) (actual time=5,382.813..5,385.320 rows=46 loops=1)

6. 5.272 5,385.272 ↑ 200.2 46 1

Merge Left Join (cost=498,089.63..498,292.68 rows=9,211 width=47) (actual time=5,382.809..5,385.272 rows=46 loops=1)

  • Merge Cond: (o.id = refunds.order_id)
7. 0.148 2,342.457 ↑ 200.2 46 1

Sort (cost=422,815.25..422,838.28 rows=9,211 width=39) (actual time=2,342.399..2,342.457 rows=46 loops=1)

  • Sort Key: o.id
  • Sort Method: quicksort Memory: 28kB
8. 444.882 2,342.309 ↑ 200.2 46 1

Bitmap Heap Scan on orders_document_storage o (cost=9,538.25..422,208.74 rows=9,211 width=39) (actual time=1,905.346..2,342.309 rows=46 loops=1)

  • Recheck Cond: (((created_at)::date >= '2020-04-01'::date) AND ((created_at)::date <= '2020-06-30'::date))
  • Rows Removed by Index Recheck: 91,208
  • Filter: ((cancelled_at IS NULL) AND ((internal_status)::text <> 'cancelled'::text) AND ((financial_status)::text <> 'refunded'::text) AND ((fulfillment_status IS NULL) OR ((fulfillment_status)::text = 'partial'::text)) AND (lower(tags) !~~ '%id%'::text) AND (lower(tags) !~~ '%missing%'::text) AND (lower(tags) !~~ '%china-change%'::text) AND (lower(tags) !~~ '%info%'::text) AND (lower(tags) !~~ '%high-shipping%'::text) AND ((snoozed_until IS NULL) OR (snoozed_until < now())))
  • Rows Removed by Filter: 212,558
  • Heap Blocks: exact=25,752 lossy=79,671
9. 1,897.427 1,897.427 ↓ 1.0 219,804 1

Bitmap Index Scan on orders_document_storage_created_at_date_index (cost=0.00..9,535.95 rows=213,952 width=0) (actual time=1,897.427..1,897.427 rows=219,804 loops=1)

  • Index Cond: (((created_at)::date >= '2020-04-01'::date) AND ((created_at)::date <= '2020-06-30'::date))
10. 69.655 3,037.543 ↓ 2.1 65,359 1

Sort (cost=75,274.38..75,351.96 rows=31,031 width=16) (actual time=3,029.536..3,037.543 rows=65,359 loops=1)

  • Sort Key: refunds.order_id
  • Sort Method: external sort Disk: 1,928kB
11. 6.506 2,967.888 ↓ 2.1 65,560 1

Subquery Scan on refunds (cost=72,338.63..72,959.25 rows=31,031 width=16) (actual time=2,937.200..2,967.888 rows=65,560 loops=1)

12. 96.116 2,961.382 ↓ 2.1 65,560 1

HashAggregate (cost=72,338.63..72,648.94 rows=31,031 width=16) (actual time=2,937.200..2,961.382 rows=65,560 loops=1)

  • Group Key: sr.order_id
13. 58.576 2,865.266 ↓ 2.1 65,976 1

Nested Loop (cost=0.43..72,178.99 rows=31,928 width=8) (actual time=1.578..2,865.266 rows=65,976 loops=1)

14. 2,344.858 2,344.858 ↓ 2.1 65,976 1

Seq Scan on shopify_refunds sr (cost=0.00..20,579.74 rows=31,928 width=8) (actual time=0.080..2,344.858 rows=65,976 loops=1)

  • Filter: (jsonb_array_length(refund_line_items) > 0)
  • Rows Removed by Filter: 29,726
15. 461.832 461.832 ↑ 1.0 1 65,976

Index Only Scan using orders_document_storage_pkey on orders_document_storage (cost=0.43..1.61 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=65,976)

  • Index Cond: (id = sr.order_id)
  • Heap Fetches: 6,954
16. 0.028 0.156 ↑ 1.0 173 1

Hash (cost=55.73..55.73 rows=173 width=13) (actual time=0.156..0.156 rows=173 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
17. 0.128 0.128 ↑ 1.0 173 1

Seq Scan on stores s (cost=0.00..55.73 rows=173 width=13) (actual time=0.007..0.128 rows=173 loops=1)

Planning time : 10.816 ms
Execution time : 12,258.051 ms