explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m95

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

Nested Loop (cost=21,420.15..89,738.55 rows=1 width=1,627) (actual rows= loops=)

  • Join Filter: (psdp.seller_id = s.seller_id)
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21,419.45..89,731.32 rows=1 width=1,666) (actual rows= loops=)

  • Join Filter: ((CASE WHEN (lower((psl.marketplace_nm)::text) ~~ 'ebay%'::text) THEN psl.product_upc ELSE psl.product_id END)::text = (p.product_asin)::text)
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,419.02..89,722.91 rows=1 width=1,694) (actual rows= loops=)

  • Hash Cond: ((psdp.marketplace_id = m.marketplace_id) AND (lower((psl.marketplace_nm)::text) = (m.marketplace_nm)::text))
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,411.67..89,713.98 rows=294 width=1,690) (actual rows= loops=)

  • Hash Cond: (lower((psl.client_nm)::text) = (c.client_nm)::text)
5. 0.000 0.000 ↓ 0.0

Seq Scan on product_seller_listing psl (cost=0.00..68,277.18 rows=4,436 width=82) (actual rows= loops=)

  • Filter: ((as_of_timestamp)::date = '2020-09-06'::date)
6. 0.000 0.000 ↓ 0.0

Hash (cost=21,408.06..21,408.06 rows=289 width=1,640) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=145.67..21,408.06 rows=289 width=1,640) (actual rows= loops=)

  • Hash Cond: (psdp.client_id = c.client_id)
8. 0.000 0.000 ↓ 0.0

Append (cost=12.57..21,274.20 rows=289 width=1,627) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on psdp_202009 psdp (cost=12.57..21,274.20 rows=289 width=1,627) (actual rows= loops=)

  • Recheck Cond: (as_of_date = '2020-09-06'::date)
  • Filter: ((as_of_month = 9) AND (date_part('year'::text, (as_of_date)::timestamp without time zone) = '2020'::double precision))
10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on psdp_202009_as_of_date_idx (cost=0.00..12.50 rows=78,739 width=0) (actual rows= loops=)

  • Index Cond: (as_of_date = '2020-09-06'::date)
11. 0.000 0.000 ↓ 0.0

Hash (cost=78.60..78.60 rows=4,360 width=17) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on clients c (cost=0.00..78.60 rows=4,360 width=17) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

Index Scan using products_product_id_key on products p (cost=0.43..8.39 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (product_id = psdp.product_id)
  • Filter: (psdp.marketplace_id = marketplace_id)
16. 0.000 0.000 ↓ 0.0

Index Scan using sellers_external_id_key on sellers s (cost=0.70..7.21 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((external_id)::text = (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)