explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ivJy

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,469.634 ↑ 1.0 10 1

Limit (cost=779,520.71..779,520.73 rows=10 width=2,844) (actual time=4,469.627..4,469.634 rows=10 loops=1)

2. 150.435 4,469.628 ↑ 29,290.2 10 1

Sort (cost=779,520.71..780,252.96 rows=292,902 width=2,844) (actual time=4,469.626..4,469.628 rows=10 loops=1)

  • Sort Key: co.id DESC
  • Sort Method: top-N heapsort Memory: 45kB
3. 774.657 4,319.193 ↓ 1.3 378,244 1

Unique (cost=753,420.32..770,262.18 rows=292,902 width=2,335) (actual time=3,212.065..4,319.193 rows=378,244 loops=1)

4. 2,654.996 3,544.536 ↓ 1.3 378,244 1

Sort (cost=753,420.32..754,152.57 rows=292,902 width=2,335) (actual time=3,212.063..3,544.536 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: 664712kB
5. 189.235 889.540 ↓ 1.3 378,244 1

Append (cost=0.00..136,157.73 rows=292,902 width=2,335) (actual time=0.012..889.540 rows=378,244 loops=1)

6. 696.089 696.089 ↓ 1.3 378,244 1

Seq Scan on customer_order co (cost=0.00..123,696.18 rows=292,392 width=2,335) (actual time=0.012..696.089 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 3.078 ↓ 0.0 0 1

Nested Loop (cost=1,544.83..2,320.81 rows=58 width=2,335) (actual time=3.078..3.078 rows=0 loops=1)

8. 0.002 3.077 ↓ 0.0 0 1

Bitmap Heap Scan on platform_user u (cost=1,544.41..1,737.81 rows=51 width=8) (actual time=3.077..3.077 rows=0 loops=1)

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

BitmapOr (cost=1,544.41..1,544.41 rows=51 width=0) (actual time=3.075..3.075 rows=0 loops=1)

10. 1.538 1.538 ↓ 0.0 0 1

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

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

Bitmap Index Scan on platform_user_last_name_trgrm (cost=0.00..780.20 rows=26 width=0) (actual time=1.534..1.534 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.41 rows=2 width=2,335) (never executed)

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

Nested Loop (cost=401.15..7,211.72 rows=452 width=2,335) (actual time=1.138..1.138 rows=0 loops=1)

14. 0.001 1.137 ↓ 0.0 0 1

Bitmap Heap Scan on fulfilment_choice fc (cost=400.72..2,226.62 rows=609 width=8) (actual time=1.137..1.137 rows=0 loops=1)

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

Bitmap Index Scan on fulfilment_choice_pickup_ref_trgrm (cost=0.00..400.57 rows=609 width=0) (actual time=1.136..1.136 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.18 rows=1 width=2,335) (never executed)

  • Index Cond: (fulfilment_choice_id = fc.id)
  • Filter: ((retailer_id = '1'::bigint) AND ((class_type)::text = 'ORDER'::text))