explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ubhw

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 439.227 ↑ 3.0 3 1

Sort (cost=244,337.07..244,337.09 rows=9 width=104) (actual time=439.227..439.227 rows=3 loops=1)

  • Sort Key: (((sum(materialisedview.sales) / mt.total) * '100'::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
2.          

CTE metricstoal

3. 18.482 51.752 ↑ 1.0 1 1

Aggregate (cost=121,713.60..121,713.61 rows=1 width=32) (actual time=51.752..51.752 rows=1 loops=1)

4. 26.036 33.270 ↓ 6.4 132,660 1

Bitmap Heap Scan on orgunit_customer_product_payer_plan_channel_day_view materialisedview_1 (cost=1,200.09..121,661.42 rows=20,871 width=12) (actual time=8.037..33.270 rows=132,660 loops=1)

  • Recheck Cond: (((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (month = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (month = ANY ('{8,9,10,11,12}'::integer[]))))
  • Filter: (sales_force_id = 1)
  • Heap Blocks: exact=6,615
5. 0.001 7.234 ↓ 0.0 0 1

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

6. 4.126 4.126 ↓ 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=4.126..4.126 rows=72,072 loops=1)

  • Index Cond: ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (month = ANY ('{1,2,3,4,5,6,7}'::integer[])))
7. 3.107 3.107 ↓ 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=3.107..3.107 rows=60,588 loops=1)

  • Index Cond: ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (month = ANY ('{8,9,10,11,12}'::integer[])))
8. 37.139 439.212 ↑ 3.0 3 1

GroupAggregate (cost=122,621.96..122,623.31 rows=9 width=104) (actual time=393.838..439.212 rows=3 loops=1)

  • Group Key: materialisedview.channel_id, mt.total, ch.channelname
9. 175.600 402.073 ↓ 1,381.9 132,660 1

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

  • Sort Key: materialisedview.channel_id, mt.total, ch.channelname
  • Sort Method: external merge Disk: 7,216kB
10. 57.636 226.473 ↓ 1,381.9 132,660 1

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

  • Join Filter: (ch.channelid = materialisedview.channel_id)
  • Rows Removed by Join Filter: 795,960
11. 28.812 36.177 ↓ 6.4 132,660 1

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

  • Recheck Cond: (((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (month = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (month = ANY ('{8,9,10,11,12}'::integer[]))))
  • Filter: (sales_force_id = 1)
  • Heap Blocks: exact=6,615
12. 0.000 7.365 ↓ 0.0 0 1

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

13. 4.228 4.228 ↓ 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=4.228..4.228 rows=72,072 loops=1)

  • Index Cond: ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (month = ANY ('{1,2,3,4,5,6,7}'::integer[])))
14. 3.137 3.137 ↓ 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=3.137..3.137 rows=60,588 loops=1)

  • Index Cond: ((orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (month = ANY ('{8,9,10,11,12}'::integer[])))
15. 80.894 132.660 ↓ 2.3 7 132,660

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

16. 0.004 51.766 ↓ 2.3 7 1

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

17. 51.756 51.756 ↑ 1.0 1 1

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

18. 0.006 0.006 ↓ 2.3 7 1

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

  • Filter: (isdeleted = 0)