explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xT4Y : cp for island

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 9,545.230 ↑ 1.0 1 1

Result (cost=9,233.68..9,233.68 rows=1 width=16) (actual time=9,545.230..9,545.230 rows=1 loops=1)

2.          

CTE most_recent_campaign_products

3. 0.032 0.034 ↓ 0.0 0 1

Hash Join (cost=31.42..36.94 rows=1 width=79) (actual time=0.034..0.034 rows=0 loops=1)

  • Hash Cond: (((campaign_products.seller_id)::text = (cp1.seller_id)::text) AND ((campaign_products.marketplace_id)::text = (cp1.marketplace_id)::text) AND ((campaign_products.sku)::text = (cp1.sku)::text) AND (campaign_products.campaign_id = cp1.campaign_id) AND ((max(campaign_products.added_on)) = cp1.added_on))
4. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=14.96..16.88 rows=640 width=71) (never executed)

  • Group Key: campaign_products.seller_id, campaign_products.marketplace_id, campaign_products.sku, campaign_products.campaign_id, campaign_products.parent_asin
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_products (cost=0.00..13.04 rows=640 width=71) (never executed)

  • Filter: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
6. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=12.28..12.28 rows=760 width=79) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on campaign_products cp1 (cost=0.00..12.28 rows=760 width=79) (actual time=0.001..0.001 rows=0 loops=1)

8.          

CTE products_to_add

9. 258.044 6,675.003 ↓ 11.9 220,969 1

HashAggregate (cost=1,209.50..1,265.40 rows=18,633 width=74) (actual time=6,581.653..6,675.003 rows=220,969 loops=1)

  • Group Key: m.seller_id, p.marketplace_id, pa.sku, pa.campaign_id
10. 75.059 6,416.959 ↓ 12.4 231,670 1

Hash Left Join (cost=0.18..1,172.24 rows=18,633 width=74) (actual time=1.813..6,416.959 rows=231,670 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)))
11. 28.070 6,341.899 ↓ 12.3 231,670 1

Nested Loop (cost=0.17..1,115.76 rows=18,821 width=74) (actual time=1.804..6,341.899 rows=231,670 loops=1)

12. 0.029 0.094 ↑ 1.0 1 1

Nested Loop (cost=0.06..14.71 rows=1 width=54) (actual time=0.031..0.094 rows=1 loops=1)

  • Join Filter: (p.merchant_id = m.id)
  • Rows Removed by Join Filter: 65
13. 0.065 0.065 ↑ 1.0 66 1

Index Scan using profiles_pkey on profiles p (cost=0.03..10.41 rows=66 width=30) (actual time=0.007..0.065 rows=66 loops=1)

  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 27
14. 0.000 0.000 ↑ 1.0 1 66

Materialize (cost=0.03..4.04 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=66)

15. 0.005 0.005 ↑ 1.0 1 1

Index Scan using seller_id_unique on merchants m (cost=0.03..4.03 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A3TUJE1XKIZPOF'::text)
16. 6,313.735 6,313.735 ↓ 8.2 231,670 1

Index Only Scan using product_ads_profile_sku_campaigns_idx on product_ads pa (cost=0.11..1,016.35 rows=28,232 width=36) (actual time=1.770..6,313.735 rows=231,670 loops=1)

  • Index Cond: (profile_id = p.id)
  • Heap Fetches: 231670
17. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=0.01..0.01 rows=1 width=120) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on most_recent_campaign_products cp (cost=0.00..0.01 rows=1 width=120) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
19.          

CTE products_to_remove

20. 0.000 3.328 ↓ 0.0 0 1

Nested Loop Anti Join (cost=4.15..11.53 rows=1 width=112) (actual time=3.328..3.328 rows=0 loops=1)

21. 0.002 3.328 ↓ 0.0 0 1

Nested Loop (cost=4.04..7.41 rows=1 width=120) (actual time=3.328..3.328 rows=0 loops=1)

22. 0.015 3.292 ↑ 1.0 1 1

Hash Join (cost=4.04..7.40 rows=1 width=54) (actual time=1.236..3.292 rows=1 loops=1)

  • Hash Cond: (p_1.merchant_id = m_1.id)
