explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RxRp

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 1,655.494 ↑ 3.0 3 1

Sort (cost=331,880.58..331,880.60 rows=9 width=72) (actual time=1,655.494..1,655.494 rows=3 loops=1)

  • Output: materialisedview.channel_id, ch.channelname, (((sum(materialisedview.sales) / max(mt.total)) * '100'::numeric))
  • Sort Key: (((sum(materialisedview.sales) / max(mt.total)) * '100'::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8,766, temp read=902 written=904
2.          

CTE metricstoal

3. 9.127 560.077 ↑ 1.0 1 1

Aggregate (cost=209,257.11..209,257.12 rows=1 width=32) (actual time=560.077..560.077 rows=1 loops=1)

  • Output: sum(materialisedview_1.sales)
  • Buffers: shared hit=1,437
4. 550.950 550.950 ↓ 6.5 4,752 1

Index Scan using ocpppcv_planpage_idx_1_day on insights_store_web_api_qa_system_integration_default_sch.orgunit_customer_product_payer_plan_channel_day_view materialisedview_1 (cost=0.56..209,255.29 rows=726 width=12) (actual time=70.844..550.950 rows=4,752 loops=1)

  • Output: materialisedview_1.orgunit_id, materialisedview_1.customer_id, materialisedview_1.product_id, materialisedview_1.sales_force_id, materialisedview_1.channel_id, materialisedview_1.plan_id, materialisedview_1.sales, materialisedview_1.nrx, materialisedview_1.payer_mix_contribution, materialisedview_1.market_share_change, materialisedview_1.sales_growth, materialisedview_1.market_share, materialisedview_1.time_grain_id, materialisedview_1.orgunit_name, materialisedview_1.orgunit_externalid, materialisedview_1.plan_name, materialisedview_1.plan_externalid, materialisedview_1.channel_name, materialisedview_1.channel_externalid, materialisedview_1.payer_id, materialisedview_1.payer_name, materialisedview_1.payer_externalid, materialisedview_1.customer_name, materialisedview_1.is_pdrp_enabled, materialisedview_1.customer_externalid, materialisedview_1.product_name, materialisedview_1.product_externalid, materialisedview_1.start_date, materialisedview_1.end_date, materialisedview_1.day, materialisedview_1.week, materialisedview_1.month, materialisedview_1.year, materialisedview_1.quarter, materialisedview_1.time_grain_name, materialisedview_1.customer_type_id
  • Index Cond: ((materialisedview_1.sales_force_id = 1) AND (materialisedview_1.orgunit_id = 174) AND (materialisedview_1.customer_id = 3))
  • Filter: (((materialisedview_1.year = 2,019) AND (materialisedview_1.day = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((materialisedview_1.year = 2,018) AND (materialisedview_1.day = ANY ('{8,9,10,11,12}'::integer[]))))
  • Rows Removed by Filter: 284,328
  • Buffers: shared hit=1,437
5. 85.732 1,655.476 ↑ 3.0 3 1

GroupAggregate (cost=122,621.96..122,623.31 rows=9 width=72) (actual time=1,588.003..1,655.476 rows=3 loops=1)

  • Output: materialisedview.channel_id, ch.channelname, ((sum(materialisedview.sales) / max(mt.total)) * '100'::numeric)
  • Group Key: materialisedview.channel_id, ch.channelname
  • Buffers: shared hit=8,766, temp read=902 written=904
6. 321.256 1,569.744 ↓ 1,381.9 132,660 1

Sort (cost=122,621.96..122,622.20 rows=96 width=84) (actual time=1,533.075..1,569.744 rows=132,660 loops=1)

  • Output: materialisedview.channel_id, ch.channelname, materialisedview.sales, mt.total
  • Sort Key: materialisedview.channel_id, ch.channelname
  • Sort Method: external merge Disk: 7,216kB
  • Buffers: shared hit=8,766, temp read=902 written=904
7. 315.292 1,248.488 ↓ 1,381.9 132,660 1

Nested Loop (cost=1,200.09..122,618.80 rows=96 width=84) (actual time=607.880..1,248.488 rows=132,660 loops=1)

  • Output: materialisedview.channel_id, ch.channelname, materialisedview.sales, mt.total
  • Join Filter: (ch.channelid = materialisedview.channel_id)
  • Rows Removed by Join Filter: 795,960
  • Buffers: shared hit=8,766
8. 90.506 137.236 ↓ 6.4 132,660 1

Bitmap Heap Scan on insights_store_web_api_qa_system_integration_default_sch.orgunit_customer_product_payer_plan_channel_day_view materialisedview (cost=1,200.09..121,661.42 rows=20,871 width=20) (actual time=47.779..137.236 rows=132,660 loops=1)

  • Output: materialisedview.orgunit_id, materialisedview.customer_id, materialisedview.product_id, materialisedview.sales_force_id, materialisedview.channel_id, materialisedview.plan_id, materialisedview.sales, materialisedview.nrx, materialisedview.payer_mix_contribution, materialisedview.market_share_change, materialisedview.sales_growth, materialisedview.market_share, materialisedview.time_grain_id, materialisedview.orgunit_name, materialisedview.orgunit_externalid, materialisedview.plan_name, materialisedview.plan_externalid, materialisedview.channel_name, materialisedview.channel_externalid, materialisedview.payer_id, materialisedview.payer_name, materialisedview.payer_externalid, materialisedview.customer_name, materialisedview.is_pdrp_enabled, materialisedview.customer_externalid, materialisedview.product_name, materialisedview.product_externalid, materialisedview.start_date, materialisedview.end_date, materialisedview.day, materialisedview.week, materialisedview.month, materialisedview.year, materialisedview.quarter, materialisedview.time_grain_name, materialisedview.customer_type_id
  • Recheck Cond: (((materialisedview.orgunit_id = 174) AND (materialisedview.customer_id = 3) AND (materialisedview.year = 2,019) AND (materialisedview.month = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((materialisedview.orgunit_id = 174) AND (materialisedview.customer_id = 3) AND (materialisedview.year = 2,018) AND (materialisedview.month = ANY ('{8,9,10,11,12}'::integer[]))))
  • Filter: (materialisedview.sales_force_id = 1)
  • Heap Blocks: exact=6,615
  • Buffers: shared hit=7,328
9. 0.004 46.730 ↓ 0.0 0 1

BitmapOr (cost=1,200.09..1,200.09 rows=33,525 width=0) (actual time=46.730..46.730 rows=0 loops=1)

  • Buffers: shared hit=713
10. 24.913 24.913 ↓ 5.3 72,072 1

Bitmap Index Scan on ocpppcv_orgunitid_customerid_year_month_idx_day (cost=0.00..488.70 rows=13,648 width=0) (actual time=24.913..24.913 rows=72,072 loops=1)

  • Index Cond: ((materialisedview.orgunit_id = 174) AND (materialisedview.customer_id = 3) AND (materialisedview.year = 2,019) AND (materialisedview.month = ANY ('{1,2,3,4,5,6,7}'::integer[])))
  • Buffers: shared hit=390
11. 21.813 21.813 ↓ 3.0 60,588 1

Bitmap Index Scan on ocpppcv_orgunitid_customerid_year_month_idx_day (cost=0.00..700.95 rows=19,877 width=0) (actual time=21.813..21.813 rows=60,588 loops=1)

  • Index Cond: ((materialisedview.orgunit_id = 174) AND (materialisedview.customer_id = 3) AND (materialisedview.year = 2,018) AND (materialisedview.month = ANY ('{8,9,10,11,12}'::integer[])))
  • Buffers: shared hit=323
12. 235.868 795.960 ↓ 2.3 7 132,660

Materialize (cost=0.00..18.19 rows=3 width=72) (actual time=0.004..0.006 rows=7 loops=132,660)

  • Output: mt.total, ch.channelname, ch.channelid
  • Buffers: shared hit=1,438
13. 0.004 560.092 ↓ 2.3 7 1

Nested Loop (cost=0.00..18.18 rows=3 width=72) (actual time=560.088..560.092 rows=7 loops=1)

  • Output: mt.total, ch.channelname, ch.channelid
  • Buffers: shared hit=1,438
14. 560.081 560.081 ↑ 1.0 1 1

CTE Scan on metricstoal mt (cost=0.00..0.02 rows=1 width=32) (actual time=560.080..560.081 rows=1 loops=1)

  • Output: mt.total
  • Buffers: shared hit=1,437
15. 0.007 0.007 ↓ 2.3 7 1

Seq Scan on insights_store_web_api_qa_system_integration_default_sch.channel ch (cost=0.00..18.12 rows=3 width=40) (actual time=0.005..0.007 rows=7 loops=1)

  • Output: ch.channelid, ch.channelname, ch.externalid, ch.isdeleted, ch.createddate, ch.updateddate
  • Filter: (ch.isdeleted = 0)
  • Buffers: shared hit=1