explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bJT5

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

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=52,608.390..52,608.391 rows=1 loops=1)

  • Output: remote_scan.amazon_seller_account_id, remote_scan.marketplace_id, remote_scan.raw_sales, remote_scan.pending_sales, remote_scan.promo_rebates, remote_scan.refunds, remote_scan.reimbursements, remote_scan.other_income, remote_scan.other_income_drilldown
  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=ec2-34-199-109-254.compute-1.amazonaws.com port=5432 dbname=citus
2. 0.003 74,330.491 ↑ 1.0 1 1

Nested Loop Left Join (cost=3,557.72..3,557.77 rows=1 width=232) (actual time=74,330.490..74,330.491 rows=1 loops=1)

  • Output: 46974, 121029, summaries_view.raw_sales, summaries_view.pending_sales, summaries_view.promo_rebates, summaries_view.refunds, summaries_view.reimbursements, summaries_view.other_income, COALESCE(other_income_drilldown_view.other_income_drilldown, '[]'::json)
  • Buffers: shared hit=34715
3.          

CTE other_income_breakdown

4. 0.001 0.059 ↓ 0.0 0 1

Append (cost=0.98..13.78 rows=2 width=44) (actual time=0.059..0.059 rows=0 loops=1)

  • Buffers: shared hit=40
5. 0.001 0.039 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.98..9.31 rows=1 width=28) (actual time=0.038..0.039 rows=0 loops=1)

  • Output: "*SELECT* 1".summary_date, "*SELECT* 1".category, "*SELECT* 1".amount
  • Buffers: shared hit=30
6. 0.000 0.038 ↓ 0.0 0 1

Nested Loop (cost=0.98..9.30 rows=1 width=52) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: product_summary_breakdowns.summary_date, product_summary_breakdowns.category, COALESCE(abs(product_summary_breakdowns.amount), '0'::numeric)
  • Inner Unique: true
  • Buffers: shared hit=30
7. 0.038 0.038 ↓ 0.0 0 1

Index Scan using index_prod_summary_breakdowns_on_amz_id_and_src_type_106897 on public.product_summary_breakdowns_106897 product_summary_breakdowns (cost=0.56..4.54 rows=1 width=42) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: product_summary_breakdowns.id, product_summary_breakdowns.amazon_seller_account_id, product_summary_breakdowns.product_id, product_summary_breakdowns.src_type, product_summary_breakdowns.category, product_summary_breakdowns.amount, product_summary_breakdowns.summary_date, product_summary_breakdowns.created_at, product_summary_breakdowns.updated_at
  • Index Cond: ((product_summary_breakdowns.amazon_seller_account_id = 46974) AND ((product_summary_breakdowns.src_type)::text = 'other_income'::text))
  • Filter: ((product_summary_breakdowns.summary_date >= '2020-01-13'::date) AND (product_summary_breakdowns.summary_date <= '2020-03-19'::date) AND (product_summary_breakdowns.amount <> '0'::numeric))
  • Rows Removed by Filter: 32
  • Buffers: shared hit=30
8. 0.000 0.000 ↓ 0.0 0

Index Scan using products_pkey_105292 on public.products_105292 products (cost=0.42..4.45 rows=1 width=12) (never executed)

  • Output: products.id, products.amazon_seller_account_id
  • Index Cond: ((products.amazon_seller_account_id = 46974) AND (products.id = product_summary_breakdowns.product_id))
  • Filter: (products.marketplace_id = 121029)
9. 0.019 0.019 ↓ 0.0 0 1

Index Scan using index_acc_summary_breakdowns_unique_constraints_106993 on public.account_summary_breakdowns_106993 account_summary_breakdowns (cost=0.42..4.45 rows=1 width=30) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: account_summary_breakdowns.summary_date, account_summary_breakdowns.category, account_summary_breakdowns.amount
  • Index Cond: ((account_summary_breakdowns.amazon_seller_account_id = 46974) AND (account_summary_breakdowns.marketplace_id = 121029) AND (account_summary_breakdowns.summary_date >= '2020-01-13'::date) AND (account_summary_breakdowns.summary_date <= '2020-03-19'::date) AND ((account_summary_breakdowns.src_type)::text = 'other_income'::text))
  • Filter: (account_summary_breakdowns.amount <> '0'::double precision)
  • Rows Removed by Filter: 10
  • Buffers: shared hit=10
10.          

CTE other_income_drilldown_view

11. 0.001 0.062 ↑ 1.0 1 1

Aggregate (cost=0.10..0.11 rows=1 width=32) (actual time=0.062..0.062 rows=1 loops=1)

  • Output: json_agg(json_build_object('category', other_income_breakdown.category, 'amount', (sum(other_income_breakdown.amount))))
  • Buffers: shared hit=40
12. 0.001 0.061 ↓ 0.0 0 1

HashAggregate (cost=0.05..0.07 rows=2 width=40) (actual time=0.060..0.061 rows=0 loops=1)

  • Output: other_income_breakdown.category, sum(other_income_breakdown.amount)
  • Group Key: other_income_breakdown.category
  • Buffers: shared hit=40
