explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qyBi

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 433.603 ↑ 3.0 3 1

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

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

CTE metricstoal

3. 17.208 49.887 ↑ 1.0 1 1

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

4. 25.512 32.679 ↓ 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=7.969..32.679 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.000 7.167 ↓ 0.0 0 1

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

6. 4.087 4.087 ↓ 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.087..4.087 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.080 3.080 ↓ 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.079..3.080 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. 36.922 433.592 ↑ 3.0 3 1

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

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

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

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

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

  • Join Filter: (ch.channelid = materialisedview.channel_id)
  • Rows Removed by Join Filter: 795,960
11. 28.615 35.970 ↓ 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.163..35.970 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.001 7.355 ↓ 0.0 0 1

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

13. 4.209 4.209 ↓ 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.209..4.209 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.145 3.145 ↓ 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.145..3.145 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. 82.759 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 49.901 ↓ 2.3 7 1

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

17. 49.891 49.891 ↑ 1.0 1 1

CTE Scan on metricstoal mt (cost=0.00..0.02 rows=1 width=32) (actual time=49.891..49.891 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)