23. 2.063 2.063 ↑ 1.0 66 1

Seq Scan on profiles p_1 (cost=0.00..3.33 rows=66 width=30) (actual time=0.010..2.063 rows=66 loops=1)

  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
  • Rows Removed by Filter: 27
24. 0.004 1.214 ↑ 1.0 1 1

Hash (cost=4.03..4.03 rows=1 width=36) (actual time=1.214..1.214 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 1.210 1.210 ↑ 1.0 1 1

Index Scan using seller_id_unique on merchants m_1 (cost=0.03..4.03 rows=1 width=36) (actual time=1.209..1.210 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A3TUJE1XKIZPOF'::text)
26. 0.034 0.034 ↓ 0.0 0 1

CTE Scan on most_recent_campaign_products cp_1 (cost=0.00..0.01 rows=1 width=112) (actual time=0.034..0.034 rows=0 loops=1)

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

Index Only Scan using product_ads_profile_sku_campaigns_idx on product_ads pa_1 (cost=0.11..4.12 rows=1 width=36) (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
28.          

CTE upd

29. 0.001 3.330 ↓ 0.0 0 1

Update on campaign_products cp_2 (cost=0.03..4.05 rows=1 width=221) (actual time=3.330..3.330 rows=0 loops=1)

30. 0.001 3.329 ↓ 0.0 0 1

Nested Loop (cost=0.03..4.05 rows=1 width=221) (actual time=3.329..3.329 rows=0 loops=1)

31. 3.328 3.328 ↓ 0.0 0 1

CTE Scan on products_to_remove rp (cost=0.00..0.01 rows=1 width=248) (actual time=3.328..3.328 rows=0 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using campaign_products_pkey on campaign_products cp_2 (cost=0.03..4.04 rows=1 width=77) (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))
33.          

CTE ins

34. 2,081.816 9,464.431 ↓ 200,712.0 200,712 1

Insert on campaign_products campaign_products_1 (cost=7,774.59..7,915.74 rows=1 width=131) (actual time=7,021.803..9,464.431 rows=200,712 loops=1)

35. 170.413 7,382.615 ↓ 200,712.0 200,712 1

Hash Join (cost=7,774.59..7,915.74 rows=1 width=131) (actual time=7,021.664..7,382.615 rows=200,712 loops=1)

  • Hash Cond: (((ap.seller_id)::text = (p_2.seller_id)::text) AND ((ap.marketplace_id)::text = (p_2.marketplace_id)::text) AND ((ap.sku)::text = (p_2.sku)::text))
36. 6,772.575 6,772.575 ↓ 11.9 220,969 1

CTE Scan on products_to_add ap (cost=0.00..111.80 rows=18,633 width=104) (actual time=6,581.655..6,772.575 rows=220,969 loops=1)

37. 64.118 439.627 ↓ 1.0 156,639 1

Hash (cost=7,069.84..7,069.84 rows=156,612 width=53) (actual time=439.627..439.627 rows=156,639 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 15215kB
38. 375.509 375.509 ↓ 1.0 156,639 1

Seq Scan on products p_2 (cost=0.00..7,069.84 rows=156,612 width=53) (actual time=0.696..375.509 rows=156,639 loops=1)

39.          

Initplan (forResult)

40. 0.003 3.334 ↑ 1.0 1 1

Aggregate (cost=0.01..0.01 rows=1 width=8) (actual time=3.334..3.334 rows=1 loops=1)

41. 3.331 3.331 ↓ 0.0 0 1

CTE Scan on upd (cost=0.00..0.01 rows=1 width=0) (actual time=3.331..3.331 rows=0 loops=1)

42. 24.177 9,541.889 ↑ 1.0 1 1

Aggregate (cost=0.01..0.01 rows=1 width=8) (actual time=9,541.889..9,541.889 rows=1 loops=1)

43. 9,517.712 9,517.712 ↓ 200,712.0 200,712 1

CTE Scan on ins (cost=0.00..0.01 rows=1 width=0) (actual time=7,021.805..9,517.712 rows=200,712 loops=1)

Planning time : 18.650 ms
Execution time : 9,558.610 ms