explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4w8L

Settings
# exclusive inclusive rows x rows loops node
1. 14.888 5,196.319 ↓ 980.0 980 1

GroupAggregate (cost=207,299.54..207,299.63 rows=1 width=796) (actual time=5,180.875..5,196.319 rows=980 loops=1)

  • Group Key: 'GOJA LLC - Merchandising'::text, dm.dms_marketplace, dp.dpt_master_sku, dp.dpt_ad_code, dd.ddt_date
2. 26.245 5,181.431 ↓ 17,518.0 17,518 1

Sort (cost=207,299.54..207,299.54 rows=1 width=608) (actual time=5,180.223..5,181.431 rows=17,518 loops=1)

  • Sort Key: dm.dms_marketplace, dp.dpt_master_sku, dp.dpt_ad_code, dd.ddt_date
  • Sort Method: quicksort Memory: 3,049kB
3. 6.628 5,155.186 ↓ 17,518.0 17,518 1

Nested Loop (cost=179,437.02..207,299.53 rows=1 width=608) (actual time=3,482.388..5,155.186 rows=17,518 loops=1)

4. 414.140 5,131.040 ↓ 17,518.0 17,518 1

Hash Join (cost=179,436.89..207,299.36 rows=1 width=73) (actual time=3,482.379..5,131.040 rows=17,518 loops=1)

  • Hash Cond: ((ffp.ffp_ct_sid)::numeric = dp.dpt_product_sid)
  • Join Filter: ((SubPlan 2) = ffp.ffp_date_sid)
  • Rows Removed by Join Filter: 481,830
5. 238.096 238.096 ↓ 1.0 175,231 1

Seq Scan on fact_fee_and_price ffp (cost=0.00..26,914.46 rows=175,090 width=22) (actual time=0.010..238.096 rows=175,231 loops=1)

  • Filter: (ffp_account_sid = ANY ('{0,1,11}'::numeric[]))
  • Rows Removed by Filter: 875
6. 12.769 3,480.108 ↓ 35,516.0 35,516 1

