explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o8Is

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 95.903 ↑ 2.0 3 1

Sort (cost=9,758.23..9,758.24 rows=6 width=72) (actual time=95.902..95.903 rows=3 loops=1)

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

CTE metricstoal

3. 17.346 19.043 ↑ 1.0 1 1

Aggregate (cost=2,968.65..2,968.66 rows=1 width=32) (actual time=19.043..19.043 rows=1 loops=1)

4. 1.127 1.697 ↓ 6.5 4,752 1

Bitmap Heap Scan on orgunit_customer_product_payer_plan_channel_day_view materialisedview_1 (cost=67.85..2,966.83 rows=727 width=12) (actual time=0.604..1.697 rows=4,752 loops=1)

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

BitmapOr (cost=67.85..67.85 rows=729 width=0) (actual time=0.570..0.570 rows=0 loops=1)

6. 0.324 0.324 ↓ 9.2 2,772 1

Bitmap Index Scan on ocpppcv_planpage_idx_111_day (cost=0.00..37.22 rows=301 width=0) (actual time=0.324..0.324 rows=2,772 loops=1)

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (day = ANY ('{1,2,3,4,5,6,7}'::integer[])))
7. 0.245 0.245 ↓ 4.6 1,980 1

Bitmap Index Scan on ocpppcv_planpage_idx_111_day (cost=0.00..30.26 rows=427 width=0) (actual time=0.245..0.245 rows=1,980 loops=1)

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (day = ANY ('{8,9,10,11,12}'::integer[])))
8. 13.742 95.888 ↑ 2.0 3 1

GroupAggregate (cost=6,789.31..6,789.49 rows=6 width=72) (actual time=87.355..95.888 rows=3 loops=1)

  • Group Key: materialisedview.channel_id, ch.channelname
9. 8.851 82.146 ↓ 1,584.0 9,504 1

Sort (cost=6,789.31..6,789.33 rows=6 width=84) (actual time=81.373..82.146 rows=9,504 loops=1)

  • Sort Key: materialisedview.channel_id, ch.channelname
  • Sort Method: quicksort Memory: 1,127kB
10. 3.346 73.295 ↓ 1,584.0 9,504 1

Nested Loop (cost=139.23..6,789.23 rows=6 width=84) (actual time=20.216..73.295 rows=9,504 loops=1)

11. 19.047 19.047 ↑ 1.0 1 1

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

12. 23.639 50.902 ↓ 1,584.0 9,504 1

Nested Loop (cost=139.23..6,789.15 rows=6 width=52) (actual time=1.157..50.902 rows=9,504 loops=1)

  • Join Filter: (materialisedview.channel_id = ch.channelid)
  • Rows Removed by Join Filter: 28,512
13. 26.181 27.263 ↓ 6.8 9,504 1

Bitmap Heap Scan on orgunit_customer_product_payer_plan_channel_day_view materialisedview (cost=139.23..6,708.56 rows=1,389 width=20) (actual time=1.147..27.263 rows=9,504 loops=1)

  • Recheck Cond: (((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (day = ANY ('{171,172,173,174,175,176,177,178,179,180,181,182}'::integer[]))) OR ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (day = ANY ('{171,172,173,174,175,176,177,178,179,180,181,182}'::integer[]))))
  • Filter: (day = ANY ('{171,172,173,174,175,176,177,178,179,180,181,182}'::integer[]))
  • Heap Blocks: exact=481
14. 0.001 1.082 ↓ 0.0 0 1

BitmapOr (cost=139.23..139.23 rows=1,665 width=0) (actual time=1.082..1.082 rows=0 loops=1)

15. 0.535 0.535 ↓ 8.5 4,752 1

Bitmap Index Scan on ocpppcv_planpage_idx_111_day (cost=0.00..64.44 rows=558 width=0) (actual time=0.535..0.535 rows=4,752 loops=1)

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,019) AND (day = ANY ('{171,172,173,174,175,176,177,178,179,180,181,182}'::integer[])))
16. 0.546 0.546 ↓ 4.3 4,752 1

Bitmap Index Scan on ocpppcv_planpage_idx_111_day (cost=0.00..74.10 rows=1,107 width=0) (actual time=0.546..0.546 rows=4,752 loops=1)

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3) AND (year = 2,018) AND (day = ANY ('{171,172,173,174,175,176,177,178,179,180,181,182}'::integer[])))
17. 0.000 0.000 ↓ 1.3 4 9,504

Materialize (cost=0.00..18.14 rows=3 width=40) (actual time=0.000..0.000 rows=4 loops=9,504)

18. 0.006 0.006 ↓ 1.7 5 1

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

  • Filter: (isdeleted = 0)