explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7yt1

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 9,574.673 ↑ 1.0 1 1

GroupAggregate (cost=5,260,667,875.11..5,260,667,875.21 rows=1 width=732) (actual time=9,574.673..9,574.673 rows=1 loops=1)

  • Group Key: '01 Commerce - Merchandising'::text, dm.dms_marketplace, dp.dpt_master_sku, dp.dpt_ad_code, ((to_char(((timezone('America/Los_Angeles'::text, timezone('utc'::text, fo.for_purchase_date_time)))::date)::timestamp with time zone, 'YYYYMMDD'::text))::integer), fpa.spend
2. 0.050 9,574.633 ↓ 11.0 11 1

Sort (cost=5,260,667,875.11..5,260,667,875.11 rows=1 width=608) (actual time=9,574.630..9,574.633 rows=11 loops=1)

  • Sort Key: dm.dms_marketplace, dp.dpt_master_sku, fpa.spend
  • Sort Method: quicksort Memory: 26kB
3. 0.264 9,574.583 ↓ 11.0 11 1

Nested Loop (cost=59,880.88..5,260,667,875.10 rows=1 width=608) (actual time=1,038.576..9,574.583 rows=11 loops=1)

4. 0.081 1,045.744 ↓ 11.0 11 1

Nested Loop (cost=59,880.88..71,982.16 rows=1 width=570) (actual time=649.170..1,045.744 rows=11 loops=1)

  • Join Filter: ((dp.dpt_marketplace)::text = (dm.dms_marketplace_sid)::text)
  • Rows Removed by Join Filter: 33
5. 0.034 1,045.531 ↓ 11.0 11 1

Nested Loop (cost=59,880.88..71,980.88 rows=1 width=67) (actual time=649.146..1,045.531 rows=11 loops=1)

6. 0.022 436.765 ↓ 2.0 2 1

Hash Join (cost=59,738.34..62,835.40 rows=1 width=38) (actual time=421.129..436.765 rows=2 loops=1)

  • Hash Cond: ((dp.dpt_master_sku)::text = (fpa.fpa_master_sku)::text)
7. 18.801 18.801 ↑ 1.4 5 1

Seq Scan on dim_products dp (cost=0.00..3,097.03 rows=7 width=30) (actual time=3.168..18.801 rows=5 loops=1)

  • Filter: ((dpt_ad_code)::text = 'B01CU94DHO'::text)
  • Rows Removed by Filter: 15,143
8. 0.002 417.942 ↑ 1.0 1 1

Hash (cost=59,738.33..59,738.33 rows=1 width=23) (actual time=417.942..417.942 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.003 417.940 ↑ 1.0 1 1

Subquery Scan on fpa (cost=59,738.29..59,738.33 rows=1 width=23) (actual time=417.939..417.940 rows=1 loops=1)

10. 0.010 417.937 ↑ 1.0 1 1

GroupAggregate (cost=59,738.29..59,738.32 rows=1 width=117) (actual time=417.937..417.937 rows=1 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
11. 0.020 417.927 ↓ 2.0 2 1

Sort (cost=59,738.29..59,738.29 rows=1 width=53) (actual time=417.926..417.927 rows=2 loops=1)

  • Sort Key: fprag.fpa_account_sid, fprag.fpa_marketplace_sid, dim_products.dpt_master_sku, fprag.fpa_is_other
  • Sort Method: quicksort Memory: 25kB
12. 0.014 417.907 ↓ 2.0 2 1

Nested Loop (cost=0.29..59,738.28 rows=1 width=53) (actual time=188.594..417.907 rows=2 loops=1)

13. 417.854 417.854 ↓ 3.0 3 1

Seq Scan on fact_ppc_report_ad_group_agr fprag (cost=0.00..59,729.96 rows=1 width=53) (actual time=188.564..417.854 rows=3 loops=1)

  • Filter: ((fpa_date_sid = '20200901'::numeric) AND (fpa_asin = 'B01CU94DHO'::text) AND (fpa_account_sid = ANY ('{2,3,4,5,6,7,8,9,10}'::numeric[])))
  • Rows Removed by Filter: 1,318,205
14. 0.039 0.039 ↑ 1.0 1 3

Index Scan using pk_product_id on dim_products (cost=0.29..8.30 rows=1 width=10) (actual time=0.013..0.013 rows=1 loops=3)

  • Index Cond: (dpt_product_sid = fprag.fpa_product_sid)
15. 585.214 608.732 ↓ 1.5 6 2

Bitmap Heap Scan on fact_orders fo (cost=142.54..9,145.44 rows=4 width=39) (actual time=113.996..304.366 rows=6 loops=2)

  • Recheck Cond: (for_product_sid = dp.dpt_product_sid)
  • Filter: (((for_order_status)::text !~~ 'Ca%'::text) AND (for_account_sid = ANY ('{2,3,4,5,6,7,8,9,10}'::numeric[])) AND ((to_char(((timezone('America/Los_Angeles'::text, timezone('utc'::text, for_purchase_date_time)))::date)::timestamp with time zone, 'YYYYMMDD'::text))::integer = 20,200,901))
  • Rows Removed by Filter: 33,913
  • Heap Blocks: exact=63,004
16. 23.518 23.518 ↓ 18.8 45,273 2

Bitmap Index Scan on fact_orders_for_product_id_idx (cost=0.00..142.53 rows=2,414 width=0) (actual time=11.759..11.759 rows=45,273 loops=2)

  • Index Cond: (for_product_sid = dp.dpt_product_sid)
17. 0.132 0.132 ↑ 1.0 4 11

Seq Scan on dim_marketplaces dm (cost=0.00..1.23 rows=4 width=584) (actual time=0.005..0.012 rows=4 loops=11)

  • Filter: ((dms_marketplace)::text = ANY ('{""Amazon US"",""Amazon CA"",""Amazon MX"",Shopify}'::text[]))
  • Rows Removed by Filter: 11
18. 4,270.299 8,528.575 ↑ 1.0 1 11

Seq Scan on fact_fee_and_price ffp (cost=0.00..5,260,595,892.91 rows=1 width=20) (actual time=387.129..775.325 rows=1 loops=11)

  • Filter: ((ffp_account_sid IS NULL) AND (ffp_asin = 'B01CU94DHO'::text) AND (ffp_date_sid = (SubPlan 1)))
  • Rows Removed by Filter: 176,393
19.          

SubPlan (for Seq Scan)

20. 0.088 4,258.276 ↑ 1.0 1 11

Aggregate (cost=29,822.82..29,822.83 rows=1 width=4) (actual time=387.116..387.116 rows=1 loops=11)

21. 4,258.188 4,258.188 ↑ 1.0 1 11

Seq Scan on fact_fee_and_price (cost=0.00..29,822.82 rows=1 width=4) (actual time=0.011..387.108 rows=1 loops=11)

  • Filter: ((ffp_account_sid IS NULL) AND (ffp.ffp_asin = ffp_asin) AND (ffp_date_sid <= (to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYYMMDD'::text))::integer))
  • Rows Removed by Filter: 176,393
Planning time : 2.734 ms
Execution time : 9,575.306 ms