explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXpn

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

Hash Right Join (cost=64,273.34..65,328.95 rows=10,633 width=175) (actual rows= loops=)

  • Hash Cond: ((dpc.sku)::text = (p.sku)::text)
  • Join Filter: (dpc.date = date_range.date)
2.          

CTE skus_with_sales_or_advertising

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=169.24..13,205.44 rows=5,292 width=14) (actual rows= loops=)

  • Hash Cond: (p_1.tm_id = pfs.tm_product_id)
4. 0.000 0.000 ↓ 0.0

Seq Scan on products p_1 (cost=0.00..12,653.99 rows=145,599 width=30) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash (cost=103.09..103.09 rows=5,292 width=16) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on product_firsts pfs (cost=0.00..103.09 rows=5,292 width=16) (actual rows= loops=)

  • Filter: ((cost_data_first_set_at IS NOT NULL) OR (ad_data_first_set_at IS NOT NULL))
7.          

CTE earliest

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,703.72..4,703.78 rows=1 width=4) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Aggregate (cost=3,736.39..3,736.40 rows=1 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on daily_product_ad_performances (cost=0.00..3,562.71 rows=69,471 width=4) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Aggregate (cost=967.34..967.35 rows=1 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on daily_product_costs (cost=0.00..896.07 rows=28,507 width=4) (actual rows= loops=)

13.          

CTE dpa

14. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=16,120.04..26,594.14 rows=69,471 width=171) (actual rows= loops=)

  • Group Key: m.seller_id, p_2.marketplace_id, pf.date, pa.sku
15. 0.000 0.000 ↓ 0.0

Gather Merge (cost=16,120.04..23,960.05 rows=57,892 width=171) (actual rows= loops=)

  • Workers Planned: 2
16. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=15,120.01..16,277.85 rows=28,946 width=171) (actual rows= loops=)

  • Group Key: m.seller_id, p_2.marketplace_id, pf.date, pa.sku
17. 0.000 0.000 ↓ 0.0

Sort (cost=15,120.01..15,192.38 rows=28,946 width=101) (actual rows= loops=)

  • Sort Key: m.seller_id, p_2.marketplace_id, pf.date, pa.sku
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,829.74..12,974.96 rows=28,946 width=101) (actual rows= loops=)

  • Hash Cond: (p_2.merchant_id = m.id)
19. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,803.54..12,872.33 rows=28,946 width=77) (actual rows= loops=)

  • Hash Cond: (pa.profile_id = p_2.id)
20. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=3,787.47..12,779.08 rows=28,946 width=45) (actual rows= loops=)

  • Hash Cond: (pa.id = pf.product_ad_id)
21. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on product_ads pa (cost=0.00..6,208.13 rows=160,813 width=31) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=3,276.65..3,276.65 rows=40,865 width=30) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on daily_product_ad_performances pf (cost=0.00..3,276.65 rows=40,865 width=30) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=12.70..12.70 rows=270 width=48) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on profiles p_2 (cost=0.00..12.70 rows=270 width=48) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=17.20..17.20 rows=720 width=36) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on merchants m (cost=0.00..17.20 rows=720 width=36) (actual rows= loops=)

28.          

CTE date_range

29. 0.000 0.000 ↓ 0.0

Function Scan on generate_series (cost=0.03..10.03 rows=1,000 width=8) (actual rows= loops=)

30.          

Initplan (forFunction Scan)

31. 0.000 0.000 ↓ 0.0

Limit (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

CTE Scan on earliest (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on daily_product_costs dpc (cost=0.00..896.07 rows=28,507 width=51) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=19,627.04..19,627.04 rows=10,633 width=150) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Merge Join (cost=19,462.51..19,627.04 rows=10,633 width=150) (actual rows= loops=)

  • Merge Cond: (date_range.date = dpa.date)
36. 0.000 0.000 ↓ 0.0

Sort (cost=69.83..72.33 rows=1,000 width=8) (actual rows= loops=)

  • Sort Key: date_range.date
37. 0.000 0.000 ↓ 0.0

CTE Scan on date_range (cost=0.00..20.00 rows=1,000 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Sort (cost=19,392.68..19,398.00 rows=2,127 width=146) (actual rows= loops=)

  • Sort Key: dpa.date
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=17,603.91..19,275.12 rows=2,127 width=146) (actual rows= loops=)

  • Hash Cond: ((dpa.sku)::text = (p.sku)::text)
40. 0.000 0.000 ↓ 0.0

CTE Scan on dpa (cost=0.00..1,389.42 rows=69,471 width=124) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=17,548.25..17,548.25 rows=4,453 width=86) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=16,108.03..17,548.25 rows=4,453 width=86) (actual rows= loops=)

  • Hash Cond: ((skus_with_sales_or_advertising.sku)::text = (p.sku)::text)
43. 0.000 0.000 ↓ 0.0

CTE Scan on skus_with_sales_or_advertising (cost=0.00..105.84 rows=5,292 width=32) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=13,381.99..13,381.99 rows=122,404 width=54) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on products p (cost=0.00..13,381.99 rows=122,404 width=54) (actual rows= loops=)

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