explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DK18

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.796 822,969.162 ↑ 14.7 37 1

Hash Left Join (cost=422,874.01..5,967,474.86 rows=543 width=69) (actual time=564,906.665..822,969.162 rows=37 loops=1)

  • Hash Cond: (o.store_id = s.id)
2. 116.323 822,968.145 ↑ 14.7 37 1

Merge Right Join (cost=422,816.11..5,967,394.57 rows=543 width=63) (actual time=564,906.384..822,968.145 rows=37 loops=1)

  • Merge Cond: (orders_document_storage.id = o.id)
  • Filter: ((o.fulfillment_status IS NULL) OR (((o.fulfillment_status)::text = 'partial'::text) AND (COALESCE((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. 247.532 780,411.126 ↑ 23.8 65,359 1

GroupAggregate (cost=0.43..5,264,711.25 rows=1,553,372 width=16) (actual time=117.976..780,411.126 rows=65,359 loops=1)

  • Group Key: orders_document_storage.id
4. 2,235.230 780,163.594 ↑ 779.3 65,776 1

Nested Loop (cost=0.43..4,992,871.15 rows=51,261,276 width=8) (actual time=66.182..780,163.594 rows=65,776 loops=1)

5. 38,301.476 38,301.476 ↓ 1.0 1,553,838 1

Index Scan using orders_document_storage_pkey on orders_document_storage (cost=0.43..1,761,857.38 rows=1,553,372 width=26) (actual time=0.017..38,301.476 rows=1,553,838 loops=1)

6. 739,626.888 739,626.888 ↓ 0.0 0 1,553,838

Function Scan on jsonb_array_elements r (cost=0.01..1.76 rows=33 width=0) (actual time=0.476..0.476 rows=0 loops=1,553,838)

  • Filter: (jsonb_array_length((value -> 'refund_line_items'::text)) > 0)
  • Rows Removed by Filter: 0
7. 0.082 42,440.696 ↑ 200.2 46 1

Materialize (cost=422,815.68..683,104.98 rows=9,211 width=55) (actual time=16,258.883..42,440.696 rows=46 loops=1)

8. 239.957 42,440.614 ↑ 200.2 46 1

Merge Left Join (cost=422,815.68..683,081.95 rows=9,211 width=55) (actual time=16,258.880..42,440.614 rows=46 loops=1)

  • Merge Cond: (o.id = order_line_items.order_id)
9. 0.203 6,398.281 ↑ 200.2 46 1

Sort (cost=422,815.25..422,838.28 rows=9,211 width=39) (actual time=6,398.197..6,398.281 rows=46 loops=1)

  • Sort Key: o.id
  • Sort Method: quicksort Memory: 28kB
10. 982.456 6,398.078 ↑ 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=5,430.168..6,398.078 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,184
  • 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
11. 5,415.622 5,415.622 ↓ 1.0 219,781 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=5,415.622..5,415.622 rows=219,781 loops=1)

  • Index Cond: (((created_at)::date >= '2020-04-01'::date) AND ((created_at)::date <= '2020-06-30'::date))
12. 342.208 35,802.376 ↓ 2.3 1,561,776 1

Materialize (cost=0.43..258,461.95 rows=687,181 width=24) (actual time=0.051..35,802.376 rows=1,561,776 loops=1)

13. 1,851.557 35,460.168 ↓ 2.3 1,561,776 1

GroupAggregate (cost=0.43..249,872.18 rows=687,181 width=24) (actual time=0.047..35,460.168 rows=1,561,776 loops=1)

  • Group Key: order_line_items.order_id
14. 33,608.611 33,608.611 ↑ 1.0 2,306,008 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..33,608.611 rows=2,306,008 loops=1)

15. 0.021 0.221 ↑ 1.0 173 1

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

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

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

Planning time : 5.037 ms
Execution time : 822,969.715 ms