explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4MwR

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 51,996.419 ↑ 1.0 500 1

Limit (cost=19,162.32..19,327.32 rows=500 width=323) (actual time=51,987.804..51,996.419 rows=500 loops=1)

2. 7.980 51,996.366 ↑ 168.3 500 1

GroupAggregate (cost=19,162.32..46,092.88 rows=84,158 width=323) (actual time=51,987.803..51,996.366 rows=500 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. 166.141 51,988.386 ↑ 44.1 1,910 1

Sort (cost=19,162.32..19,372.71 rows=84,158 width=291) (actual time=51,987.173..51,988.386 rows=1,910 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: 12,240kB
4. 9.784 51,822.245 ↑ 1.4 61,351 1

Merge Anti Join (cost=2,746.41..5,701.85 rows=84,158 width=291) (actual time=51,786.978..51,822.245 rows=61,351 loops=1)

  • Merge Cond: (p.id = dp.product_id)
  • Join Filter: (v.shop_id = dp.shop_id)
5. 14.787 51,812.441 ↑ 2.7 61,351 1

Merge Join (cost=2,688.72..5,222.80 rows=168,315 width=307) (actual time=51,786.956..51,812.441 rows=61,351 loops=1)

  • Merge Cond: (p.id = v.shopify_product_id)
6. 1.288 2.592 ↓ 1.0 1,903 1

Sort (cost=288.78..293.46 rows=1,871 width=203) (actual time=2.350..2.592 rows=1,903 loops=1)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 467kB
7. 0.220 1.304 ↓ 1.0 1,903 1

Append (cost=0.42..187.09 rows=1,871 width=203) (actual time=0.015..1.304 rows=1,903 loops=1)

8. 1.084 1.084 ↓ 1.0 1,903 1

Index Scan using shopify_products_2500_shop_id_idx on shopify_products_2500 p (cost=0.42..177.74 rows=1,871 width=203) (actual time=0.014..1.084 rows=1,903 loops=1)

  • Index Cond: (shop_id = 2,261)
9. 52.424 51,795.062 ↓ 3.4 61,351 1

Sort (cost=2,399.94..2,444.92 rows=17,992 width=120) (actual time=51,784.599..51,795.062 rows=61,351 loops=1)

  • Sort Key: v.shopify_product_id
  • Sort Method: external sort Disk: 7,224kB
10. 12.965 51,742.638 ↓ 3.4 61,351 1

Hash Left Join (cost=31.02..1,128.35 rows=17,992 width=120) (actual time=51,721.552..51,742.638 rows=61,351 loops=1)

  • Hash Cond: ((v.shop_id = o.shopid) AND (v.id = o.variant_id))
11. 1.217 8.146 ↓ 1.0 18,029 1

Append (cost=0.42..961.92 rows=17,992 width=104) (actual time=0.015..8.146 rows=18,029 loops=1)

12. 6.929 6.929 ↓ 1.0 18,029 1

Index Scan using shopify_product_variants_2500_shop_id_idx on shopify_product_variants_2500 v (cost=0.42..871.96 rows=17,992 width=104) (actual time=0.015..6.929 rows=18,029 loops=1)

  • Index Cond: (shop_id = 2,261)
  • Filter: (active IS TRUE)
  • Rows Removed by Filter: 61
13. 29.863 51,721.527 ↓ 50,327.0 50,327 1

Hash (cost=30.57..30.57 rows=1 width=32) (actual time=51,721.527..51,721.527 rows=50,327 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,319kB
14. 22,658.054 51,691.664 ↓ 55,141.0 55,141 1

Nested Loop Left Join (cost=0.50..30.57 rows=1 width=32) (actual time=621.257..51,691.664 rows=55,141 loops=1)

  • Join Filter: ((o.shopid = lm.shopid) AND (lm.id = o.line_item_id))
  • Rows Removed by Join Filter: 385,483,740
15. 360.290 360.290 ↓ 55,141.0 55,141 1

Function Scan on rp_shopify_order_line_items o (cost=0.25..17.75 rows=1 width=24) (actual time=334.254..360.290 rows=55,141 loops=1)

  • Filter: ((created_at >= '2020-08-11 04:00:00'::timestamp without time zone) AND (created_at <= '2020-09-10 03:59:59'::timestamp without time zone) AND (shopid = 2,261))
16. 28,673.320 28,673.320 ↓ 1,398.2 6,991 55,141

Function Scan on rp_min_line_items lm (cost=0.25..12.75 rows=5 width=16) (actual time=0.005..0.520 rows=6,991 loops=55,141)

  • Filter: (shopid = 2,261)
17. 0.007 0.020 ↑ 35.5 2 1

Sort (cost=57.70..57.88 rows=71 width=16) (actual time=0.019..0.020 rows=2 loops=1)

  • Sort Key: dp.product_id
  • Sort Method: quicksort Memory: 25kB
18. 0.013 0.013 ↑ 35.5 2 1

Index Scan using index_deleted_products_on_shop_id on deleted_products dp (cost=0.42..55.52 rows=71 width=16) (actual time=0.011..0.013 rows=2 loops=1)

  • Index Cond: (shop_id = 2,261)
Planning time : 5.506 ms
Execution time : 52,001.233 ms