explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1rVx

Settings
# exclusive inclusive rows x rows loops node
1. 2.456 177,353.366 ↓ 1.2 18,029 1

Subquery Scan on t (cost=51,300.65..51,925.28 rows=14,697 width=480) (actual time=177,295.943..177,353.366 rows=18,029 loops=1)

2. 24.510 177,350.910 ↓ 1.2 18,029 1

Group (cost=51,300.65..51,778.31 rows=14,697 width=778) (actual time=177,295.941..177,350.910 rows=18,029 loops=1)

  • Group Key: p.title, v.title, v.sku, p.product_type, p.handle, p.metafields_global_description_tag, v.option1, v.option2, v.option3, p.vendor, p.tags, v.barcode
3. 238.702 177,326.400 ↓ 4.1 60,848 1

Sort (cost=51,300.65..51,337.40 rows=14,697 width=298) (actual time=177,295.935..177,326.400 rows=60,848 loops=1)

  • Sort Key: p.title, v.title, v.sku, p.product_type, p.handle, p.metafields_global_description_tag, v.option1, v.option2, v.option3, p.vendor, p.tags, v.barcode
  • Sort Method: external merge Disk: 10,056kB
4. 23.143 177,087.698 ↓ 4.1 60,848 1

Hash Anti Join (cost=55.28..49,105.38 rows=14,697 width=298) (actual time=62,798.176..177,087.698 rows=60,848 loops=1)

  • Hash Cond: ((v.shop_id = dp.shop_id) AND (p.id = dp.product_id))
5. 44.209 177,063.179 ↓ 4.1 60,848 1

Nested Loop (cost=1.49..48,827.47 rows=14,697 width=314) (actual time=62,796.790..177,063.179 rows=60,848 loops=1)

6. 68,752.628 176,532.186 ↓ 4.1 60,848 1

Nested Loop Left Join (cost=1.06..18,481.56 rows=14,697 width=71) (actual time=62,796.773..176,532.186 rows=60,848 loops=1)

  • Join Filter: ((v.shop_id = o.shopid) AND (o.variant_id = v.id))
  • Rows Removed by Join Filter: 983,900,926
7. 759.414 759.414 ↓ 1.2 18,029 1

Index Scan using index_shopify_product_variants_on_shop_id on shopify_product_variants v (cost=0.56..18,193.78 rows=14,697 width=79) (actual time=0.048..759.414 rows=18,029 loops=1)

  • Index Cond: (shop_id = 2,261)
  • Filter: (active IS TRUE)
  • Rows Removed by Filter: 61
8. 44,244.140 107,020.144 ↓ 54,576.0 54,576 18,029

Materialize (cost=0.50..30.58 rows=1 width=16) (actual time=0.765..5.936 rows=54,576 loops=18,029)

9. 21,734.724 62,776.004 ↓ 54,576.0 54,576 1

Nested Loop Left Join (cost=0.50..30.57 rows=1 width=16) (actual time=13,790.077..62,776.004 rows=54,576 loops=1)

  • Join Filter: ((o.shopid = lm.shopid) AND (lm.id = o.line_item_id))
  • Rows Removed by Join Filter: 380,551,475
10. 13,534.976 13,534.976 ↓ 54,576.0 54,576 1

Function Scan on rp_shopify_order_line_items o (cost=0.25..17.75 rows=1 width=24) (actual time=13,516.290..13,534.976 rows=54,576 loops=1)

  • Filter: ((created_at >= '2020-08-10 04:00:00'::timestamp without time zone) AND (created_at <= '2020-09-09 03:59:59'::timestamp without time zone) AND (shopid = 2,261))
11. 27,506.304 27,506.304 ↓ 1,394.6 6,973 54,576

Function Scan on rp_min_line_items lm (cost=0.25..12.75 rows=5 width=16) (actual time=0.005..0.504 rows=6,973 loops=54,576)

  • Filter: (shopid = 2,261)
12. 486.784 486.784 ↑ 1.0 1 60,848

Index Scan using shopify_products_pkey on shopify_products p (cost=0.43..2.06 rows=1 width=251) (actual time=0.008..0.008 rows=1 loops=60,848)

  • Index Cond: (id = v.shopify_product_id)
13. 0.003 1.376 ↑ 35.5 2 1

Hash (cost=52.72..52.72 rows=71 width=16) (actual time=1.376..1.376 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 1.373 1.373 ↑ 35.5 2 1

Index Scan using index_deleted_products_on_shop_id on deleted_products dp (cost=0.42..52.72 rows=71 width=16) (actual time=0.973..1.373 rows=2 loops=1)

  • Index Cond: (shop_id = 2,261)
Planning time : 1.589 ms
Execution time : 177,357.438 ms