13. 0.060 0.060 ↓ 0.0 0 1

CTE Scan on other_income_breakdown (cost=0.00..0.04 rows=2 width=40) (actual time=0.060..0.060 rows=0 loops=1)

  • Output: other_income_breakdown.summary_date, other_income_breakdown.category, other_income_breakdown.amount
  • Buffers: shared hit=40
14.          

CTE summaries_view

15. 0.000 74,330.423 ↑ 1.0 1 1

Sort (cost=3,543.82..3,543.83 rows=1 width=192) (actual time=74,330.423..74,330.423 rows=1 loops=1)

  • Output: (COALESCE(sum(ps.raw_sales), '0'::numeric)), (COALESCE(sum((COALESCE(estimated_data_summaries_table.pending_sales, '0'::numeric))), '0'::numeric)), (COALESCE(sum(ps.promo_rebates), '0'::numeric)), (COALESCE(sum(ps.refunds), '0'::numeric)), (COALESCE(sum(ps.reimbursements), '0'::numeric)), ((COALESCE(sum(ps.other_income) FILTER (WHERE (ps.other_income >= '0'::numeric)), '0'::numeric) + COALESCE($5, '0'::numeric)))
  • Sort Key: (COALESCE(sum(ps.raw_sales), '0'::numeric))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=34675
16.          

CTE estimated_data_summaries_table

17. 1.153 13.036 ↓ 470.8 3,766 1

Nested Loop (cost=1.11..867.80 rows=8 width=108) (actual time=0.014..13.036 rows=3,766 loops=1)

  • Output: product_summaries.id, product_summaries.product_id, product_summaries.amazon_seller_account_id, product_summaries.created_at, product_summaries.updated_at, product_summaries.summary_date, product_summaries.fba_fees, product_summaries.supplier_shipping, product_summaries.manual_expenses, product_summaries.pending_sales, product_summaries.promo_rebates, product_summaries.promo_units_sold, product_summaries.raw_sales, product_summaries.refunds, product_summaries.regular_units_sold, product_summaries.sales_tax, product_summaries.units_pending, product_summaries.units_refunded, product_summaries.unit_cost, product_summaries.shipping_cost, product_summaries.misc_cost, product_summaries.estimated_fees, product_summaries.other_income, product_summaries.reimbursements, product_summaries.reshipping, 121029
  • Buffers: shared hit=13825
18. 1.631 1.631 ↓ 102.5 2,563 1

Index Scan using uniq_index_products_on_acct_market_sku_asin_105292 on public.products_105292 products_1 (cost=0.55..51.80 rows=25 width=12) (actual time=0.005..1.631 rows=2,563 loops=1)

  • Output: products_1.amazon_seller_account_id, products_1.id
  • Index Cond: ((products_1.amazon_seller_account_id = 46974) AND (products_1.marketplace_id = 121029))
  • Buffers: shared hit=2152
19. 10.252 10.252 ↑ 15.0 1 2,563

Index Scan using idx_product_summaries_on_amz_id_product_id_summary_date_106673 on public.product_summaries_106673 product_summaries (cost=0.57..32.49 rows=15 width=104) (actual time=0.003..0.004 rows=1 loops=2,563)

  • Output: product_summaries.id, product_summaries.product_id, product_summaries.amazon_seller_account_id, product_summaries.created_at, product_summaries.updated_at, product_summaries.summary_date, product_summaries.fba_fees, product_summaries.supplier_shipping, product_summaries.manual_expenses, product_summaries.pending_sales, product_summaries.promo_rebates, product_summaries.promo_units_sold, product_summaries.raw_sales, product_summaries.refunds, product_summaries.regular_units_sold, product_summaries.sales_tax, product_summaries.units_pending, product_summaries.units_refunded, product_summaries.unit_cost, product_summaries.shipping_cost, product_summaries.misc_cost, product_summaries.estimated_fees, product_summaries.other_income, product_summaries.reimbursements, product_summaries.reshipping
  • Index Cond: ((product_summaries.amazon_seller_account_id = 46974) AND (product_summaries.product_id = products_1.id) AND (product_summaries.summary_date >= '2020-03-05'::date) AND (product_summaries.summary_date <= '2020-03-19'::date))
  • Buffers: shared hit=11673
20.          

Initplan (for Sort)

21. 0.007 0.051 ↑ 1.0 1 1

Aggregate (cost=4.45..4.46 rows=1 width=32) (actual time=0.051..0.051 rows=1 loops=1)

  • Output: sum(account_summaries.other_income)
  • Buffers: shared hit=34
22. 0.044 0.044 ↓ 31.0 31 1

Index Scan using index_acc_summaries_on_uniq_acct_id_and_date_and_market_106929 on public.account_summaries_106929 account_summaries (cost=0.42..4.45 rows=1 width=3) (actual time=0.014..0.044 rows=31 loops=1)

  • Output: account_summaries.other_income
  • Index Cond: ((account_summaries.amazon_seller_account_id = 46974) AND (account_summaries.marketplace_id = 121029) AND (account_summaries.summary_date >= '2020-01-13'::date) AND (account_summaries.summary_date <= '2020-03-19'::date))
  • Filter: (account_summaries.other_income >= '0'::numeric)
  • Buffers: shared hit=34
