explain.depesz.com

PostgreSQL's explain analyze made readable

Result: crnn

Settings
# exclusive inclusive rows x rows loops node
1. 201.894 339,605.210 ↓ 5.1 25,679 1

Sort (cost=282,113.08..282,125.69 rows=5,047 width=880) (actual time=339,603.042..339,605.210 rows=25,679 loops=1)

  • Output: ((aggr.display_asin)::character varying(10)), ((aggr.asin)::character varying(10)), aggr.sku, (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: 4380kB
  • Buffers: shared hit=165349 read=264149
2.          

CTE aggr

3. 110.411 339,315.307 ↓ 5.1 25,679 1

Merge Left Join (cost=280,846.41..281,297.92 rows=5,047 width=314) (actual time=339,165.633..339,315.307 rows=25,679 loops=1)

  • Output: COALESCE(tr.display_asin, (sales.display_asin)::bpchar), COALESCE(tr.asin, sales.asin), sales.sku, 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 * (sales.sum_units / tr.dsc)) END
  • Merge Cond: ((sales.merchant = tr.merchant_customer_id) AND ((sales.display_asin)::bpchar = tr.display_asin) AND (sales.asin = tr.asin))
  • Buffers: shared hit=165343 read=264149
4. 133.894 607.677 ↓ 5.1 25,679 1

Sort (cost=36,171.24..36,183.86 rows=5,047 width=140) (actual time=598.369..607.677 rows=25,679 loops=1)

  • Output: sales.display_asin, sales.asin, sales.sku, 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: 4380kB
  • Buffers: shared hit=33714 read=1062
5. 2.750 473.783 ↓ 5.1 25,679 1

Subquery Scan on sales (cost=35,722.03..35,860.82 rows=5,047 width=140) (actual time=455.973..473.783 rows=25,679 loops=1)

  • Output: sales.display_asin, sales.asin, sales.sku, sales.sum_ops, sales.sum_units, sales.sum_order_items, sales.merchant
  • Buffers: shared hit=33714 read=1062
6. 55.118 471.033 ↓ 5.1 25,679 1

HashAggregate (cost=35,722.03..35,810.35 rows=5,047 width=140) (actual time=455.973..471.033 rows=25,679 loops=1)

  • Output: daily_merchant_asin_sales.merchant, daily_merchant_asin_sales.display_asin, daily_merchant_asin_sales.asin, daily_merchant_asin_sales.sku, 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, daily_merchant_asin_sales.sku
  • Buffers: shared hit=33714 read=1062
7. 3.165 415.915 ↑ 1.5 32,741 1

Append (cost=0.00..34,838.77 rows=50,472 width=58) (actual time=1.330..415.915 rows=32,741 loops=1)

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

