explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9NSL

Settings
# exclusive inclusive rows x rows loops node
1. 1.518 2,973.741 ↑ 3.3 125 1

GroupAggregate (cost=1,458,105.22..1,458,119.01 rows=412 width=112) (actual time=2,971.963..2,973.741 rows=125 loops=1)

  • Group Key: margins.campaign_id, margins.profile_id
2. 1.640 2,972.223 ↓ 1.1 4,647 1

Sort (cost=1,458,105.22..1,458,107.28 rows=4,116 width=48) (actual time=2,971.946..2,972.223 rows=4,647 loops=1)

  • Sort Key: margins.campaign_id, margins.profile_id
  • Sort Method: quicksort Memory: 556kB
3. 0.590 2,970.583 ↓ 1.1 4,647 1

Subquery Scan on margins (cost=1,457,202.90..1,458,055.80 rows=4,116 width=48) (actual time=2,797.149..2,970.583 rows=4,647 loops=1)

4. 5.814 2,969.993 ↓ 1.1 4,647 1

Hash Join (cost=1,457,202.90..1,458,043.45 rows=4,116 width=80) (actual time=2,797.147..2,969.993 rows=4,647 loops=1)

  • Hash Cond: ((p.sku)::text = (pc.sku)::text)
5.          

CTE products_by_campaigns

6. 0.937 261.674 ↓ 4,647.0 4,647 1

Unique (cost=25,649.55..25,649.56 rows=1 width=38) (actual time=260.495..261.674 rows=4,647 loops=1)

7. 7.296 260.737 ↓ 4,890.0 4,890 1

Sort (cost=25,649.55..25,649.56 rows=1 width=38) (actual time=260.494..260.737 rows=4,890 loops=1)

  • Sort Key: c2.id, p_1.sku
  • Sort Method: quicksort Memory: 575kB
8. 2.566 253.441 ↓ 4,890.0 4,890 1

Nested Loop (cost=2.50..25,649.55 rows=1 width=38) (actual time=2.430..253.441 rows=4,890 loops=1)

9. 5.686 237.949 ↓ 6,463.0 6,463 1

Nested Loop (cost=2.41..25,645.77 rows=1 width=54) (actual time=2.417..237.949 rows=6,463 loops=1)

10. 1.208 167.093 ↓ 9,310.0 9,310 1

Nested Loop (cost=2.30..25,641.67 rows=1 width=48) (actual time=2.395..167.093 rows=9,310 loops=1)

11. 0.086 0.120 ↓ 2.7 129 1

Bitmap Heap Scan on campaigns c2 (cost=2.18..96.61 rows=48 width=16) (actual time=0.041..0.120 rows=129 loops=1)

  • Recheck Cond: ((profile_id = '3811963422597420'::bigint) AND ((state)::text = 'enabled'::text))
  • Heap Blocks: exact=17
12. 0.034 0.034 ↓ 2.7 129 1

Bitmap Index Scan on campaigns_profile_id_state_index (cost=0.00..2.18 rows=48 width=0) (actual time=0.034..0.034 rows=129 loops=1)

  • Index Cond: ((profile_id = '3811963422597420'::bigint) AND ((state)::text = 'enabled'::text))
13. 165.765 165.765 ↓ 72.0 72 129

Index Scan using product_ads_profile_sku_campaigns_idx on product_ads p_ads (cost=0.11..532.19 rows=1 width=40) (actual time=0.635..1.285 rows=72 loops=129)

  • Index Cond: ((profile_id = '3811963422597420'::bigint) AND (campaign_id = c2.id))
  • Filter: ((state)::text = 'enabled'::text)
  • Rows Removed by Filter: 8
14. 65.170 65.170 ↑ 1.0 1 9,310

Index Scan using products_seller_marketplace_sku_unique on products p_1 (cost=0.11..4.10 rows=1 width=22) (actual time=0.007..0.007 rows=1 loops=9,310)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p_ads.sku)::text))
  • Filter: (is_active AND is_valid)
  • Rows Removed by Filter: 0
