explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KaYN : insert CP

Settings
# exclusive inclusive rows x rows loops node
1. 441.852 1,863.922 ↓ 0.0 0 1

Insert on campaign_products (cost=5,258.73..5,266.78 rows=1 width=152) (actual time=1,863.915..1,863.922 rows=0 loops=1)

2.          

CTE products_to_add

3. 173.568 863.536 ↓ 12,473.0 12,473 1

Unique (cost=5,258.44..5,258.45 rows=1 width=87) (actual time=599.754..863.536 rows=12,473 loops=1)

4. 207.734 689.968 ↓ 13,980.0 13,980 1

Sort (cost=5,258.44..5,258.44 rows=1 width=87) (actual time=599.737..689.968 rows=13,980 loops=1)

  • Sort Key: pa.sku, pa.campaign_id
  • Sort Method: quicksort Memory: 1662kB
5. 194.886 482.234 ↓ 13,980.0 13,980 1

Hash Left Join (cost=629.94..5,258.43 rows=1 width=87) (actual time=1.728..482.234 rows=13,980 loops=1)

  • Hash Cond: (((m.seller_id)::text = (cp.seller_id)::text) AND ((p_1.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. 190.377 286.896 ↓ 1.0 13,980 1

Nested Loop (cost=596.84..4,806.79 rows=13,951 width=87) (actual time=1.230..286.896 rows=13,980 loops=1)

7. 0.035 0.103 ↑ 1.0 1 1

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

  • Join Filter: (p_1.merchant_id = m.id)
8. 0.039 0.039 ↑ 1.0 1 1

Index Scan using profiles_pkey on profiles p_1 (cost=0.15..8.17 rows=1 width=48) (actual time=0.023..0.039 rows=1 loops=1)

  • Index Cond: (id = '3811963422597420'::bigint)
  • Filter: ((marketplace_id)::text = 'ATVPDKIKX0DER'::text)
9. 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.023..0.029 rows=1 loops=1)

  • Index Cond: ((seller_id)::text = 'A1LMQBEOGTEVAC'::text)
10. 95.718 96.416 ↓ 1.0 13,980 1

Bitmap Heap Scan on product_ads pa (cost=596.54..4,650.93 rows=13,951 width=31) (actual time=0.733..96.416 rows=13,980 loops=1)

  • Recheck Cond: (profile_id = '3811963422597420'::bigint)
  • Heap Blocks: exact=157
11. 0.698 0.698 ↓ 1.0 13,980 1

Bitmap Index Scan on product_ads_profile_sku_campaigns_idx (cost=0.00..593.05 rows=13,951 width=0) (actual time=0.692..0.698 rows=13,980 loops=1)

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

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

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

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

  • Filter: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
14. 245.675 1,422.070 ↓ 10,169.0 10,169 1

Nested Loop (cost=0.28..8.33 rows=1 width=152) (actual time=645.821..1,422.070 rows=10,169 loops=1)

15. 1,026.719 1,026.719 ↓ 12,473.0 12,473 1

CTE Scan on products_to_add ap (cost=0.00..0.02 rows=1 width=104) (actual time=599.770..1,026.719 rows=12,473 loops=1)

16. 149.676 149.676 ↑ 1.0 1 12,473

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,473)

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