explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TJ5s

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 18,302.128 ↓ 7.5 30 1

Limit (cost=18,905.66..18,905.70 rows=4 width=127) (actual time=18,301.999..18,302.128 rows=30 loops=1)

2. 0.116 18,302.122 ↓ 7.5 30 1

GroupAggregate (cost=18,905.66..18,905.70 rows=4 width=127) (actual time=18,301.998..18,302.122 rows=30 loops=1)

  • Group Key: valid_order_ids.email_received_date, orders.id, valid_order_ids.domain, retailer_policy_details.price_protection_days, (((valid_order_ids.email_received_date)::date + retailer_policy_details.price_protection_days))
  • Filter: (count(COALESCE(order_items.override_url, adhoc_spider_prices.id)) <> count(*))
  • Rows Removed by Filter: 86
3. 17.517 18,302.006 ↓ 65.2 261 1

Sort (cost=18,905.66..18,905.66 rows=4 width=180) (actual time=18,301.986..18,302.006 rows=261 loops=1)

  • Sort Key: valid_order_ids.email_received_date DESC, orders.id, valid_order_ids.domain, retailer_policy_details.price_protection_days, (((valid_order_ids.email_received_date)::date + retailer_policy_details.price_protection_days))
  • Sort Method: quicksort Memory: 1997kB
4. 11,969.242 18,284.489 ↓ 1,702.2 6,809 1

Nested Loop Left Join (cost=1,347.83..18,905.65 rows=4 width=180) (actual time=73.943..18,284.489 rows=6,809 loops=1)

  • Join Filter: ((adhoc_spider_prices.order_item_id)::bpchar = order_items.id)
  • Rows Removed by Join Filter: 52505737
5. 5.066 922.519 ↓ 1,702.2 6,809 1

Nested Loop (cost=1,347.83..18,508.27 rows=4 width=176) (actual time=41.365..922.519 rows=6,809 loops=1)

6. 8.059 664.662 ↓ 3,283.0 3,283 1

Nested Loop (cost=1,347.74..18,507.76 rows=1 width=144) (actual time=41.320..664.662 rows=3,283 loops=1)

7. 7.770 458.951 ↓ 45.8 3,801 1

Nested Loop (cost=1,347.63..18,468.10 rows=83 width=144) (actual time=41.269..458.951 rows=3,801 loops=1)

8. 12.997 280.136 ↓ 45.8 3,801 1

Nested Loop (cost=1,347.55..18,131.31 rows=83 width=60) (actual time=41.242..280.136 rows=3,801 loops=1)

9. 130.204 168.269 ↓ 39.9 9,887 1

Bitmap Heap Scan on valid_order_ids (cost=1,347.52..18,091.76 rows=248 width=56) (actual time=41.222..168.269 rows=9,887 loops=1)

  • Recheck Cond: ((domain)::text = ANY ('{bestbuy.com,kohls.com,target.com,walmart.com,zappos.com,footlocker.com,jcrew.com,bloomingdales.com,macys.com,newegg.com,rei.com,nordstrom.com}'::text[]))
  • Filter: (((email_received_date)::date >= '2019-07-14'::date) AND ((email_received_date)::date <= '2019-08-13'::date))
  • Rows Removed by Filter: 51584
  • Heap Blocks: exact=15360
10. 38.065 38.065 ↓ 1.4 67,953 1

Bitmap Index Scan on idx_valid_order_ids_domain (cost=0.00..1,347.50 rows=49,656 width=0) (actual time=38.065..38.065 rows=67,953 loops=1)

  • Index Cond: ((domain)::text = ANY ('{bestbuy.com,kohls.com,target.com,walmart.com,zappos.com,footlocker.com,jcrew.com,bloomingdales.com,macys.com,newegg.com,rei.com,nordstrom.com}'::text[]))
11. 98.870 98.870 ↓ 0.0 0 9,887

Index Scan using retailer_policy_details_domain_key on retailer_policy_details (cost=0.03..0.16 rows=1 width=18) (actual time=0.010..0.010 rows=0 loops=9,887)

  • Index Cond: ((domain)::text = (valid_order_ids.domain)::text)
  • Filter: (((valid_order_ids.email_received_date)::date + price_protection_days) >= now())
  • Rows Removed by Filter: 1
12. 171.045 171.045 ↑ 1.0 1 3,801

Index Scan using orders_pkey on orders (cost=0.09..4.06 rows=1 width=84) (actual time=0.045..0.045 rows=1 loops=3,801)

  • Index Cond: (id = (valid_order_ids.order_id)::bpchar)
13. 197.652 197.652 ↑ 1.0 1 3,801

Index Scan using emails_pkey on emails (cost=0.11..0.48 rows=1 width=17) (actual time=0.052..0.052 rows=1 loops=3,801)

  • Index Cond: ((id)::text = (orders.email_id)::text)
  • Filter: (((sender)::text !~~* '%J.Crew Factory%'::text) AND ((sender)::text !~~* '%NORDSTROM RACK%'::text) AND ((sender)::text !~~* '%Walmart eBooks%'::text) AND ((sender)::text !~~* '%Walmart Contacts%'::text) AND ((sender)::text !~~* '%Walmart Grocery%'::text) AND ((sender)::text !~~* '%noreply@walmart.ca%'::text))
  • Rows Removed by Filter: 0
14. 252.791 252.791 ↑ 6.0 2 3,283

Index Scan using idx_order_items_order_id on order_items (cost=0.09..0.47 rows=12 width=106) (actual time=0.049..0.077 rows=2 loops=3,283)

  • Index Cond: (order_id = orders.id)
  • Filter: (((product_name)::text !~~* '%gift%card%'::text) AND ((product_name)::text !~~* '%Sling TV - $30 off Sling + STARZ over 2 months%'::text) AND ((product_name)::text !~~* '%Cash Card%'::text) AND ((product_name)::text !~~* '%Audiobook subscription: 1 credit/month%'::text) AND ((product_name)::text !~~* '%1-Hour High-Quality%'::text) AND ((product_name)::text !~~* 'High-Quality 4 x 6 Prints'::text) AND ((product_name)::text !~~* 'High-Quality 5 x 7 Prints'::text))
  • Rows Removed by Filter: 0
15. 5,390.520 5,392.728 ↑ 1.0 7,712 6,809

Materialize (cost=0.00..277.85 rows=7,712 width=74) (actual time=0.000..0.792 rows=7,712 loops=6,809)

16. 2.208 2.208 ↑ 1.0 7,712 1

Seq Scan on adhoc_spider_prices (cost=0.00..270.14 rows=7,712 width=74) (actual time=0.008..2.208 rows=7,712 loops=1)

Planning time : 28.353 ms
Execution time : 18,302.906 ms