explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ivWq

Settings
# exclusive inclusive rows x rows loops node
1. 2,232.486 32,037.092 ↓ 0.0 1 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=32,037.091..32,037.092 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-107-23-9-148.compute-1.amazonaws.com port=5432 dbname=citus
2. 0.003 29,804.606 ↑ 1.0 1 1

Nested Loop Left Join (cost=186,417.63..186,417.68 rows=1 width=232) (actual time=29,804.605..29,804.606 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=1221779
3.          

CTE other_income_breakdown

4. 0.001 0.071 ↓ 0.0 0 1

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

  • Buffers: shared hit=45
5. 0.000 0.042 ↓ 0.0 0 1

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

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

Nested Loop (cost=0.98..9.30 rows=1 width=52) (actual time=0.041..0.042 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.041 0.041 ↓ 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.041..0.041 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 >= '2019-11-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.028 0.028 ↓ 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.028..0.028 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 >= '2019-11-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: 20
  • Buffers: shared hit=15
10.          

CTE other_income_drilldown_view

11. 0.001 0.074 ↑ 1.0 1 1

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

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

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

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

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

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

CTE summaries_view

15. 0.000 29,804.525 ↑ 1.0 1 1

Sort (cost=186,403.73..186,403.74 rows=1 width=192) (actual time=29,804.524..29,804.525 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($6, '0'::numeric)))
  • Sort Key: (COALESCE(sum(ps.raw_sales), '0'::numeric))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1221734
16.          

CTE estimated_data_summaries_table

17. 26.783 711.447 ↓ 0.0 0 1

Gather (cost=5,299.49..51,072.21 rows=12 width=108) (actual time=711.385..711.447 rows=0 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
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=149796
18. 1.730 684.664 ↓ 0.0 0 3 / 3

Nested Loop (cost=4,299.49..50,071.01 rows=5 width=108) (actual time=684.663..684.664 rows=0 loops=3)

  • 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=149796
  • Worker 0: actual time=666.431..666.431 rows=0 loops=1
  • Buffers: shared hit=44709
  • Worker 1: actual time=693.059..693.059 rows=0 loops=1
  • Buffers: shared hit=42803
19. 1.176 1.176 ↓ 85.4 854 3 / 3

Parallel Index Scan using products_pkey_105292 on public.products_105292 products_1 (cost=0.42..6,593.91 rows=10 width=12) (actual time=0.012..1.176 rows=854 loops=3)

  • Output: products_1.amazon_seller_account_id, products_1.id
  • Index Cond: (products_1.amazon_seller_account_id = 46974)
  • Filter: (products_1.marketplace_id = 121029)
  • Rows Removed by Filter: 371
  • Buffers: shared hit=2218
  • Worker 0: actual time=0.009..1.133 rows=804 loops=1
  • Buffers: shared hit=556
  • Worker 1: actual time=0.008..1.015 rows=730 loops=1
  • Buffers: shared hit=555
20. 0.854 681.758 ↓ 0.0 0 2,563 / 3

Bitmap Heap Scan on public.product_summaries_106673 product_summaries (cost=4,299.07..4,347.47 rows=24 width=104) (actual time=0.798..0.798 rows=0 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
  • Recheck Cond: ((product_summaries.product_id = products_1.id) AND (product_summaries.amazon_seller_account_id = 46974) AND (product_summaries.summary_date >= '2020-03-19'::date) AND (product_summaries.summary_date <= '2019-03-05'::date))
  • Buffers: shared hit=147578
  • Worker 0: actual time=0.826..0.826 rows=0 loops=804
  • Buffers: shared hit=44153
  • Worker 1: actual time=0.946..0.946 rows=0 loops=730
  • Buffers: shared hit=42248
21. 11.523 680.904 ↓ 0.0 0 2,563 / 3

BitmapAnd (cost=4,299.07..4,299.07 rows=24 width=0) (actual time=0.797..0.797 rows=0 loops=2,563)

  • Buffers: shared hit=147578
  • Worker 0: actual time=0.824..0.824 rows=0 loops=804
  • Buffers: shared hit=44153
  • Worker 1: actual time=0.944..0.944 rows=0 loops=730
  • Buffers: shared hit=42248
22. 667.234 667.234 ↑ 1.0 14,419 2,563 / 3

Bitmap Index Scan on index_product_summaries_on_product_id_106673 (cost=0.00..224.51 rows=14,680 width=0) (actual time=0.781..0.781 rows=14,419 loops=2,563)

  • Index Cond: (product_summaries.product_id = products_1.id)
  • Buffers: shared hit=138992
  • Worker 0: actual time=0.811..0.811 rows=13694 loops=804
  • Buffers: shared hit=41524
  • Worker 1: actual time=0.931..0.931 rows=14532 loops=730
  • Buffers: shared hit=39835
23. 2.146 2.146 ↓ 0.0 0 2,146 / 3

Bitmap Index Scan on uniq_index_product_summaries_on_product_and_summary_date_106673 (cost=0.00..4,032.39 rows=167,666 width=0) (actual time=0.003..0.003 rows=0 loops=2,146)

  • Index Cond: ((product_summaries.amazon_seller_account_id = 46974) AND (product_summaries.summary_date >= '2020-03-19'::date) AND (product_summaries.summary_date <= '2019-03-05'::date))
  • Buffers: shared hit=8586
  • Worker 0: actual time=0.004..0.004 rows=0 loops=657
  • Buffers: shared hit=2629
  • Worker 1: actual time=0.003..0.003 rows=0 loops=603
  • Buffers: shared hit=2413
24.          

Initplan (for Sort)

25. 0.011 0.095 ↑ 1.0 1 1

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

  • Output: sum(account_summaries.other_income)
  • Buffers: shared hit=61
26. 0.084 0.084 ↓ 58.0 58 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.025..0.084 rows=58 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 >= '2019-11-13'::date) AND (account_summaries.summary_date <= '2020-03-19'::date))
  • Filter: (account_summaries.other_income >= '0'::numeric)
  • Buffers: shared hit=61
27. 90.756 29,804.519 ↑ 1.0 1 1

Aggregate (cost=135,327.04..135,327.06 rows=1 width=192) (actual time=29,804.519..29,804.519 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($6, '0'::numeric))
  • Buffers: shared hit=1221734
28. 27.992 29,713.763 ↓ 6,041.9 217,508 1

Merge Left Join (cost=13,623.75..135,326.40 rows=36 width=47) (actual time=739.992..29,713.763 rows=217,508 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
  • Merge Cond: (ps.product_id = estimated_data_summaries_table.product_id)
  • Join Filter: ((estimated_data_summaries_table.amazon_seller_account_id = ps.amazon_seller_account_id) AND (estimated_data_summaries_table.summary_date = ps.summary_date))
  • Buffers: shared hit=1221673
29. 18,305.788 28,974.377 ↓ 6,041.9 217,508 1

Gather Merge (cost=13,623.47..135,326.01 rows=36 width=31) (actual time=28.593..28,974.377 rows=217,508 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
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2701715
30. 12.725 10,668.589 ↓ 4,833.5 72,503 3 / 3

Nested Loop (cost=12,623.44..134,321.83 rows=15 width=31) (actual time=22.120..10,668.589 rows=72,503 loops=3)

  • 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=2701715
  • Worker 0: actual time=22.755..10003.059 rows=68003 loops=1
  • Buffers: shared hit=826657
  • Worker 1: actual time=18.585..9705.758 rows=61937 loops=1
  • Buffers: shared hit=803181
31. 2.327 2.327 ↓ 85.4 854 3 / 3

Parallel Index Scan using products_pkey_105292 on public.products_105292 p (cost=0.42..6,593.91 rows=10 width=12) (actual time=0.012..2.327 rows=854 loops=3)

  • Output: p.id, p.amazon_seller_account_id
  • Index Cond: (p.amazon_seller_account_id = 46974)
  • Filter: (p.marketplace_id = 121029)
  • Rows Removed by Filter: 371
  • Buffers: shared hit=2219
  • Worker 0: actual time=0.011..2.781 rows=777 loops=1
  • Buffers: shared hit=897
  • Worker 1: actual time=0.009..2.237 rows=766 loops=1
  • Buffers: shared hit=570
32. 30.756 10,653.537 ↓ 1.1 85 2,563 / 3

Bitmap Heap Scan on public.product_summaries_106673 ps (cost=12,623.02..12,772.05 rows=74 width=31) (actual time=12.442..12.470 rows=85 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
  • Recheck Cond: ((ps.product_id = p.id) AND (ps.amazon_seller_account_id = 46974) AND (ps.summary_date >= '2019-11-13'::date) AND (ps.summary_date <= '2020-03-19'::date))
  • Heap Blocks: exact=8317
  • Buffers: shared hit=2699496
  • Worker 0: actual time=12.818..12.852 rows=88 loops=777
  • Buffers: shared hit=825760
  • Worker 1: actual time=12.630..12.654 rows=81 loops=766
  • Buffers: shared hit=802611
33. 45.946 10,622.781 ↓ 0.0 0 2,563 / 3

BitmapAnd (cost=12,623.02..12,623.02 rows=74 width=0) (actual time=12.434..12.434 rows=0 loops=2,563)

  • Buffers: shared hit=2686296
  • Worker 0: actual time=12.809..12.809 rows=0 loops=777
  • Buffers: shared hit=823184
  • Worker 1: actual time=12.621..12.621 rows=0 loops=766
  • Buffers: shared hit=800304
34. 408.371 408.371 ↑ 1.0 14,419 2,563 / 3

Bitmap Index Scan on index_product_summaries_on_product_id_106673 (cost=0.00..224.51 rows=14,680 width=0) (actual time=0.478..0.478 rows=14,419 loops=2,563)

  • Index Cond: (ps.product_id = p.id)
  • Buffers: shared hit=138992
  • Worker 0: actual time=0.514..0.514 rows=14855 loops=777
  • Buffers: shared hit=43324
  • Worker 1: actual time=0.464..0.464 rows=14086 loops=766
  • Buffers: shared hit=40623
35. 10,168.463 10,168.463 ↑ 2.2 229,768 2,146 / 3

Bitmap Index Scan on uniq_index_product_summaries_on_product_and_summary_date_106673 (cost=0.00..12,270.64 rows=510,406 width=0) (actual time=14.215..14.215 rows=229,768 loops=2,146)

  • Index Cond: ((ps.amazon_seller_account_id = 46974) AND (ps.summary_date >= '2019-11-13'::date) AND (ps.summary_date <= '2020-03-19'::date))
  • Buffers: shared hit=2547304
  • Worker 0: actual time=14.451..14.451 rows=229768 loops=657
  • Buffers: shared hit=779860
  • Worker 1: actual time=14.462..14.462 rows=229768 loops=640
  • Buffers: shared hit=759681
36. 0.007 711.394 ↓ 0.0 0 1

Sort (cost=0.28..0.29 rows=1 width=48) (actual time=711.393..711.394 rows=0 loops=1)

  • 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))
  • Sort Key: estimated_data_summaries_table.product_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=149796
37. 711.387 711.387 ↓ 0.0 0 1

CTE Scan on estimated_data_summaries_table (cost=0.00..0.27 rows=1 width=48) (actual time=711.387..711.387 rows=0 loops=1)

  • 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=149796
38. 29,804.527 29,804.527 ↑ 1.0 1 1

CTE Scan on summaries_view (cost=0.00..0.02 rows=1 width=192) (actual time=29,804.526..29,804.527 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=1221734
39. 0.076 0.076 ↑ 1.0 1 1

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

  • Output: other_income_drilldown_view.other_income_drilldown
  • Buffers: shared hit=45
Planning time : 1.426 ms
Execution time : 32,037.112 ms