explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jBFn

Settings
# exclusive inclusive rows x rows loops node
1. 419.349 10,844.568 ↓ 2.5 26,841 1

Hash Right Join (cost=58,874.84..59,930.45 rows=10,633 width=175) (actual time=10,216.728..10,844.568 rows=26,841 loops=1)

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

CTE skus_with_sales_or_advertising

3. 1,015.489 2,133.264 ↓ 1.1 6,012 1

Hash Join (cost=169.24..13,205.44 rows=5,292 width=14) (actual time=81.462..2,133.264 rows=6,012 loops=1)

  • Hash Cond: (p_2.tm_id = pfs.tm_product_id)
4. 1,036.452 1,036.452 ↑ 1.0 145,599 1

Seq Scan on products p_2 (cost=0.00..12,653.99 rows=145,599 width=30) (actual time=0.070..1,036.452 rows=145,599 loops=1)

5. 40.833 81.323 ↓ 1.1 6,012 1

Hash (cost=103.09..103.09 rows=5,292 width=16) (actual time=81.316..81.323 rows=6,012 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 346kB
6. 40.490 40.490 ↓ 1.1 6,012 1

Seq Scan on product_firsts pfs (cost=0.00..103.09 rows=5,292 width=16) (actual time=0.013..40.490 rows=6,012 loops=1)

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

CTE date_range

8. 0.453 0.453 ↑ 16.4 61 1

Function Scan on generate_series (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.029..0.453 rows=61 loops=1)

9. 208.574 208.574 ↓ 1.0 29,044 1

Seq Scan on daily_product_costs dpc (cost=0.00..896.07 rows=28,507 width=51) (actual time=0.021..208.574 rows=29,044 loops=1)

10. 191.991 10,216.645 ↓ 2.5 26,841 1

Hash (cost=45,526.47..45,526.47 rows=10,633 width=150) (actual time=10,216.639..10,216.645 rows=26,841 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3790kB
11. 362.767 10,024.654 ↓ 2.5 26,841 1

Merge Join (cost=45,361.95..45,526.47 rows=10,633 width=150) (actual time=9,480.365..10,024.654 rows=26,841 loops=1)

  • Merge Cond: (date_range.date = pf.date)
12. 0.853 2.180 ↑ 18.5 54 1

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

  • Sort Key: date_range.date
  • Sort Method: quicksort Memory: 27kB
13. 1.327 1.327 ↑ 16.4 61 1

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

14. 387.935 9,659.707 ↓ 12.6 26,841 1

Sort (cost=45,292.12..45,297.44 rows=2,127 width=146) (actual time=9,477.918..9,659.707 rows=26,841 loops=1)

  • Sort Key: pf.date
  • Sort Method: external sort Disk: 2928kB
15. 433.324 9,271.772 ↓ 12.6 26,841 1

Hash Join (cost=33,723.95..45,174.55 rows=2,127 width=146) (actual time=7,658.742..9,271.772 rows=26,841 loops=1)

  • Hash Cond: ((pa.sku)::text = (p.sku)::text)
16. 710.877 4,682.094 ↑ 1.9 36,861 1

Finalize GroupAggregate (cost=16,120.04..26,594.14 rows=69,471 width=171) (actual time=2,966.196..4,682.094 rows=36,861 loops=1)

  • Group Key: m.seller_id, p_1.marketplace_id, pf.date, pa.sku
17. 0.000 3,971.217 ↑ 1.0 55,697 1

Gather Merge (cost=16,120.04..23,960.05 rows=57,892 width=171) (actual time=2,966.111..3,971.217 rows=55,697 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 921.888 10,235.850 ↑ 1.6 18,566 3

Partial GroupAggregate (cost=15,120.01..16,277.85 rows=28,946 width=171) (actual time=2,945.701..3,411.950 rows=18,566 loops=3)

  • Group Key: m.seller_id, p_1.marketplace_id, pf.date, pa.sku
19. 1,177.818 9,313.962 ↑ 1.2 24,511 3

Sort (cost=15,120.01..15,192.38 rows=28,946 width=101) (actual time=2,945.653..3,104.654 rows=24,511 loops=3)

  • Sort Key: m.seller_id, p_1.marketplace_id, pf.date, pa.sku
  • Sort Method: quicksort Memory: 4022kB
  • Worker 0: Sort Method: quicksort Memory: 4020kB
  • Worker 1: Sort Method: quicksort Memory: 4056kB
20. 940.362 8,136.144 ↑ 1.2 24,511 3

Hash Join (cost=3,829.74..12,974.96 rows=28,946 width=101) (actual time=324.344..2,712.048 rows=24,511 loops=3)

  • Hash Cond: (p_1.merchant_id = m.id)
21. 958.224 7,195.374 ↑ 1.2 24,511 3

Hash Join (cost=3,803.54..12,872.33 rows=28,946 width=77) (actual time=324.161..2,398.458 rows=24,511 loops=3)

  • Hash Cond: (pa.profile_id = p_1.id)
22. 2,875.431 6,236.694 ↑ 1.2 24,511 3

Parallel Hash Join (cost=3,787.47..12,779.08 rows=28,946 width=45) (actual time=323.981..2,078.898 rows=24,511 loops=3)

  • Hash Cond: (pa.id = pf.product_ad_id)
23. 2,399.877 2,399.877 ↑ 1.3 128,466 3

Parallel Seq Scan on product_ads pa (cost=0.00..6,208.13 rows=160,813 width=31) (actual time=0.066..799.959 rows=128,466 loops=3)

24. 483.342 961.386 ↑ 1.7 24,511 3

Parallel Hash (cost=3,276.65..3,276.65 rows=40,865 width=30) (actual time=320.456..320.462 rows=24,511 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 5696kB
25. 478.044 478.044 ↑ 1.7 24,511 3

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

26. 0.216 0.456 ↑ 33.8 8 3

Hash (cost=12.70..12.70 rows=270 width=48) (actual time=0.146..0.152 rows=8 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.240 0.240 ↑ 33.8 8 3

Seq Scan on profiles p_1 (cost=0.00..12.70 rows=270 width=48) (actual time=0.017..0.080 rows=8 loops=3)

28. 0.180 0.408 ↑ 120.0 6 3

Hash (cost=17.20..17.20 rows=720 width=36) (actual time=0.130..0.136 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.228 0.228 ↑ 120.0 6 3

Seq Scan on merchants m (cost=0.00..17.20 rows=720 width=36) (actual time=0.026..0.076 rows=6 loops=3)

30. 42.139 4,156.354 ↓ 1.3 6,001 1

Hash (cost=17,548.25..17,548.25 rows=4,453 width=86) (actual time=4,156.348..4,156.354 rows=6,001 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 704kB
31. 102.619 4,114.215 ↓ 1.3 6,001 1

Hash Join (cost=16,108.03..17,548.25 rows=4,453 width=86) (actual time=1,879.671..4,114.215 rows=6,001 loops=1)

  • Hash Cond: ((skus_with_sales_or_advertising.sku)::text = (p.sku)::text)
32. 2,213.670 2,213.670 ↓ 1.1 6,012 1

CTE Scan on skus_with_sales_or_advertising (cost=0.00..105.84 rows=5,292 width=32) (actual time=81.476..2,213.670 rows=6,012 loops=1)

33. 883.778 1,797.926 ↓ 1.0 122,576 1

Hash (cost=13,381.99..13,381.99 rows=122,404 width=54) (actual time=1,797.919..1,797.926 rows=122,576 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3118kB
34. 914.148 914.148 ↓ 1.0 122,576 1

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

  • Filter: (is_valid AND ((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Rows Removed by Filter: 23023
Planning time : 1.595 ms
Execution time : 11,024.473 ms