explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0kHU

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 14,077.516 ↑ 1.0 10 1

Limit (cost=1,282,667.01..1,282,667.03 rows=10 width=2,844) (actual time=14,077.513..14,077.516 rows=10 loops=1)

2. 87.590 14,077.514 ↑ 29,909.4 10 1

Sort (cost=1,282,667.01..1,283,414.74 rows=299,094 width=2,844) (actual time=14,077.512..14,077.514 rows=10 loops=1)

  • Sort Key: co.id DESC
  • Sort Method: top-N heapsort Memory: 45kB
3. 548.362 13,989.924 ↓ 1.3 378,244 1

Unique (cost=1,256,014.85..1,273,212.75 rows=299,094 width=2,844) (actual time=13,078.330..13,989.924 rows=378,244 loops=1)

4. 13,003.551 13,441.562 ↓ 1.3 378,244 1

Sort (cost=1,256,014.85..1,256,762.58 rows=299,094 width=2,844) (actual time=13,078.330..13,441.562 rows=378,244 loops=1)

  • Sort Key: co.id, co.created_on, co.version, co.updated_on, co.external_reference, co.return_info, co.num_shipment_splits, co.status, co.type, co.retailer_id, co.user_id, co.location_id, co.fulfilment_choice_id, co.total_price, co.total_tax_price, co.order_id, co.class_type, co.return_type, co.attributes, co.flex_type, co.flex_version, co.fulfilment_plan_id
  • Sort Method: external merge Disk: 664704kB
5. 29.210 438.011 ↓ 1.3 378,244 1

Append (cost=0.00..127,792.39 rows=299,094 width=2,844) (actual time=0.110..438.011 rows=378,244 loops=1)

6. 408.769 408.769 ↓ 1.3 378,244 1

Seq Scan on customer_order co (cost=0.00..124,004.88 rows=299,033 width=2,334) (actual time=0.109..408.769 rows=378,244 loops=1)

  • Filter: ((retailer_id = '1'::bigint) AND ((class_type)::text = 'ORDER'::text) AND (lower((external_reference)::text) ~~ '%300%'::text))
  • Rows Removed by Filter: 105711
7. 0.001 0.025 ↓ 0.0 0 1

Nested Loop (cost=32.81..772.10 rows=60 width=2,334) (actual time=0.025..0.025 rows=0 loops=1)

8. 0.000 0.024 ↓ 0.0 0 1

Bitmap Heap Scan on platform_user u (cost=32.39..218.43 rows=49 width=8) (actual time=0.024..0.024 rows=0 loops=1)

  • Recheck Cond: ((lower((first_name)::text) ~~ '%300%'::text) OR (lower((last_name)::text) ~~ '%300%'::text))
9. 0.001 0.024 ↓ 0.0 0 1

BitmapOr (cost=32.39..32.39 rows=49 width=0) (actual time=0.024..0.024 rows=0 loops=1)

10. 0.015 0.015 ↓ 0.0 0 1

Bitmap Index Scan on platform_user_first_name_trgrm (cost=0.00..16.18 rows=24 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (lower((first_name)::text) ~~ '%300%'::text)
11. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on platform_user_last_name_trgrm (cost=0.00..16.19 rows=25 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (lower((last_name)::text) ~~ '%300%'::text)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_customer_order_user_id on customer_order co_1 (cost=0.42..11.28 rows=2 width=2,334) (never executed)

  • Index Cond: (user_id = u.id)
  • Filter: ((retailer_id = '1'::bigint) AND ((class_type)::text = 'ORDER'::text))
13. 0.000 0.007 ↓ 0.0 0 1

Nested Loop (cost=12.42..24.47 rows=1 width=2,334) (actual time=0.007..0.007 rows=0 loops=1)

14. 0.000 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on fulfilment_choice fc (cost=12.00..16.02 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Recheck Cond: (lower((pickup_location_ref)::text) ~~ '%300%'::text)
15. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on fulfilment_choice_pickup_ref_trgrm (cost=0.00..12.00 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (lower((pickup_location_ref)::text) ~~ '%300%'::text)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using "FK_fiqdwx9kll9lkjx590ue8i3l7" on customer_order co_2 (cost=0.42..8.45 rows=1 width=2,334) (never executed)

  • Index Cond: (fulfilment_choice_id = fc.id)
  • Filter: ((retailer_id = '1'::bigint) AND ((class_type)::text = 'ORDER'::text))
Planning time : 0.943 ms
Execution time : 14,171.976 ms