23. 43.236 74,330.416 ↑ 1.0 1 1

Aggregate (cost=2,671.55..2,671.56 rows=1 width=192) (actual time=74,330.416..74,330.416 rows=1 loops=1)

  • Output: COALESCE(sum(ps.raw_sales), '0'::numeric), COALESCE(sum((COALESCE(estimated_data_summaries_table.pending_sales, '0'::numeric))), '0'::numeric), COALESCE(sum(ps.promo_rebates), '0'::numeric), COALESCE(sum(ps.refunds), '0'::numeric), COALESCE(sum(ps.reimbursements), '0'::numeric), (COALESCE(sum(ps.other_income) FILTER (WHERE (ps.other_income >= '0'::numeric)), '0'::numeric) + COALESCE($5, '0'::numeric))
  • Buffers: shared hit=34675
24. 20,376.012 74,287.180 ↓ 3,599.9 93,598 1

Nested Loop Left Join (cost=1.11..2,671.08 rows=26 width=47) (actual time=15.767..74,287.180 rows=93,598 loops=1)

  • Output: ps.raw_sales, (COALESCE(estimated_data_summaries_table.pending_sales, '0'::numeric)), ps.promo_rebates, ps.refunds, ps.reimbursements, ps.other_income
  • Join Filter: ((estimated_data_summaries_table.amazon_seller_account_id = ps.amazon_seller_account_id) AND (estimated_data_summaries_table.product_id = ps.product_id) AND (estimated_data_summaries_table.summary_date = ps.summary_date))
  • Rows Removed by Join Filter: 352486302
  • Buffers: shared hit=34641
25. 18.649 92.318 ↓ 3,599.9 93,598 1

Nested Loop (cost=1.11..2,670.19 rows=26 width=31) (actual time=0.036..92.318 rows=93,598 loops=1)

  • Output: ps.raw_sales, ps.promo_rebates, ps.refunds, ps.reimbursements, ps.other_income, ps.product_id, ps.amazon_seller_account_id, ps.summary_date
  • Buffers: shared hit=20816
26. 4.468 4.468 ↓ 102.5 2,563 1

Index Scan using uniq_index_products_on_acct_market_sku_asin_105292 on public.products_105292 p (cost=0.55..51.80 rows=25 width=12) (actual time=0.016..4.468 rows=2,563 loops=1)

  • Output: p.id, p.amazon_seller_account_id
  • Index Cond: ((p.amazon_seller_account_id = 46974) AND (p.marketplace_id = 121029))
  • Buffers: shared hit=2152
27. 69.201 69.201 ↑ 1.4 37 2,563

Index Scan using idx_product_summaries_on_amz_id_product_id_summary_date_106673 on public.product_summaries_106673 ps (cost=0.57..104.23 rows=51 width=31) (actual time=0.009..0.027 rows=37 loops=2,563)

  • Output: ps.raw_sales, ps.promo_rebates, ps.refunds, ps.reimbursements, ps.other_income, ps.product_id, ps.amazon_seller_account_id, ps.summary_date
  • Index Cond: ((ps.amazon_seller_account_id = 46974) AND (ps.product_id = p.id) AND (ps.summary_date >= '2020-01-13'::date) AND (ps.summary_date <= '2020-03-19'::date))
  • Buffers: shared hit=18664
28. 53,818.850 53,818.850 ↓ 3,766.0 3,766 93,598

CTE Scan on estimated_data_summaries_table (cost=0.00..0.18 rows=1 width=48) (actual time=0.000..0.575 rows=3,766 loops=93,598)

  • Output: estimated_data_summaries_table.product_id, estimated_data_summaries_table.amazon_seller_account_id, estimated_data_summaries_table.summary_date, COALESCE(estimated_data_summaries_table.pending_sales, '0'::numeric)
  • Filter: (estimated_data_summaries_table.amazon_seller_account_id = 46974)
  • Buffers: shared hit=13825
29. 74,330.425 74,330.425 ↑ 1.0 1 1

CTE Scan on summaries_view (cost=0.00..0.02 rows=1 width=192) (actual time=74,330.424..74,330.425 rows=1 loops=1)

  • Output: summaries_view.raw_sales, summaries_view.pending_sales, summaries_view.promo_rebates, summaries_view.refunds, summaries_view.reimbursements, summaries_view.other_income
  • Buffers: shared hit=34675
30. 0.063 0.063 ↑ 1.0 1 1

CTE Scan on other_income_drilldown_view (cost=0.00..0.02 rows=1 width=32) (actual time=0.063..0.063 rows=1 loops=1)

  • Output: other_income_drilldown_view.other_income_drilldown
  • Buffers: shared hit=40
Planning time : 1.496 ms
Execution time : 52,608.411 ms