explain.depesz.com

PostgreSQL's explain analyze made readable

Result: plmm : Remove CP

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 930.565 ↓ 0.0 0 1

Update on campaign_products cp (cost=6,710.24..6,718.29 rows=1 width=237) (actual time=930.559..930.565 rows=0 loops=1)

2.          

CTE products_to_remove

3. 167.628 930.519 ↓ 0.0 0 1

Merge Anti Join (cost=5,623.10..6,709.96 rows=1 width=55) (actual time=930.513..930.519 rows=0 loops=1)

  • Merge Cond: (((cp_1.sku)::text = (pa.sku)::text) AND (cp_1.campaign_id = pa.campaign_id))
  • Join Filter: (pa.profile_id = p.id)
4. 258.827 464.656 ↑ 1.0 10,169 1

Nested Loop (cost=0.58..998.57 rows=10,169 width=63) (actual time=0.136..464.656 rows=10,169 loops=1)

5. 73.632 73.632 ↑ 1.0 10,169 1

Index Scan using campaign_products_pkey on campaign_products cp_1 (cost=0.29..855.10 rows=10,169 width=55) (actual time=0.032..73.632 rows=10,169 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Filter: (removed_on IS NULL)
6. 132.109 132.197 ↑ 1.0 1 10,169

Materialize (cost=0.30..16.35 rows=1 width=72) (actual time=0.006..0.013 rows=1 loops=10,169)

7. 0.035 0.088 ↑ 1.0 1 1

Nested Loop (cost=0.30..16.35 rows=1 width=72) (actual time=0.059..0.088 rows=1 loops=1)

  • Join Filter: (p.merchant_id = m.id)
8. 0.032 0.032 ↑ 1.0 1 1

Index Scan using profiles_pkey on profiles p (cost=0.15..8.17 rows=1 width=48) (actual time=0.016..0.032 rows=1 loops=1)

  • Index Cond: (id = '3811963422597420'::bigint)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
9. 0.021 0.021 ↑ 1.0 1 1

Index Scan using seller_id_unique on merchants m (cost=0.15..8.17 rows=1 width=36) (actual time=0.015..0.021 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
10. 201.671 298.235 ↓ 1.0 13,974 1

Sort (cost=5,611.32..5,646.20 rows=13,951 width=31) (actual time=208.330..298.235 rows=13,974 loops=1)

  • Sort Key: pa.sku, pa.campaign_id
  • Sort Method: quicksort Memory: 1477kB
11. 95.906 96.564 ↓ 1.0 13,980 1

Bitmap Heap Scan on product_ads pa (cost=596.54..4,650.93 rows=13,951 width=31) (actual time=0.690..96.564 rows=13,980 loops=1)

  • Recheck Cond: (profile_id = '3811963422597420'::bigint)
  • Heap Blocks: exact=157
12. 0.658 0.658 ↓ 1.0 13,980 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..593.05 rows=13,951 width=0) (actual time=0.652..0.658 rows=13,980 loops=1)

  • Index Cond: (profile_id = '3811963422597420'::bigint)
13. 0.014 930.549 ↓ 0.0 0 1

Nested Loop (cost=0.29..8.34 rows=1 width=237) (actual time=930.543..930.549 rows=0 loops=1)

14. 930.535 930.535 ↓ 0.0 0 1

CTE Scan on products_to_remove rp (cost=0.00..0.02 rows=1 width=248) (actual time=930.529..930.535 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using campaign_products_pkey on campaign_products cp (cost=0.29..8.31 rows=1 width=93) (never executed)

  • Index Cond: (((seller_id)::text = (rp.seller_id)::text) AND ((marketplace_id)::text = (rp.marketplace_id)::text) AND ((sku)::text = (rp.sku)::text) AND (campaign_id = rp.campaign_id) AND (added_on = rp.added_on))
Planning time : 10.595 ms
Execution time : 931.417 ms