explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CnTC

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 44,087.732 ↑ 1.0 500 1

Limit (cost=18,783.65..18,948.65 rows=500 width=323) (actual time=44,079.889..44,087.732 rows=500 loops=1)

2. 7.439 44,087.663 ↑ 150.1 500 1

GroupAggregate (cost=18,783.65..42,801.57 rows=75,056 width=323) (actual time=44,079.889..44,087.663 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. 173.319 44,080.224 ↑ 42.3 1,773 1

Sort (cost=18,783.65..18,971.29 rows=75,056 width=291) (actual time=44,079.051..44,080.224 rows=1,773 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: 11,008kB
4. 9.198 43,906.905 ↑ 1.4 55,186 1

Merge Anti Join (cost=4,205.03..6,841.73 rows=75,056 width=291) (actual time=43,873.392..43,906.905 rows=55,186 loops=1)

  • Merge Cond: (p.id = dp.product_id)
  • Join Filter: (v.shop_id = dp.shop_id)
5. 13.883 43,897.686 ↑ 2.7 55,186 1

Merge Join (cost=4,144.94..6,405.81 rows=150,113 width=307) (actual time=43,873.370..43,897.686 rows=55,186 loops=1)

  • Merge Cond: (p.id = v.shopify_product_id)
6. 1.185 2.344 ↓ 1.0 1,905 1

Sort (cost=675.38..679.96 rows=1,834 width=204) (actual time=2.071..2.344 rows=1,905 loops=1)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 467kB
7. 0.130 1.159 ↓ 1.0 1,905 1

Append (cost=0.42..575.97 rows=1,834 width=204) (actual time=0.014..1.159 rows=1,905 loops=1)

8. 1.029 1.029 ↓ 1.0 1,905 1

Index Scan using shopify_products_2500_shop_id_idx on shopify_products_2500 p (cost=0.42..566.80 rows=1,834 width=204) (actual time=0.014..1.029 rows=1,905 loops=1)

  • Index Cond: (shop_id = 2,261)
9. 55.731 43,881.459 ↓ 3.4 55,186 1

Sort (cost=3,469.57..3,510.49 rows=16,370 width=119) (actual time=43,871.291..43,881.459 rows=55,186 loops=1)

  • Sort Key: v.shopify_product_id
  • Sort Method: external sort Disk: 6,480kB
10. 12.861 43,825.728 ↓ 3.4 55,186 1

Hash Left Join (cost=31.02..2,323.77 rows=16,370 width=119) (actual time=43,802.685..43,825.728 rows=55,186 loops=1)

  • Hash Cond: ((v.shop_id = o.shopid) AND (v.id = o.variant_id))
11. 1.331 10.220 ↓ 1.1 18,036 1

Append (cost=0.42..2,169.58 rows=16,370 width=103) (actual time=0.019..10.220 rows=18,036 loops=1)

12. 8.889 8.889 ↓ 1.1 18,036 1

Index Scan using shopify_product_variants_2500_shop_id_idx on shopify_product_variants_2500 v (cost=0.42..2,087.73 rows=16,370 width=103) (actual time=0.018..8.889 rows=18,036 loops=1)

  • Index Cond: (shop_id = 2,261)
  • Filter: (active IS TRUE)
  • Rows Removed by Filter: 61
13. 25.357 43,802.647 ↓ 43,927.0 43,927 1

Hash (cost=30.57..30.57 rows=1 width=32) (actual time=43,802.647..43,802.647 rows=43,927 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,968kB
14. 19,368.093 43,777.290 ↓ 47,648.0 47,648 1

Nested Loop Left Join (cost=0.50..30.57 rows=1 width=32) (actual time=613.115..43,777.290 rows=47,648 loops=1)

  • Join Filter: ((o.shopid = lm.shopid) AND (lm.id = o.line_item_id))
  • Rows Removed by Join Filter: 322,570,190
15. 346.957 346.957 ↓ 47,648.0 47,648 1

Function Scan on rp_shopify_order_line_items o (cost=0.25..17.75 rows=1 width=24) (actual time=325.630..346.957 rows=47,648 loops=1)

  • Filter: ((created_at >= '2020-08-16 04:00:00'::timestamp without time zone) AND (created_at <= '2020-09-15 03:59:59'::timestamp without time zone) AND (shopid = 2,261))
16. 24,062.240 24,062.240 ↓ 1,354.0 6,770 47,648

Function Scan on rp_min_line_items lm (cost=0.25..12.75 rows=5 width=16) (actual time=0.006..0.505 rows=6,770 loops=47,648)

  • Filter: (shopid = 2,261)
17. 0.008 0.021 ↑ 37.0 2 1

Sort (cost=60.09..60.27 rows=74 width=16) (actual time=0.020..0.021 rows=2 loops=1)

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

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

  • Index Cond: (shop_id = 2,261)
Planning time : 5.595 ms
Execution time : 44,092.403 ms