15. 12.926 12.926 ↑ 1.0 1 6,463

Index Scan using ad_groups_pkey on ad_groups ag (cost=0.09..3.77 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=6,463)

  • Index Cond: (id = p_ads.ad_group_id)
  • Filter: ((profile_id = '3811963422597420'::bigint) AND ((state)::text = 'enabled'::text))
  • Rows Removed by Filter: 0
16.          

CTE product_cogs

17. 0.729 6.678 ↓ 3.1 4,323 1

Unique (cost=2,688.40..2,689.79 rows=1,378 width=27) (actual time=5.751..6.678 rows=4,323 loops=1)

18. 2.715 5.949 ↓ 3.1 4,323 1

Sort (cost=2,688.40..2,689.09 rows=1,392 width=27) (actual time=5.751..5.949 rows=4,323 loops=1)

  • Sort Key: cost_of_goods_daily_rollup.sku, cost_of_goods_daily_rollup.effective_date DESC
  • Sort Method: quicksort Memory: 530kB
19. 3.234 3.234 ↓ 3.1 4,323 1

Index Scan using cost_of_goods_daily_rollup__pkey on cost_of_goods_daily_rollup (cost=0.11..2,673.86 rows=1,392 width=27) (actual time=0.023..3.234 rows=4,323 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
20.          

CTE product_fees

21. 85.898 2,689.089 ↑ 28.8 4,085 1

Unique (cost=1,427,979.62..1,428,850.45 rows=117,587 width=31) (actual time=2,525.268..2,689.089 rows=4,085 loops=1)

22. 2,115.639 2,603.191 ↓ 1.1 969,563 1

Sort (cost=1,427,979.62..1,428,415.04 rows=870,829 width=31) (actual time=2,525.266..2,603.191 rows=969,563 loops=1)

  • Sort Key: product_fee_projections.sku, product_fee_projections.date DESC
  • Sort Method: quicksort Memory: 100324kB
23. 330.226 487.552 ↓ 1.1 969,563 1

Bitmap Heap Scan on product_fee_projections (cost=31,301.34..1,410,796.40 rows=870,829 width=31) (actual time=172.615..487.552 rows=969,563 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Heap Blocks: exact=81866
24. 157.326 157.326 ↓ 1.1 969,691 1

Bitmap Index Scan on product_fee_projections_seller_market_sku_date_pkey (cost=0.00..31,257.80 rows=870,829 width=0) (actual time=157.326..157.326 rows=969,691 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
25. 1.834 2,955.887 ↓ 7.9 4,647 1

Hash Join (cost=0.01..795.49 rows=588 width=160) (actual time=2,788.841..2,955.887 rows=4,647 loops=1)

  • Hash Cond: ((pf.sku)::text = (p.sku)::text)
26. 2,690.498 2,690.498 ↑ 28.8 4,085 1

CTE Scan on product_fees pf (cost=0.00..705.52 rows=117,587 width=92) (actual time=2,525.272..2,690.498 rows=4,085 loops=1)

27. 0.918 263.555 ↓ 4,647.0 4,647 1

Hash (cost=0.01..0.01 rows=1 width=68) (actual time=263.554..263.555 rows=4,647 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 361kB
28. 262.637 262.637 ↓ 4,647.0 4,647 1

CTE Scan on products_by_campaigns p (cost=0.00..0.01 rows=1 width=68) (actual time=260.498..262.637 rows=4,647 loops=1)

29. 0.807 8.292 ↓ 3.1 4,323 1

Hash (cost=8.27..8.27 rows=1,378 width=52) (actual time=8.292..8.292 rows=4,323 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 277kB
30. 7.485 7.485 ↓ 3.1 4,323 1

CTE Scan on product_cogs pc (cost=0.00..8.27 rows=1,378 width=52) (actual time=5.753..7.485 rows=4,323 loops=1)

Planning time : 4.435 ms
Execution time : 2,980.465 ms