explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Dec : without-explicit-profile-merchant-join

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 1,958.225 ↑ 1.0 1 1

Result (cost=19,706.40..19,706.41 rows=1 width=16) (actual time=1,958.213..1,958.225 rows=1 loops=1)

2.          

CTE products_to_add

3. 180.802 685.504 ↓ 12,765.0 12,765 1

Unique (cost=5,519.53..5,519.54 rows=1 width=87) (actual time=411.227..685.504 rows=12,765 loops=1)

4. 211.656 504.702 ↓ 14,283.0 14,283 1

Sort (cost=5,519.53..5,519.54 rows=1 width=87) (actual time=411.208..504.702 rows=14,283 loops=1)

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

Hash Left Join (cost=589.73..5,519.52 rows=1 width=87) (actual time=1.006..293.046 rows=14,283 loops=1)

  • Hash Cond: ((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. 95.825 96.511 ↓ 1.1 14,283 1

Bitmap Heap Scan on product_ads pa (cost=563.23..5,294.04 rows=13,265 width=23) (actual time=0.721..96.511 rows=14,283 loops=1)

  • Recheck Cond: (profile_id = '3811963422597420'::bigint)
  • Heap Blocks: exact=162
7. 0.686 0.686 ↓ 1.1 14,301 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..559.91 rows=13,265 width=0) (actual time=0.680..0.686 rows=14,301 loops=1)

  • Index Cond: (profile_id = '3811963422597420'::bigint)
8. 0.018 0.257 ↓ 0.0 0 1

Hash (cost=16.60..16.60 rows=660 width=34) (actual time=0.250..0.257 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
9. 0.239 0.239 ↓ 0.0 0 1

Seq Scan on campaign_products cp (cost=0.00..16.60 rows=660 width=34) (actual time=0.233..0.239 rows=0 loops=1)

10.          

CTE products_to_remove

11. 0.027 0.272 ↓ 0.0 0 1

Hash Right Join (cost=29.80..14,170.24 rows=1 width=55) (actual time=0.266..0.272 rows=0 loops=1)

  • Hash Cond: ((pa_1.campaign_id = cp_1.campaign_id) AND ((pa_1.sku)::text = (cp_1.sku)::text))
  • Filter: (pa_1.profile_id IS NULL)
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on product_ads pa_1 (cost=0.00..8,395.16 rows=383,016 width=31) (never executed)

13. 0.018 0.245 ↓ 0.0 0 1

Hash (cost=19.90..19.90 rows=660 width=55) (actual time=0.238..0.245 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.227 0.227 ↓ 0.0 0 1

Seq Scan on campaign_products cp_1 (cost=0.00..19.90 rows=660 width=55) (actual time=0.221..0.227 rows=0 loops=1)

  • Filter: ((removed_on IS NULL) AND ((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
15.          

CTE upd

16. 0.024 0.328 ↓ 0.0 0 1

Update on campaign_products cp_2 (cost=0.15..8.22 rows=1 width=349) (actual time=0.316..0.328 rows=0 loops=1)

17. 0.016 0.304 ↓ 0.0 0 1

Nested Loop (cost=0.15..8.22 rows=1 width=349) (actual time=0.298..0.304 rows=0 loops=1)

18. 0.288 0.288 ↓ 0.0 0 1

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

19. 0.000 0.000 ↓ 0.0 0

Index Scan using campaign_products_pkey on campaign_products cp_2 (cost=0.15..8.18 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))
20.          

CTE ins

21. 477.371 1,741.206 ↓ 10,461.0 10,461 1

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

22. 254.950 1,263.835 ↓ 10,461.0 10,461 1

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

23. 855.705 855.705 ↓ 12,765.0 12,765 1

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

24. 153.180 153.180 ↑ 1.0 1 12,765

Index Scan using products_seller_id_marketplace_and_sku_pkey on products p (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) AND ((marketplace_id)::text = ap.marketplace_id) AND ((sku)::text = (ap.sku)::text))
25.          

Initplan (forResult)

26. 0.025 0.364 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.350..0.364 rows=1 loops=1)

27. 0.339 0.339 ↓ 0.0 0 1

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

28. 69.645 1,957.818 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=1,957.805..1,957.818 rows=1 loops=1)

29. 1,888.173 1,888.173 ↓ 10,461.0 10,461 1

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