explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OZn1

Settings
# exclusive inclusive rows x rows loops node
1. 1,944.327 5,435.751 ↓ 5.6 204,721 1

Sort (cost=289,677.46..289,768.81 rows=36,540 width=364) (actual time=5,413.193..5,435.751 rows=204,721 loops=1)

  • Output: ((aggr.display_asin)::character varying(10)), ((aggr.asin)::character varying(10)), (round(COALESCE(aggr.dsc, '0'::numeric), 0)), (CASE WHEN (totals.total_sessions = '0'::numeric) THEN '0'::numeric ELSE round(('100'::numeric * (COALESCE(aggr.dsc, '0'::numeric) / totals.total_sessions)), 2) END), (round(COALESCE(aggr.pv, '0'::numeric), 0)), (CASE WHEN (totals.total_pv = '0'::numeric) THEN '0'::numeric ELSE round(('100'::numeric * (COALESCE(aggr.pv, '0'::numeric) / totals.total_pv)), 2) END), (round(COALESCE(aggr.bb, '0'::numeric), 2)), (round(COALESCE(aggr.sum_units, '0'::numeric), 0)), (round(aggr.unit_session_pct, 2)), (COALESCE(aggr.sum_ops, '0'::numeric)), (round(COALESCE(aggr.sum_order_items, '0'::numeric), 0))
  • Sort Key: (round(COALESCE(aggr.sum_order_items, '0'::numeric), 0)), ((aggr.asin)::character varying(10))
  • Sort Method: quicksort Memory: 34933kB
  • Buffers: shared hit=393980 read=2
2.          

CTE aggr

3. 177.386 2,820.358 ↓ 5.6 204,721 1

Merge Full Join (cost=282,562.31..283,254.20 rows=36,540 width=300) (actual time=2,593.262..2,820.358 rows=204,721 loops=1)

  • Output: COALESCE(tr.display_asin, (sales.display_asin)::bpchar), COALESCE(tr.asin, sales.asin), tr.pv, tr.dsc, tr.bb, sales.sum_ops, sales.sum_units, sales.sum_order_items, CASE WHEN (COALESCE(tr.dsc, '0'::numeric) = '0'::numeric) THEN '0'::numeric ELSE ('100'::numeric * (COALESCE(sales.sum_units, '0'::numeric) / tr.dsc)) END
  • Merge Cond: ((tr.merchant_customer_id = sales.merchant) AND (tr.display_asin = (sales.display_asin)::bpchar) AND (tr.asin = sales.asin))
  • Buffers: shared hit=393980 read=2
4. 1,265.076 2,415.553 ↓ 5.5 200,014 1

Sort (cost=246,245.71..246,337.06 rows=36,540 width=126) (actual time=2,370.920..2,415.553 rows=200,014 loops=1)

  • Output: tr.display_asin, tr.asin, tr.pv, tr.dsc, tr.bb, tr.merchant_customer_id
  • Sort Key: tr.merchant_customer_id, tr.display_asin, tr.asin
  • Sort Method: quicksort Memory: 22123kB
  • Buffers: shared hit=360084 read=1
5. 20.803 1,150.477 ↓ 5.5 200,014 1

Subquery Scan on tr (cost=242,014.90..243,476.50 rows=36,540 width=126) (actual time=884.965..1,150.477 rows=200,014 loops=1)

  • Output: tr.display_asin, tr.asin, tr.pv, tr.dsc, tr.bb, tr.merchant_customer_id
  • Buffers: shared hit=360084 read=1
6. 672.820 1,129.674 ↓ 5.5 200,014 1

HashAggregate (cost=242,014.90..243,111.10 rows=36,540 width=126) (actual time=884.964..1,129.674 rows=200,014 loops=1)

  • Output: d_daily_merchant_asin_activity.merchant_customer_id, d_daily_merchant_asin_activity.display_asin, d_daily_merchant_asin_activity.asin, sum(d_daily_merchant_asin_activity.page_views), sum(d_daily_merchant_asin_activity.distinct_session_count), CASE WHEN (sum(d_daily_merchant_asin_activity.page_views) = '0'::numeric) THEN '0'::numeric ELSE ('100'::numeric * (sum(d_daily_merchant_asin_activity.buy_box_page_view_count) / sum(d_daily_merchant_asin_activity.page_views))) END
  • Group Key: d_daily_merchant_asin_activity.merchant_customer_id, d_daily_merchant_asin_activity.display_asin, d_daily_merchant_asin_activity.asin
  • Buffers: shared hit=360084 read=1
