explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RCej : returning 1 + sum 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 2,153.684 ↑ 1.0 1 1

Result (cost=13,887.75..13,887.76 rows=1 width=16) (actual time=2,153.672..2,153.684 rows=1 loops=1)

  • Output: $14, $15
  • Buffers: shared hit=67959 read=215 dirtied=215
2.          

CTE products_to_add

3. 180.388 869.927 ↓ 12,765.0 12,765 1

Unique (cost=8,196.65..8,196.66 rows=1 width=87) (actual time=594.943..869.927 rows=12,765 loops=1)

  • Output: m.seller_id, p.marketplace_id, pa.sku, pa.campaign_id
  • Buffers: shared hit=283
4. 209.825 689.539 ↓ 14,283.0 14,283 1

Sort (cost=8,196.65..8,196.65 rows=1 width=87) (actual time=594.927..689.539 rows=14,283 loops=1)

  • Output: m.seller_id, p.marketplace_id, pa.sku, pa.campaign_id
  • Sort Key: pa.sku, pa.campaign_id
  • Sort Method: quicksort Memory: 1701kB
  • Buffers: shared hit=283
5. 194.091 479.714 ↓ 14,283.0 14,283 1

Hash Left Join (cost=2,341.88..8,196.64 rows=1 width=87) (actual time=1.589..479.714 rows=14,283 loops=1)

  • Output: m.seller_id, p.marketplace_id, pa.sku, pa.campaign_id
  • Hash Cond: (((m.seller_id)::text = (cp.seller_id)::text) AND ((p.marketplace_id)::text = (cp.marketplace_id)::text) AND (pa.campaign_id = cp.campaign_id) AND ((pa.sku)::text = (cp.sku)::text))
  • Filter: (((cp.added_on IS NULL) AND (cp.removed_on IS NULL)) OR ((cp.added_on IS NOT NULL) AND (cp.removed_on IS NOT NULL)))
  • Buffers: shared hit=283
6. 189.146 285.221 ↓ 10.1 14,283 1

Nested Loop (cost=2,308.78..8,120.96 rows=1,419 width=87) (actual time=1.156..285.221 rows=14,283 loops=1)

  • Output: p.marketplace_id, m.seller_id, pa.sku, pa.campaign_id
  • Buffers: shared hit=283
7. 0.037 0.109 ↑ 1.0 1 1

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

  • Output: p.marketplace_id, p.id, m.seller_id
  • Buffers: shared hit=4
8. 0.037 0.037 ↑ 1.0 1 1

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

  • Output: m.id, m.seller_id, m.vendor_id, m.account_id
  • Index Cond: ((m.seller_id)::text = 'A1LMQBEOGTEVAC'::text)
  • Buffers: shared hit=2
9. 0.035 0.035 ↑ 1.0 1 1