Hash (cost=179,436.87..179,436.87 rows=1 width=80) (actual time=3,480.108..3,480.108 rows=35,516 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,841kB
7. 87.665 3,467.339 ↓ 35,516.0 35,516 1

Hash Join (cost=148,619.98..179,436.87 rows=1 width=80) (actual time=2,612.212..3,467.339 rows=35,516 loops=1)

  • Hash Cond: ((fprag.fpa_date_sid = dd.ddt_date_sid) AND ((dim_products.dpt_master_sku)::text = (dp.dpt_master_sku)::text) AND (fprag.fpa_asin = (dp.dpt_ad_code)::text))
8. 170.568 3,305.560 ↑ 1.8 339,365 1

GroupAggregate (cost=131,384.50..149,423.65 rows=601,305 width=117) (actual time=2,536.238..3,305.560 rows=339,365 loops=1)

  • Group Key: fprag.fpa_account_sid, fprag.fpa_marketplace_sid, dim_products.dpt_master_sku, fprag.fpa_asin, fprag.fpa_date_sid, fprag.fpa_is_other
9. 2,717.146 3,134.992 ↑ 1.3 475,597 1

Sort (cost=131,384.50..132,887.76 rows=601,305 width=53) (actual time=2,536.222..3,134.992 rows=475,597 loops=1)

  • Sort Key: fprag.fpa_account_sid, fprag.fpa_marketplace_sid, dim_products.dpt_master_sku, fprag.fpa_asin, fprag.fpa_date_sid, fprag.fpa_is_other
  • Sort Method: external merge Disk: 29,432kB
10. 156.073 417.846 ↑ 1.3 475,597 1

Hash Join (cost=3,448.22..53,110.52 rows=601,305 width=53) (actual time=8.324..417.846 rows=475,597 loops=1)

  • Hash Cond: (fprag.fpa_product_sid = dim_products.dpt_product_sid)
11. 253.647 253.647 ↓ 1.0 677,815 1

Seq Scan on fact_ppc_report_ad_group_agr fprag (cost=0.00..41,111.96 rows=676,612 width=53) (actual time=0.034..253.647 rows=677,815 loops=1)

  • Filter: (fpa_date_sid >= '20200101'::numeric)
  • Rows Removed by Filter: 624,982
12. 2.824 8.126 ↑ 1.8 15,143 1

Hash (cost=3,102.54..3,102.54 rows=27,654 width=10) (actual time=8.126..8.126 rows=15,143 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 903kB
13. 5.302 5.302 ↑ 1.8 15,143 1

Seq Scan on dim_products (cost=0.00..3,102.54 rows=27,654 width=10) (actual time=0.004..5.302 rows=15,143 loops=1)

14. 8.878 74.114 ↓ 7.6 25,885 1

Hash (cost=17,176.00..17,176.00 rows=3,399 width=79) (actual time=74.114..74.114 rows=25,885 loops=1)

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3,357kB
15. 9.749 65.236 ↓ 7.6 25,886 1

Hash Join (cost=3,537.44..17,176.00 rows=3,399 width=79) (actual time=9.934..65.236 rows=25,886 loops=1)

  • Hash Cond: (fo.for_product_sid = dp.dpt_product_sid)
16. 8.966 46.943 ↓ 7.6 25,899 1

Merge Join (cost=89.22..13,681.05 rows=3,399 width=49) (actual time=1.345..46.943 rows=25,899 loops=1)

  • Merge Cond: (fo.for_purchase_date_sid = dd.ddt_date_sid)
17. 33.909 33.909 ↓ 7.6 25,899 1

Index Scan using fact_orders_for_purchase_date_id_idx on fact_orders fo (cost=0.43..13,441.01 rows=3,399 width=38) (actual time=0.022..33.909 rows=25,899 loops=1)

  • Index Cond: (for_purchase_date_sid >= '20200901'::numeric)
  • Filter: ((for_order_status)::text !~~ 'Ca%'::text)
  • Rows Removed by Filter: 762
18. 4.068 4.068 ↓ 4.1 29,796 1

Index Scan using dim_date_pkey on dim_date dd (cost=0.28..350.86 rows=7,305 width=11) (actual time=0.020..4.068 rows=29,796 loops=1)

19. 2.701 8.544 ↑ 1.8 15,143 1

Hash (cost=3,102.54..3,102.54 rows=27,654 width=30) (actual time=8.544..8.544 rows=15,143 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,164kB
20. 5.843 5.843 ↑ 1.8 15,143 1

Seq Scan on dim_products dp (cost=0.00..3,102.54 rows=27,654 width=30) (actual time=0.004..5.843 rows=15,143 loops=1)

21.          

SubPlan (for Hash Join)

22. 0.000 998.696 ↑ 1.0 1 499,348

Result (cost=12.07..12.08 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=499,348)

23.          

Initplan (for Result)

24. 0.000 998.696 ↑ 1.0 1 499,348

Limit (cost=0.42..12.07 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=499,348)

25. 998.696 998.696 ↑ 3.0 1 499,348

Index Only Scan Backward using fact_fee_and_price_ffp_ct_sid_idx on fact_fee_and_price (cost=0.42..35.38 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=499,348)

  • Index Cond: ((ffp_ct_sid = ffp.ffp_ct_sid) AND (ffp_date_sid IS NOT NULL) AND (ffp_date_sid >= 20,200,101))
  • Filter: ((ffp_date_sid)::numeric <= fo.for_purchase_date_sid)
  • Rows Removed by Filter: 0
  • Heap Fetches: 510,620
26. 17.518 17.518 ↑ 1.0 1 17,518

Index Scan using pk_dms_marketplace_id on dim_marketplaces dm (cost=0.14..0.15 rows=1 width=584) (actual time=0.001..0.001 rows=1 loops=17,518)

  • Index Cond: ((dms_marketplace_sid)::text = (dp.dpt_marketplace)::text)
Planning time : 1.814 ms
Execution time : 5,200.704 ms