7. 29.524 456.854 ↑ 1.0 354,593 1

Append (cost=0.00..234,707.00 rows=365,395 width=43) (actual time=1.390..456.854 rows=354,593 loops=1)

  • Buffers: shared hit=360084 read=1
8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on booker.d_daily_merchant_asin_activity (cost=0.00..0.00 rows=1 width=144) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: d_daily_merchant_asin_activity.merchant_customer_id, d_daily_merchant_asin_activity.display_asin, d_daily_merchant_asin_activity.asin, d_daily_merchant_asin_activity.page_views, d_daily_merchant_asin_activity.distinct_session_count, d_daily_merchant_asin_activity.buy_box_page_view_count
  • Filter: ((d_daily_merchant_asin_activity.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (d_daily_merchant_asin_activity.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone) AND (d_daily_merchant_asin_activity.merchant_customer_id = 142499351) AND (d_daily_merchant_asin_activity.marketplace_id = 1))
9. 0.015 0.015 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_0 on booker.ddmaa_201903_0 (cost=0.70..2.72 rows=1 width=43) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: ddmaa_201903_0.merchant_customer_id, ddmaa_201903_0.display_asin, ddmaa_201903_0.asin, ddmaa_201903_0.page_views, ddmaa_201903_0.distinct_session_count, ddmaa_201903_0.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_0.marketplace_id = 1) AND (ddmaa_201903_0.merchant_customer_id = 142499351) AND (ddmaa_201903_0.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_0.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
10. 0.009 0.009 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_1 on booker.ddmaa_201903_1 (cost=0.70..2.72 rows=1 width=43) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: ddmaa_201903_1.merchant_customer_id, ddmaa_201903_1.display_asin, ddmaa_201903_1.asin, ddmaa_201903_1.page_views, ddmaa_201903_1.distinct_session_count, ddmaa_201903_1.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_1.marketplace_id = 1) AND (ddmaa_201903_1.merchant_customer_id = 142499351) AND (ddmaa_201903_1.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_1.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
11. 1.269 1.269 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_2 on booker.ddmaa_201903_2 (cost=0.70..2.72 rows=1 width=43) (actual time=1.269..1.269 rows=0 loops=1)

  • Output: ddmaa_201903_2.merchant_customer_id, ddmaa_201903_2.display_asin, ddmaa_201903_2.asin, ddmaa_201903_2.page_views, ddmaa_201903_2.distinct_session_count, ddmaa_201903_2.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_2.marketplace_id = 1) AND (ddmaa_201903_2.merchant_customer_id = 142499351) AND (ddmaa_201903_2.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_2.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=4 read=1
12. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_3 on booker.ddmaa_201903_3 (cost=0.70..2.72 rows=1 width=43) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: ddmaa_201903_3.merchant_customer_id, ddmaa_201903_3.display_asin, ddmaa_201903_3.asin, ddmaa_201903_3.page_views, ddmaa_201903_3.distinct_session_count, ddmaa_201903_3.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_3.marketplace_id = 1) AND (ddmaa_201903_3.merchant_customer_id = 142499351) AND (ddmaa_201903_3.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_3.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
13. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_4 on booker.ddmaa_201903_4 (cost=0.70..2.72 rows=1 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201903_4.merchant_customer_id, ddmaa_201903_4.display_asin, ddmaa_201903_4.asin, ddmaa_201903_4.page_views, ddmaa_201903_4.distinct_session_count, ddmaa_201903_4.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_4.marketplace_id = 1) AND (ddmaa_201903_4.merchant_customer_id = 142499351) AND (ddmaa_201903_4.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_4.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
14. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_5 on booker.ddmaa_201903_5 (cost=0.70..2.72 rows=1 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201903_5.merchant_customer_id, ddmaa_201903_5.display_asin, ddmaa_201903_5.asin, ddmaa_201903_5.page_views, ddmaa_201903_5.distinct_session_count, ddmaa_201903_5.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_5.marketplace_id = 1) AND (ddmaa_201903_5.merchant_customer_id = 142499351) AND (ddmaa_201903_5.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_5.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
15. 0.009 0.009 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_6 on booker.ddmaa_201903_6 (cost=0.70..2.72 rows=1 width=43) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: ddmaa_201903_6.merchant_customer_id, ddmaa_201903_6.display_asin, ddmaa_201903_6.asin, ddmaa_201903_6.page_views, ddmaa_201903_6.distinct_session_count, ddmaa_201903_6.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_6.marketplace_id = 1) AND (ddmaa_201903_6.merchant_customer_id = 142499351) AND (ddmaa_201903_6.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_6.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
16. 0.008 0.008 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_7 on booker.ddmaa_201903_7 (cost=0.70..2.72 rows=1 width=43) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: ddmaa_201903_7.merchant_customer_id, ddmaa_201903_7.display_asin, ddmaa_201903_7.asin, ddmaa_201903_7.page_views, ddmaa_201903_7.distinct_session_count, ddmaa_201903_7.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_7.marketplace_id = 1) AND (ddmaa_201903_7.merchant_customer_id = 142499351) AND (ddmaa_201903_7.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_7.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
17. 0.009 0.009 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_8 on booker.ddmaa_201903_8 (cost=0.70..2.72 rows=1 width=43) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: ddmaa_201903_8.merchant_customer_id, ddmaa_201903_8.display_asin, ddmaa_201903_8.asin, ddmaa_201903_8.page_views, ddmaa_201903_8.distinct_session_count, ddmaa_201903_8.buy_box_page_view_count
  • Index Cond: ((ddmaa_201903_8.marketplace_id = 1) AND (ddmaa_201903_8.merchant_customer_id = 142499351) AND (ddmaa_201903_8.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201903_8.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
18. 0.011 0.011 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_0 on booker.ddmaa_201904_0 (cost=0.70..7,125.98 rows=10,217 width=43) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: ddmaa_201904_0.merchant_customer_id, ddmaa_201904_0.display_asin, ddmaa_201904_0.asin, ddmaa_201904_0.page_views, ddmaa_201904_0.distinct_session_count, ddmaa_201904_0.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_0.marketplace_id = 1) AND (ddmaa_201904_0.merchant_customer_id = 142499351) AND (ddmaa_201904_0.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_0.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
19. 0.009 0.009 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_1 on booker.ddmaa_201904_1 (cost=0.70..6,734.28 rows=9,177 width=43) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: ddmaa_201904_1.merchant_customer_id, ddmaa_201904_1.display_asin, ddmaa_201904_1.asin, ddmaa_201904_1.page_views, ddmaa_201904_1.distinct_session_count, ddmaa_201904_1.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_1.marketplace_id = 1) AND (ddmaa_201904_1.merchant_customer_id = 142499351) AND (ddmaa_201904_1.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_1.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
20. 425.889 425.889 ↓ 1.3 354,593 1

Index Scan using i_ddmaa_201904_2 on booker.ddmaa_201904_2 (cost=0.70..174,855.53 rows=278,188 width=43) (actual time=0.016..425.889 rows=354,593 loops=1)

  • Output: ddmaa_201904_2.merchant_customer_id, ddmaa_201904_2.display_asin, ddmaa_201904_2.asin, ddmaa_201904_2.page_views, ddmaa_201904_2.distinct_session_count, ddmaa_201904_2.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_2.marketplace_id = 1) AND (ddmaa_201904_2.merchant_customer_id = 142499351) AND (ddmaa_201904_2.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_2.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=360000
21. 0.018 0.018 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_3 on booker.ddmaa_201904_3 (cost=0.70..8,177.61 rows=11,085 width=43) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: ddmaa_201904_3.merchant_customer_id, ddmaa_201904_3.display_asin, ddmaa_201904_3.asin, ddmaa_201904_3.page_views, ddmaa_201904_3.distinct_session_count, ddmaa_201904_3.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_3.marketplace_id = 1) AND (ddmaa_201904_3.merchant_customer_id = 142499351) AND (ddmaa_201904_3.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_3.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
22. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_4 on booker.ddmaa_201904_4 (cost=0.70..7,158.49 rows=11,193 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201904_4.merchant_customer_id, ddmaa_201904_4.display_asin, ddmaa_201904_4.asin, ddmaa_201904_4.page_views, ddmaa_201904_4.distinct_session_count, ddmaa_201904_4.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_4.marketplace_id = 1) AND (ddmaa_201904_4.merchant_customer_id = 142499351) AND (ddmaa_201904_4.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_4.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
23. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_5 on booker.ddmaa_201904_5 (cost=0.70..7,604.67 rows=11,482 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201904_5.merchant_customer_id, ddmaa_201904_5.display_asin, ddmaa_201904_5.asin, ddmaa_201904_5.page_views, ddmaa_201904_5.distinct_session_count, ddmaa_201904_5.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_5.marketplace_id = 1) AND (ddmaa_201904_5.merchant_customer_id = 142499351) AND (ddmaa_201904_5.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_5.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
24. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_6 on booker.ddmaa_201904_6 (cost=0.70..7,931.90 rows=11,313 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201904_6.merchant_customer_id, ddmaa_201904_6.display_asin, ddmaa_201904_6.asin, ddmaa_201904_6.page_views, ddmaa_201904_6.distinct_session_count, ddmaa_201904_6.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_6.marketplace_id = 1) AND (ddmaa_201904_6.merchant_customer_id = 142499351) AND (ddmaa_201904_6.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_6.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
25. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_7 on booker.ddmaa_201904_7 (cost=0.70..6,782.50 rows=10,028 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201904_7.merchant_customer_id, ddmaa_201904_7.display_asin, ddmaa_201904_7.asin, ddmaa_201904_7.page_views, ddmaa_201904_7.distinct_session_count, ddmaa_201904_7.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_7.marketplace_id = 1) AND (ddmaa_201904_7.merchant_customer_id = 142499351) AND (ddmaa_201904_7.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_7.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
26. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_8 on booker.ddmaa_201904_8 (cost=0.70..8,311.53 rows=12,702 width=43) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201904_8.merchant_customer_id, ddmaa_201904_8.display_asin, ddmaa_201904_8.asin, ddmaa_201904_8.page_views, ddmaa_201904_8.distinct_session_count, ddmaa_201904_8.buy_box_page_view_count
  • Index Cond: ((ddmaa_201904_8.marketplace_id = 1) AND (ddmaa_201904_8.merchant_customer_id = 142499351) AND (ddmaa_201904_8.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (ddmaa_201904_8.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
27. 134.236 227.419 ↓ 5.1 26,433 1

Sort (cost=36,316.60..36,329.65 rows=5,220 width=126) (actual time=222.319..227.419 rows=26,433 loops=1)

  • Output: sales.display_asin, sales.asin, sales.sum_ops, sales.sum_units, sales.sum_order_items, sales.merchant
  • Sort Key: sales.merchant, sales.display_asin USING <, sales.asin
  • Sort Method: quicksort Memory: 2834kB
  • Buffers: shared hit=33896 read=1
28. 2.845 93.183 ↓ 5.1 26,433 1

Subquery Scan on sales (cost=35,850.72..35,994.27 rows=5,220 width=126) (actual time=77.643..93.183 rows=26,433 loops=1)

  • Output: sales.display_asin, sales.asin, sales.sum_ops, sales.sum_units, sales.sum_order_items, sales.merchant
  • Buffers: shared hit=33896 read=1
29. 45.050 90.338 ↓ 5.1 26,433 1

HashAggregate (cost=35,850.72..35,942.07 rows=5,220 width=126) (actual time=77.642..90.338 rows=26,433 loops=1)

  • Output: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_asin, daily_merchant_asin_sales.asin, sum(daily_merchant_asin_sales.ops), sum(daily_merchant_asin_sales.units), sum(daily_merchant_asin_sales.order_items)
  • Group Key: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_asin, daily_merchant_asin_sales.asin
  • Buffers: shared hit=33896 read=1
30. 3.283 45.288 ↑ 1.6 33,357 1

Append (cost=0.00..35,067.72 rows=52,200 width=44) (actual time=1.391..45.288 rows=33,357 loops=1)

  • Buffers: shared hit=33896 read=1
31. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on booker.daily_merchant_asin_sales (cost=0.00..0.00 rows=1 width=142) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_asin, daily_merchant_asin_sales.asin, daily_merchant_asin_sales.ops, daily_merchant_asin_sales.units, daily_merchant_asin_sales.order_items
  • Filter: ((daily_merchant_asin_sales.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (daily_merchant_asin_sales.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone) AND (daily_merchant_asin_sales.marketplace_id = 1) AND (daily_merchant_asin_sales.merchant = 142499351))
32. 1.363 1.363 ↓ 0.0 0 1

Index Scan using i_dmas_201903_2 on booker.dmas_201903 (cost=0.57..2.59 rows=1 width=44) (actual time=1.363..1.363 rows=0 loops=1)

  • Output: dmas_201903.merchant, dmas_201903.display_asin, dmas_201903.asin, dmas_201903.ops, dmas_201903.units, dmas_201903.order_items
  • Index Cond: ((dmas_201903.marketplace_id = 1) AND (dmas_201903.merchant = 142499351) AND (dmas_201903.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (dmas_201903.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=3 read=1
33. 40.639 40.639 ↑ 1.6 33,357 1

Index Scan using i_dmas_201904_2 on booker.dmas_201904 (cost=0.57..35,065.13 rows=52,198 width=44) (actual time=0.021..40.639 rows=33,357 loops=1)

  • Output: dmas_201904.merchant, dmas_201904.display_asin, dmas_201904.asin, dmas_201904.ops, dmas_201904.units, dmas_201904.order_items
  • Index Cond: ((dmas_201904.marketplace_id = 1) AND (dmas_201904.merchant = 142499351) AND (dmas_201904.metrics_date >= '2019-03-31 12:00:00'::timestamp without time zone) AND (dmas_201904.metrics_date <= '2019-04-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=33893
34.          

CTE totals

35. 63.486 2,968.202 ↑ 1.0 1 1

Aggregate (cost=913.51..913.52 rows=1 width=64) (actual time=2,968.202..2,968.202 rows=1 loops=1)

  • Output: sum(COALESCE(aggr_1.pv, '0'::numeric)), sum(COALESCE(aggr_1.dsc, '0'::numeric))
  • Buffers: shared hit=393980 read=2
36. 2,904.716 2,904.716 ↓ 5.6 204,721 1

CTE Scan on aggr aggr_1 (cost=0.00..730.80 rows=36,540 width=64) (actual time=2,593.265..2,904.716 rows=204,721 loops=1)

  • Output: aggr_1.display_asin, aggr_1.asin, aggr_1.pv, aggr_1.dsc, aggr_1.bb, aggr_1.sum_ops, aggr_1.sum_units, aggr_1.sum_order_items, aggr_1.unit_session_pct
  • Buffers: shared hit=393980 read=2
37. 502.275 3,491.424 ↓ 5.6 204,721 1

Nested Loop (cost=0.00..2,740.52 rows=36,540 width=364) (actual time=2,968.241..3,491.424 rows=204,721 loops=1)

  • Output: (aggr.display_asin)::character varying(10), (aggr.asin)::character varying(10), round(COALESCE(aggr.dsc, '0'::numeric), 0), CASE WHEN (totals.total_sessions = '0'::numeric) THEN '0'::numeric ELSE round(('100'::numeric * (COALESCE(aggr.dsc, '0'::numeric) / totals.total_sessions)), 2) END, round(COALESCE(aggr.pv, '0'::numeric), 0), CASE WHEN (totals.total_pv = '0'::numeric) THEN '0'::numeric ELSE round(('100'::numeric * (COALESCE(aggr.pv, '0'::numeric) / totals.total_pv)), 2) END, round(COALESCE(aggr.bb, '0'::numeric), 2), round(COALESCE(aggr.sum_units, '0'::numeric), 0), round(aggr.unit_session_pct, 2), COALESCE(aggr.sum_ops, '0'::numeric), round(COALESCE(aggr.sum_order_items, '0'::numeric), 0)
  • Buffers: shared hit=393980 read=2
38. 2,968.206 2,968.206 ↑ 1.0 1 1

CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=2,968.205..2,968.206 rows=1 loops=1)

  • Output: totals.total_pv, totals.total_sessions
  • Buffers: shared hit=393980 read=2
39. 20.943 20.943 ↓ 5.6 204,721 1

CTE Scan on aggr (cost=0.00..730.80 rows=36,540 width=300) (actual time=0.001..20.943 rows=204,721 loops=1)

  • Output: aggr.display_asin, aggr.asin, aggr.pv, aggr.dsc, aggr.bb, aggr.sum_ops, aggr.sum_units, aggr.sum_order_items, aggr.unit_session_pct