explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EGNI

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Delete on product_seller_daily_price pdp (cost=8.54..92,231.84 rows=1 width=46) (actual rows= loops=)

  • Delete on psdp_202008 pdp_1
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.54..92,231.84 rows=1 width=46) (actual rows= loops=)

  • Join Filter: ((psdp.client_id = c.client_id) AND (lower((psl.client_nm)::text) = (c.client_nm)::text))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.54..92,074.55 rows=1 width=59) (actual rows= loops=)

  • Join Filter: ((pdp_1.client_id = psdp.client_id) AND (pdp_1.product_seller_daily_price_id = psdp.product_seller_daily_price_id))
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.11..92,069.18 rows=1 width=93) (actual rows= loops=)

  • Join Filter: ((CASE WHEN ((upper((psl.seller_nm)::text) = upper((psl.marketplace_nm)::text)) AND (upper((psl.marketplace_nm)::text) ~~ 'AMAZON%'::text)) THEN stg.amazon_sellerid(psl.seller_nm) WHEN ((length(btrim(regexp_replace((psl.seller_id)::text, '[+|?;]+'::text, ''::text, 'g'::text))) = 0) OR (psl.seller_id IS NULL) OR ((psl.seller_id)::text = 'Missing'::text)) THEN psl.seller_nm ELSE psl.seller_id END)::text = (s.external_id)::text)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.68..92,060.52 rows=1 width=121) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.25..91,841.78 rows=28 width=87) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.82..64,651.56 rows=3,223 width=97) (actual rows= loops=)

  • Hash Cond: (lower((psl.marketplace_nm)::text) = (m.marketplace_nm)::text)
8. 0.000 0.000 ↓ 0.0

Seq Scan on product_seller_listing psl (cost=0.00..64,635.69 rows=3,223 width=87) (actual rows= loops=)

  • Filter: ((as_of_timestamp)::date = '2020-08-07'::date)
9. 0.000 0.000 ↓ 0.0

Hash (cost=4.14..4.14 rows=214 width=23) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on marketplace m (cost=0.00..4.14 rows=214 width=23) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using products_pkey on products p (cost=0.43..8.44 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (((product_asin)::text = (CASE WHEN (lower((psl.marketplace_nm)::text) ~~ 'ebay%'::text) THEN psl.product_upc ELSE psl.product_id END)::text) AND (marketplace_id = m.marketplace_id))
12. 0.000 0.000 ↓ 0.0

Index Scan using psdp_202008_as_of_date_product_id_seller_id_idx on psdp_202008 pdp_1 (cost=0.43..7.80 rows=1 width=34) (actual rows= loops=)

  • Index Cond: ((as_of_date = '2020-08-07'::date) AND (product_id = p.product_id))
  • Filter: ((as_of_month = 8) AND (p.marketplace_id = marketplace_id) AND (date_part('year'::text, (as_of_date)::timestamp without time zone) = '2020'::double precision))
13. 0.000 0.000 ↓ 0.0

Index Scan using sellers_seller_id_key on sellers s (cost=0.43..8.37 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (seller_id = pdp_1.seller_id)
14. 0.000 0.000 ↓ 0.0

Append (cost=0.43..5.35 rows=1 width=38) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using psdp_202008_as_of_date_product_id_seller_id_idx on psdp_202008 psdp (cost=0.43..5.35 rows=1 width=38) (actual rows= loops=)

  • Index Cond: ((as_of_date = '2020-08-07'::date) AND (product_id = p.product_id) AND (seller_id = s.seller_id))
  • Filter: ((as_of_month = 8) AND (p.marketplace_id = marketplace_id) AND (date_part('year'::text, (as_of_date)::timestamp without time zone) = '2020'::double precision))
16. 0.000 0.000 ↓ 0.0

Seq Scan on clients c (cost=0.00..79.47 rows=4,447 width=23) (actual rows= loops=)