explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jkk7

Settings
# exclusive inclusive rows x rows loops node
1. 974.917 348,745.835 ↓ 3.1 124,848 1

Sort (cost=307,812.27..307,912.27 rows=40,000 width=326) (actual time=348,735.267..348,745.835 rows=124,848 loops=1)

  • Output: ((aggr.display_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.display_asin)::character varying(10))
  • Sort Method: quicksort Memory: 14060kB
  • Buffers: shared hit=112919 read=260269
2.          

CTE aggr

3. 125.509 347,382.345 ↓ 3.1 124,848 1

Merge Full Join (cost=300,304.43..300,954.69 rows=40,000 width=256) (actual time=347,220.203..347,382.345 rows=124,848 loops=1)

  • Output: COALESCE(tr.display_asin, (sales.display_asin)::bpchar), 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))
  • Buffers: shared hit=112919 read=260269
4. 758.519 347,041.461 ↓ 3.0 120,453 1

Sort (cost=280,129.68..280,229.68 rows=40,000 width=115) (actual time=347,010.323..347,041.461 rows=120,453 loops=1)

  • Output: tr.display_asin, tr.pv, tr.dsc, tr.bb, tr.merchant_customer_id
  • Sort Key: tr.merchant_customer_id, tr.display_asin
  • Sort Method: quicksort Memory: 12483kB
  • Buffers: shared hit=78939 read=260268
5. 13.351 346,282.942 ↓ 3.0 120,453 1

Subquery Scan on tr (cost=275,472.14..277,072.14 rows=40,000 width=115) (actual time=346,105.151..346,282.942 rows=120,453 loops=1)

  • Output: tr.display_asin, tr.pv, tr.dsc, tr.bb, tr.merchant_customer_id
  • Buffers: shared hit=78939 read=260268
6. 1,677.619 346,269.591 ↓ 3.0 120,453 1

HashAggregate (cost=275,472.14..276,672.14 rows=40,000 width=115) (actual time=346,105.149..346,269.591 rows=120,453 loops=1)

  • Output: d_daily_merchant_asin_activity.merchant_customer_id, d_daily_merchant_asin_activity.display_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
  • Buffers: shared hit=78939 read=260268
7. 140.961 344,591.972 ↑ 1.3 332,023 1

Append (cost=0.00..268,067.34 rows=423,131 width=32) (actual time=58.547..344,591.972 rows=332,023 loops=1)

  • Buffers: shared hit=78939 read=260268
8. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on booker.d_daily_merchant_asin_activity (cost=0.00..0.00 rows=1 width=100) (actual time=0.002..0.002 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.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-02-28 12:00:00'::timestamp without time zone) AND (d_daily_merchant_asin_activity.metrics_date <= '2019-03-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. 5.996 5.996 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_0 on booker.ddmaa_201902_0 (cost=0.70..2.72 rows=1 width=32) (actual time=5.996..5.996 rows=0 loops=1)

  • Output: ddmaa_201902_0.merchant_customer_id, ddmaa_201902_0.display_asin, ddmaa_201902_0.page_views, ddmaa_201902_0.distinct_session_count, ddmaa_201902_0.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_0.marketplace_id = 1) AND (ddmaa_201902_0.merchant_customer_id = 142499351) AND (ddmaa_201902_0.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_0.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
10. 6.056 6.056 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_1 on booker.ddmaa_201902_1 (cost=0.70..2.72 rows=1 width=32) (actual time=6.056..6.056 rows=0 loops=1)

  • Output: ddmaa_201902_1.merchant_customer_id, ddmaa_201902_1.display_asin, ddmaa_201902_1.page_views, ddmaa_201902_1.distinct_session_count, ddmaa_201902_1.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_1.marketplace_id = 1) AND (ddmaa_201902_1.merchant_customer_id = 142499351) AND (ddmaa_201902_1.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_1.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
11. 5.331 5.331 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_2 on booker.ddmaa_201902_2 (cost=0.70..2.72 rows=1 width=32) (actual time=5.331..5.331 rows=0 loops=1)

  • Output: ddmaa_201902_2.merchant_customer_id, ddmaa_201902_2.display_asin, ddmaa_201902_2.page_views, ddmaa_201902_2.distinct_session_count, ddmaa_201902_2.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_2.marketplace_id = 1) AND (ddmaa_201902_2.merchant_customer_id = 142499351) AND (ddmaa_201902_2.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_2.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
12. 7.076 7.076 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_3 on booker.ddmaa_201902_3 (cost=0.70..2.72 rows=1 width=32) (actual time=7.076..7.076 rows=0 loops=1)

  • Output: ddmaa_201902_3.merchant_customer_id, ddmaa_201902_3.display_asin, ddmaa_201902_3.page_views, ddmaa_201902_3.distinct_session_count, ddmaa_201902_3.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_3.marketplace_id = 1) AND (ddmaa_201902_3.merchant_customer_id = 142499351) AND (ddmaa_201902_3.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_3.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
13. 5.833 5.833 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_4 on booker.ddmaa_201902_4 (cost=0.70..2.72 rows=1 width=32) (actual time=5.833..5.833 rows=0 loops=1)

  • Output: ddmaa_201902_4.merchant_customer_id, ddmaa_201902_4.display_asin, ddmaa_201902_4.page_views, ddmaa_201902_4.distinct_session_count, ddmaa_201902_4.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_4.marketplace_id = 1) AND (ddmaa_201902_4.merchant_customer_id = 142499351) AND (ddmaa_201902_4.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_4.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
14. 5.987 5.987 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_5 on booker.ddmaa_201902_5 (cost=0.70..2.72 rows=1 width=32) (actual time=5.987..5.987 rows=0 loops=1)

  • Output: ddmaa_201902_5.merchant_customer_id, ddmaa_201902_5.display_asin, ddmaa_201902_5.page_views, ddmaa_201902_5.distinct_session_count, ddmaa_201902_5.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_5.marketplace_id = 1) AND (ddmaa_201902_5.merchant_customer_id = 142499351) AND (ddmaa_201902_5.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_5.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
15. 5.475 5.475 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_6 on booker.ddmaa_201902_6 (cost=0.70..2.72 rows=1 width=32) (actual time=5.475..5.475 rows=0 loops=1)

  • Output: ddmaa_201902_6.merchant_customer_id, ddmaa_201902_6.display_asin, ddmaa_201902_6.page_views, ddmaa_201902_6.distinct_session_count, ddmaa_201902_6.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_6.marketplace_id = 1) AND (ddmaa_201902_6.merchant_customer_id = 142499351) AND (ddmaa_201902_6.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_6.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
16. 5.335 5.335 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_7 on booker.ddmaa_201902_7 (cost=0.70..2.72 rows=1 width=32) (actual time=5.335..5.335 rows=0 loops=1)

  • Output: ddmaa_201902_7.merchant_customer_id, ddmaa_201902_7.display_asin, ddmaa_201902_7.page_views, ddmaa_201902_7.distinct_session_count, ddmaa_201902_7.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_7.marketplace_id = 1) AND (ddmaa_201902_7.merchant_customer_id = 142499351) AND (ddmaa_201902_7.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_7.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
17. 6.424 6.424 ↓ 0.0 0 1

Index Scan using i_ddmaa_201902_8 on booker.ddmaa_201902_8 (cost=0.70..2.72 rows=1 width=32) (actual time=6.424..6.424 rows=0 loops=1)

  • Output: ddmaa_201902_8.merchant_customer_id, ddmaa_201902_8.display_asin, ddmaa_201902_8.page_views, ddmaa_201902_8.distinct_session_count, ddmaa_201902_8.buy_box_page_view_count
  • Index Cond: ((ddmaa_201902_8.marketplace_id = 1) AND (ddmaa_201902_8.merchant_customer_id = 142499351) AND (ddmaa_201902_8.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201902_8.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared read=5
18. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_0 on booker.ddmaa_201903_0 (cost=0.70..6,714.70 rows=9,704 width=32) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: ddmaa_201903_0.merchant_customer_id, ddmaa_201903_0.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_0.metrics_date <= '2019-03-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_201903_1 on booker.ddmaa_201903_1 (cost=0.70..6,415.61 rows=8,940 width=32) (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.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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_1.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
20. 344,397.403 344,397.403 ↑ 1.0 332,023 1

Index Scan using i_ddmaa_201903_2 on booker.ddmaa_201903_2 (cost=0.70..208,969.44 rows=335,420 width=32) (actual time=5.006..344,397.403 rows=332,023 loops=1)

  • Output: ddmaa_201903_2.merchant_customer_id, ddmaa_201903_2.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_2.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=78899 read=260223
21. 0.019 0.019 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_3 on booker.ddmaa_201903_3 (cost=0.70..8,440.17 rows=12,029 width=32) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: ddmaa_201903_3.merchant_customer_id, ddmaa_201903_3.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_3.metrics_date <= '2019-03-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_201903_4 on booker.ddmaa_201903_4 (cost=0.70..7,293.87 rows=11,262 width=32) (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.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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_4.metrics_date <= '2019-03-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_201903_5 on booker.ddmaa_201903_5 (cost=0.70..7,804.15 rows=12,228 width=32) (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.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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_5.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
24. 0.012 0.012 ↓ 0.0 0 1

Index Scan using i_ddmaa_201903_6 on booker.ddmaa_201903_6 (cost=0.70..7,273.57 rows=10,508 width=32) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: ddmaa_201903_6.merchant_customer_id, ddmaa_201903_6.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_6.metrics_date <= '2019-03-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_201903_7 on booker.ddmaa_201903_7 (cost=0.70..6,872.55 rows=10,020 width=32) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201903_7.merchant_customer_id, ddmaa_201903_7.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_7.metrics_date <= '2019-03-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_201903_8 on booker.ddmaa_201903_8 (cost=0.70..8,258.79 rows=13,010 width=32) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: ddmaa_201903_8.merchant_customer_id, ddmaa_201903_8.display_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-02-28 12:00:00'::timestamp without time zone) AND (ddmaa_201903_8.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
27. 126.616 215.375 ↓ 8.4 24,005 1

Sort (cost=20,174.75..20,181.93 rows=2,872 width=115) (actual time=209.852..215.375 rows=24,005 loops=1)

  • Output: sales.display_asin, sales.sum_ops, sales.sum_units, sales.sum_order_items, sales.merchant
  • Sort Key: sales.merchant, sales.display_asin USING <
  • Sort Method: quicksort Memory: 2644kB
  • Buffers: shared hit=33980 read=1
28. 2.544 88.759 ↓ 8.4 24,005 1

Subquery Scan on sales (cost=19,930.80..20,009.78 rows=2,872 width=115) (actual time=74.301..88.759 rows=24,005 loops=1)

  • Output: sales.display_asin, sales.sum_ops, sales.sum_units, sales.sum_order_items, sales.merchant
  • Buffers: shared hit=33980 read=1
29. 42.617 86.215 ↓ 8.4 24,005 1

HashAggregate (cost=19,930.80..19,981.06 rows=2,872 width=115) (actual time=74.301..86.215 rows=24,005 loops=1)

  • Output: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_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
  • Buffers: shared hit=33980 read=1
30. 2.920 43.598 ↓ 1.2 33,397 1

Append (cost=0.00..19,571.80 rows=28,720 width=33) (actual time=1.416..43.598 rows=33,397 loops=1)

  • Buffers: shared hit=33980 read=1
31. 0.005 0.005 ↓ 0.0 0 1

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

  • Output: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_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-02-28 12:00:00'::timestamp without time zone) AND (daily_merchant_asin_sales.metrics_date <= '2019-03-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.393 1.393 ↓ 0.0 0 1

Index Scan using i_dmas_201902_2 on booker.dmas_201902 (cost=0.57..2.59 rows=1 width=33) (actual time=1.393..1.393 rows=0 loops=1)

  • Output: dmas_201902.merchant, dmas_201902.display_asin, dmas_201902.ops, dmas_201902.units, dmas_201902.order_items
  • Index Cond: ((dmas_201902.marketplace_id = 1) AND (dmas_201902.merchant = 142499351) AND (dmas_201902.metrics_date >= '2019-02-28 12:00:00'::timestamp without time zone) AND (dmas_201902.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=3 read=1
33. 39.280 39.280 ↓ 1.2 33,397 1

Index Scan using i_dmas_201903_2 on booker.dmas_201903 (cost=0.57..19,569.21 rows=28,718 width=33) (actual time=0.018..39.280 rows=33,397 loops=1)

  • Output: dmas_201903.merchant, dmas_201903.display_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-02-28 12:00:00'::timestamp without time zone) AND (dmas_201903.metrics_date <= '2019-03-15 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=33977
34.          

CTE totals

35. 40.163 347,469.579 ↑ 1.0 1 1

Aggregate (cost=1,000.00..1,000.01 rows=1 width=64) (actual time=347,469.578..347,469.579 rows=1 loops=1)

  • Output: sum(COALESCE(aggr_1.pv, '0'::numeric)), sum(COALESCE(aggr_1.dsc, '0'::numeric))
  • Buffers: shared hit=112919 read=260269
36. 347,429.416 347,429.416 ↓ 3.1 124,848 1

CTE Scan on aggr aggr_1 (cost=0.00..800.00 rows=40,000 width=64) (actual time=347,220.206..347,429.416 rows=124,848 loops=1)

  • Output: aggr_1.display_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=112919 read=260269
37. 288.367 347,770.918 ↓ 3.1 124,848 1

Nested Loop (cost=0.00..2,800.02 rows=40,000 width=326) (actual time=347,469.667..347,770.918 rows=124,848 loops=1)

  • Output: (aggr.display_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=112919 read=260269
38. 347,469.582 347,469.582 ↑ 1.0 1 1

CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=347,469.581..347,469.582 rows=1 loops=1)

  • Output: totals.total_pv, totals.total_sessions
  • Buffers: shared hit=112919 read=260269
39. 12.969 12.969 ↓ 3.1 124,848 1

CTE Scan on aggr (cost=0.00..800.00 rows=40,000 width=256) (actual time=0.001..12.969 rows=124,848 loops=1)

  • Output: aggr.display_asin, aggr.pv, aggr.dsc, aggr.bb, aggr.sum_ops, aggr.sum_units, aggr.sum_order_items, aggr.unit_session_pct
Planning time : 10.798 ms