explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X2Sb : cp_returning_1

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

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

2.          

CTE products_to_add

3. 179.143 867.560 ↓ 12,765.0 12,765 1

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

4. 207.641 688.417 ↓ 14,283.0 14,283 1

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

  • Sort Key: pa.sku, pa.campaign_id
  • Sort Method: quicksort Memory: 1701kB
5. 194.996 480.776 ↓ 14,283.0 14,283 1

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

  • 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)))
6. 188.516 285.364 ↓ 10.1 14,283 1

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

7. 0.037 0.101 ↑ 1.0 1 1

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

8. 0.029 0.029 ↑ 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.029 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
9. 0.035 0.035 ↑ 1.0 1 1

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

  • Index Cond: (merchant_id = m.id)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 1
10. 96.120 96.747 ↑ 3.8 14,283 1

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

  • Recheck Cond: (profile_id = p.id)
  • Heap Blocks: exact=162
11. 0.627 0.627 ↑ 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.621..0.627 rows=14,301 loops=1)

  • Index Cond: (profile_id = p.id)
12. 0.016 0.416 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 0.400 0.400 ↓ 0.0 0 1

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

  • Filter: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
14.          

CTE products_to_remove

15. 0.024 2.602 ↓ 0.0 0 1

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

  • Join Filter: (p_1.merchant_id = m_1.id)
16. 0.038 0.038 ↑ 1.0 1 1

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

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
17. 0.014 2.540 ↓ 0.0 0 1

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

18. 1.620 2.526 ↓ 0.0 0 1

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

19. 0.501 0.501 ↓ 5.0 5 1

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

  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 2
20. 0.405 0.405 ↓ 0.0 0 5

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

  • Filter: ((removed_on IS NULL) AND ((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
21. 0.000 0.000 ↓ 0.0 0

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

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

CTE upd

23. 0.021 2.653 ↓ 0.0 0 1

Update on campaign_products cp_2 (cost=31.45..52.75 rows=1 width=237) (actual time=2.641..2.653 rows=0 loops=1)

24. 0.015 2.632 ↓ 0.0 0 1

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

  • 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))
25. 2.617 2.617 ↓ 0.0 0 1

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

26. 0.000 0.000 ↓ 0.0 0

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

27. 0.000 0.000 ↓ 0.0 0

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

28.          

CTE ins

29. 469.279 1,908.537 ↓ 10,461.0 10,461 1

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

30. 249.730 1,439.258 ↓ 10,461.0 10,461 1

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

31. 1,036.348 1,036.348 ↓ 12,765.0 12,765 1

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

32. 153.180 153.180 ↑ 1.0 1 12,765

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

  • Index Cond: (((seller_id)::text = (ap.seller_id)::text) AND ((marketplace_id)::text = (ap.marketplace_id)::text) AND ((sku)::text = (ap.sku)::text))
33.          

Initplan (forResult)

34. 0.024 2.687 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=2.673..2.687 rows=1 loops=1)

35. 2.663 2.663 ↓ 0.0 0 1

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

36. 69.602 2,121.299 ↑ 1.0 1 1

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

37. 2,051.697 2,051.697 ↓ 10,461.0 10,461 1

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

Planning time : 15.189 ms
Execution time : 2,125.819 ms