Index Scan using profiles_merchant_id_index on public.profiles p (cost=0.15..8.17 rows=1 width=48) (actual time=0.020..0.035 rows=1 loops=1)

  • Output: p.marketplace_id, p.merchant_id, p.id
  • Index Cond: (p.merchant_id = m.id)
  • Filter: ((p.marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2
10. 95.298 95.966 ↑ 3.8 14,283 1

Bitmap Heap Scan on public.product_ads pa (cost=2,308.48..7,557.44 rows=54,717 width=31) (actual time=0.697..95.966 rows=14,283 loops=1)

  • Output: pa.id, pa.profile_id, pa.campaign_id, pa.ad_group_id, pa.sku, pa.asin, pa.state
  • Recheck Cond: (pa.profile_id = p.id)
  • Heap Blocks: exact=162
  • Buffers: shared hit=279
11. 0.668 0.668 ↑ 3.8 14,301 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..2,294.80 rows=54,717 width=0) (actual time=0.662..0.668 rows=14,301 loops=1)

  • Index Cond: (pa.profile_id = p.id)
  • Buffers: shared hit=117
12. 0.016 0.402 ↓ 0.0 0 1

Hash (cost=19.90..19.90 rows=660 width=63) (actual time=0.396..0.402 rows=0 loops=1)

  • Output: cp.seller_id, cp.marketplace_id, cp.campaign_id, cp.sku, cp.added_on, cp.removed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 0.386 0.386 ↓ 0.0 0 1

Seq Scan on public.campaign_products cp (cost=0.00..19.90 rows=660 width=63) (actual time=0.380..0.386 rows=0 loops=1)

  • Output: cp.seller_id, cp.marketplace_id, cp.campaign_id, cp.sku, cp.added_on, cp.removed_on
  • Filter: (((cp.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((cp.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
14.          

CTE products_to_remove

15. 0.027 2.426 ↓ 0.0 0 1

Nested Loop (cost=0.57..5,629.95 rows=643 width=55) (actual time=2.419..2.426 rows=0 loops=1)

  • Output: cp_1.seller_id, cp_1.marketplace_id, cp_1.sku, cp_1.campaign_id, cp_1.added_on
  • Join Filter: (p_1.merchant_id = m_1.id)
  • Buffers: shared hit=3
16. 0.038 0.038 ↑ 1.0 1 1

Index Scan using seller_id_unique on public.merchants m_1 (cost=0.15..8.17 rows=1 width=36) (actual time=0.023..0.038 rows=1 loops=1)

  • Output: m_1.id, m_1.seller_id, m_1.vendor_id, m_1.account_id
  • Index Cond: ((m_1.seller_id)::text = 'A1LMQBEOGTEVAC'::text)
  • Buffers: shared hit=2
17. 0.015 2.361 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..5,613.74 rows=643 width=63) (actual time=2.355..2.361 rows=0 loops=1)

  • Output: p_1.merchant_id, cp_1.seller_id, cp_1.marketplace_id, cp_1.sku, cp_1.campaign_id, cp_1.added_on
  • Buffers: shared hit=1
18. 1.554 2.346 ↓ 0.0 0 1

Nested Loop (cost=0.00..39.88 rows=660 width=71) (actual time=2.340..2.346 rows=0 loops=1)

  • Output: p_1.merchant_id, p_1.id, cp_1.seller_id, cp_1.marketplace_id, cp_1.sku, cp_1.campaign_id, cp_1.added_on
  • Buffers: shared hit=1
19. 0.407 0.407 ↓ 5.0 5 1

Seq Scan on public.profiles p_1 (cost=0.00..13.38 rows=1 width=48) (actual time=0.355..0.407 rows=5 loops=1)

  • Output: p_1.merchant_id, p_1.marketplace_id, p_1.id
  • Filter: ((p_1.marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
20. 0.385 0.385 ↓ 0.0 0 5

Seq Scan on public.campaign_products cp_1 (cost=0.00..19.90 rows=660 width=55) (actual time=0.076..0.077 rows=0 loops=5)

  • Output: cp_1.seller_id, cp_1.marketplace_id, cp_1.sku, cp_1.campaign_id, cp_1.parent_asin, cp_1.added_on, cp_1.removed_on
  • Filter: ((cp_1.removed_on IS NULL) AND ((cp_1.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((cp_1.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_ads_profile_sku_campaigns_idx on public.product_ads pa_1 (cost=0.42..8.44 rows=1 width=31) (never executed)

  • Output: pa_1.profile_id, pa_1.sku, pa_1.campaign_id
  • Index Cond: ((pa_1.profile_id = p_1.id) AND (pa_1.sku = (cp_1.sku)::text) AND (pa_1.campaign_id = cp_1.campaign_id))
  • Heap Fetches: 0
22.          

CTE upd

23. 0.023 2.480 ↓ 0.0 0 1

Update on public.campaign_products cp_2 (cost=31.45..52.75 rows=1 width=237) (actual time=2.467..2.480 rows=0 loops=1)

  • Output: 1
  • Buffers: shared hit=3
24. 0.016 2.457 ↓ 0.0 0 1

Hash Join (cost=31.45..52.75 rows=1 width=237) (actual time=2.451..2.457 rows=0 loops=1)

  • Output: cp_2.seller_id, cp_2.marketplace_id, cp_2.sku, cp_2.campaign_id, cp_2.parent_asin, cp_2.added_on, now(), cp_2.ctid, rp.*
  • Inner Unique: true
  • Hash Cond: (((rp.seller_id)::text = (cp_2.seller_id)::text) AND ((rp.marketplace_id)::text = (cp_2.marketplace_id)::text) AND ((rp.sku)::text = (cp_2.sku)::text) AND (rp.campaign_id = cp_2.campaign_id) AND (rp.added_on = cp_2.added_on))
  • Buffers: shared hit=3
25. 2.441 2.441 ↓ 0.0 0 1

CTE Scan on products_to_remove rp (cost=0.00..12.86 rows=643 width=248) (actual time=2.435..2.441 rows=0 loops=1)

  • Output: rp.*, rp.seller_id, rp.marketplace_id, rp.sku, rp.campaign_id, rp.added_on
  • Buffers: shared hit=3
26. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.60..16.60 rows=660 width=93) (never executed)

  • Output: cp_2.seller_id, cp_2.marketplace_id, cp_2.sku, cp_2.campaign_id, cp_2.parent_asin, cp_2.added_on, cp_2.ctid
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.campaign_products cp_2 (cost=0.00..16.60 rows=660 width=93) (never executed)

  • Output: cp_2.seller_id, cp_2.marketplace_id, cp_2.sku, cp_2.campaign_id, cp_2.parent_asin, cp_2.added_on, cp_2.ctid
28.          

CTE ins

29. 492.568 1,937.246 ↓ 10,461.0 10,461 1

Insert on public.campaign_products (cost=0.28..8.33 rows=1 width=152) (actual time=645.938..1,937.246 rows=10,461 loops=1)

  • Output: 1
  • Buffers: shared hit=67956 read=215 dirtied=215
30. 252.031 1,444.678 ↓ 10,461.0 10,461 1

Nested Loop (cost=0.28..8.33 rows=1 width=152) (actual time=641.066..1,444.678 rows=10,461 loops=1)

  • Output: ap.seller_id, ap.marketplace_id, ap.sku, ap.campaign_id, p_2.parent_asin, now(), NULL::timestamp with time zone
  • Inner Unique: true
  • Buffers: shared hit=36370
31. 1,039.467 1,039.467 ↓ 12,765.0 12,765 1

CTE Scan on products_to_add ap (cost=0.00..0.02 rows=1 width=104) (actual time=594.959..1,039.467 rows=12,765 loops=1)

  • Output: ap.seller_id, ap.marketplace_id, ap.sku, ap.campaign_id
  • Buffers: shared hit=283
32. 153.180 153.180 ↑ 1.0 1 12,765

Index Scan using products_seller_id_marketplace_and_sku_pkey on public.products p_2 (cost=0.28..8.30 rows=1 width=71) (actual time=0.012..0.012 rows=1 loops=12,765)

  • Output: p_2.parent_asin, p_2.seller_id, p_2.marketplace_id, p_2.sku
  • Index Cond: (((p_2.seller_id)::text = (ap.seller_id)::text) AND ((p_2.marketplace_id)::text = (ap.marketplace_id)::text) AND ((p_2.sku)::text = (ap.sku)::text))
  • Buffers: shared hit=36087
33.          

Initplan (forResult)

34. 0.026 2.515 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=2.501..2.515 rows=1 loops=1)

  • Output: sum(1)
  • Buffers: shared hit=3
35. 2.489 2.489 ↓ 0.0 0 1

CTE Scan on upd (cost=0.00..0.02 rows=1 width=0) (actual time=2.483..2.489 rows=0 loops=1)

  • Output: upd."?column?
  • Buffers: shared hit=3
36. 70.039 2,151.128 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=2,151.115..2,151.128 rows=1 loops=1)

  • Output: sum(1)
  • Buffers: shared hit=67956 read=215 dirtied=215
37. 2,081.089 2,081.089 ↓ 10,461.0 10,461 1

CTE Scan on ins (cost=0.00..0.02 rows=1 width=0) (actual time=645.954..2,081.089 rows=10,461 loops=1)

  • Output: ins."?column?
  • Buffers: shared hit=67956 read=215 dirtied=215