explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gwnr : cp_returning_star

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

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

2.          

CTE products_to_add

3. 180.347 868.384 ↓ 12,765.0 12,765 1

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

4. 208.613 688.037 ↓ 14,283.0 14,283 1

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

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

Hash Left Join (cost=2,341.88..8,196.64 rows=1 width=87) (actual time=3.767..479.424 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.362 286.138 ↓ 10.1 14,283 1

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

7. 0.039 0.140 ↑ 1.0 1 1

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

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

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
9. 0.063 0.063 ↑ 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.048..0.063 rows=1 loops=1)

  • Index Cond: (merchant_id = m.id)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 1
10. 95.076 97.636 ↑ 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=2.613..97.636 rows=14,283 loops=1)

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

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

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

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

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

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

CTE products_to_remove

15. 0.025 2.534 ↓ 0.0 0 1

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

  • Join Filter: (p_1.merchant_id = m_1.id)
16. 0.051 0.051 ↑ 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.035..0.051 rows=1 loops=1)

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

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

18. 1.622 2.443 ↓ 0.0 0 1

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

19. 0.421 0.421 ↓ 5.0 5 1

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

  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 2
20. 0.400 0.400 ↓ 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.080 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.022 2.587 ↓ 0.0 0 1

Update on campaign_products cp_2 (cost=31.45..52.75 rows=1 width=349) (actual time=2.574..2.587 rows=0 loops=1)

24. 0.010 2.565 ↓ 0.0 0 1

Hash Join (cost=31.45..52.75 rows=1 width=349) (actual time=2.558..2.565 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.555 2.555 ↓ 0.0 0 1

CTE Scan on products_to_remove rp (cost=0.00..12.86 rows=643 width=248) (actual time=2.543..2.555 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. 499.259 1,948.947 ↓ 10,461.0 10,461 1

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

30. 257.920 1,449.688 ↓ 10,461.0 10,461 1

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

31. 1,038.588 1,038.588 ↓ 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.724..1,038.588 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.026 2.622 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=2.608..2.622 rows=1 loops=1)

35. 2.596 2.596 ↓ 0.0 0 1

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

36. 70.019 2,167.277 ↑ 1.0 1 1

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

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

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

Planning time : 35.684 ms
Execution time : 2,172.093 ms