Seq Scan on booker.daily_merchant_asin_sales (cost=0.00..0.00 rows=1 width=658) (actual time=0.001..0.001 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.sku, daily_merchant_asin_sales.ops, daily_merchant_asin_sales.units, daily_merchant_asin_sales.order_items
  • Filter: ((daily_merchant_asin_sales.metrics_date >= '2019-04-14 12:00:00'::timestamp without time zone) AND (daily_merchant_asin_sales.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone) AND (daily_merchant_asin_sales.units > '0'::numeric) AND (daily_merchant_asin_sales.marketplace_id = 1) AND (daily_merchant_asin_sales.merchant = 142499351))
9. 412.749 412.749 ↑ 1.5 32,741 1

Index Scan using i_dmas_201904_2 on booker.dmas_201904 (cost=0.57..34,838.77 rows=50,471 width=58) (actual time=1.329..412.749 rows=32,741 loops=1)

  • Output: dmas_201904.merchant, dmas_201904.display_asin, dmas_201904.asin, dmas_201904.sku, 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-04-14 12:00:00'::timestamp without time zone) AND (dmas_201904.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Filter: (dmas_201904.units > '0'::numeric)
  • Rows Removed by Filter: 669
  • Buffers: shared hit=33714 read=1062
10. 1,285.758 338,597.219 ↓ 5.7 206,887 1

Sort (cost=244,675.17..244,765.91 rows=36,296 width=126) (actual time=338,567.222..338,597.219 rows=206,887 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: 22672kB
  • Buffers: shared hit=131629 read=263087
11. 21.379 337,311.461 ↓ 5.7 206,872 1

Subquery Scan on tr (cost=240,474.35..241,926.19 rows=36,296 width=126) (actual time=336,989.553..337,311.461 rows=206,872 loops=1)

  • Output: tr.display_asin, tr.asin, tr.pv, tr.dsc, tr.bb, tr.merchant_customer_id
  • Buffers: shared hit=131629 read=263087
12. 1,914.302 337,290.082 ↓ 5.7 206,872 1

HashAggregate (cost=240,474.35..241,563.23 rows=36,296 width=126) (actual time=336,989.552..337,290.082 rows=206,872 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=131629 read=263087
13. 131.068 335,375.780 ↑ 1.0 360,099 1

Append (cost=0.00..233,215.13 rows=362,961 width=43) (actual time=51.531..335,375.780 rows=360,099 loops=1)

  • Buffers: shared hit=131629 read=263087
14. 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=144) (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.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-04-14 12:00:00'::timestamp without time zone) AND (d_daily_merchant_asin_activity.metrics_date <= '2019-04-30 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))
15. 0.028 0.028 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_0 on booker.ddmaa_201904_0 (cost=0.70..7,503.22 rows=10,758 width=43) (actual time=0.028..0.028 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_0.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
16. 0.010 0.010 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_1 on booker.ddmaa_201904_1 (cost=0.70..6,754.74 rows=9,205 width=43) (actual time=0.010..0.010 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_1.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
17. 335,244.585 335,244.585 ↓ 1.3 360,099 1

Index Scan using i_ddmaa_201904_2 on booker.ddmaa_201904_2 (cost=0.70..173,567.38 rows=276,101 width=43) (actual time=51.490..335,244.585 rows=360,099 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_2.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=131589 read=263087
18. 0.023 0.023 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_3 on booker.ddmaa_201904_3 (cost=0.70..8,185.57 rows=11,096 width=43) (actual time=0.023..0.023 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_3.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
19. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_4 on booker.ddmaa_201904_4 (cost=0.70..6,997.54 rows=10,941 width=43) (actual time=0.013..0.013 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_4.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
20. 0.012 0.012 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_5 on booker.ddmaa_201904_5 (cost=0.70..7,498.96 rows=11,323 width=43) (actual time=0.012..0.012 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_5.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
21. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_6 on booker.ddmaa_201904_6 (cost=0.70..8,122.77 rows=11,586 width=43) (actual time=0.013..0.013 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_6.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
22. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_7 on booker.ddmaa_201904_7 (cost=0.70..6,794.77 rows=10,046 width=43) (actual time=0.013..0.013 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_7.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
23. 0.013 0.013 ↓ 0.0 0 1

Index Scan using i_ddmaa_201904_8 on booker.ddmaa_201904_8 (cost=0.70..7,790.19 rows=11,904 width=43) (actual time=0.013..0.013 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-04-14 12:00:00'::timestamp without time zone) AND (ddmaa_201904_8.metrics_date <= '2019-04-30 12:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
24.          

CTE totals

25. 8.473 339,337.358 ↑ 1.0 1 1

Aggregate (cost=126.18..126.19 rows=1 width=64) (actual time=339,337.357..339,337.358 rows=1 loops=1)

  • Output: sum(COALESCE(aggr_1.pv, '0'::numeric)), sum(COALESCE(aggr_1.dsc, '0'::numeric))
  • Buffers: shared hit=165343 read=264149
26. 339,328.885 339,328.885 ↓ 5.1 25,679 1

CTE Scan on aggr aggr_1 (cost=0.00..100.94 rows=5,047 width=64) (actual time=339,165.636..339,328.885 rows=25,679 loops=1)

  • Output: aggr_1.display_asin, aggr_1.asin, aggr_1.sku, 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=165343 read=264149
27. 62.991 339,403.316 ↓ 5.1 25,679 1

Nested Loop (cost=0.00..378.55 rows=5,047 width=880) (actual time=339,337.404..339,403.316 rows=25,679 loops=1)

  • Output: (aggr.display_asin)::character varying(10), (aggr.asin)::character varying(10), aggr.sku, 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=165343 read=264149
28. 339,337.360 339,337.360 ↑ 1.0 1 1

CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=339,337.359..339,337.360 rows=1 loops=1)

  • Output: totals.total_pv, totals.total_sessions
  • Buffers: shared hit=165343 read=264149
29. 2.965 2.965 ↓ 5.1 25,679 1

CTE Scan on aggr (cost=0.00..100.94 rows=5,047 width=816) (actual time=0.001..2.965 rows=25,679 loops=1)

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