explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aQhX

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

Sort (cost=56,835.90..56,835.92 rows=9 width=72) (actual time=165.161..165.161 rows=3 loops=1)

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

CTE metricstoal

3. 0.777 1.793 ↑ 1.0 1 1

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

4. 0.683 1.016 ↓ 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.356..1.016 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.000 0.333 ↓ 0.0 0 1

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

6. 0.193 0.193 ↓ 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.193..0.193 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.140 0.140 ↓ 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.140..0.140 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. 19.553 165.150 ↑ 3.0 3 1

GroupAggregate (cost=53,866.38..53,867.10 rows=9 width=72) (actual time=147.693..165.150 rows=3 loops=1)

  • Group Key: materialisedview.channel_id, ch.channelname
9. 48.581 145.597 ↓ 1,645.6 74,052 1

Sort (cost=53,866.38..53,866.49 rows=45 width=84) (actual time=138.696..145.597 rows=74,052 loops=1)

  • Sort Key: materialisedview.channel_id, ch.channelname
  • Sort Method: external merge Disk: 4,024kB
10. 77.191 97.016 ↓ 1,645.6 74,052 1

Nested Loop (cost=523.90..53,865.15 rows=45 width=84) (actual time=6.566..97.016 rows=74,052 loops=1)

  • Join Filter: (ch.channelid = materialisedview.channel_id)
  • Rows Removed by Join Filter: 444,312
11. 15.564 19.825 ↓ 7.7 74,052 1

Bitmap Heap Scan on orgunit_customer_product_payer_plan_channel_day_view materialisedview (cost=523.90..53,412.35 rows=9,658 width=20) (actual time=4.758..19.825 rows=74,052 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 ((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[]))))
  • Filter: (sales_force_id = 1)
  • Heap Blocks: exact=3,703
12. 0.001 4.261 ↓ 0.0 0 1

BitmapOr (cost=523.90..523.90 rows=14,081 width=0) (actual time=4.261..4.261 rows=0 loops=1)

13. 4.125 4.125 ↓ 5.3 72,072 1

Bitmap Index Scan on ocpppcv_orgunitid_customerid_year_month_idx_day (cost=0.00..488.81 rows=13,654 width=0) (actual time=4.125..4.125 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. 0.135 0.135 ↓ 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.135..0.135 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[])))
15. 0.000 0.000 ↓ 2.3 7 74,052

Materialize (cost=0.00..18.19 rows=3 width=72) (actual time=0.000..0.000 rows=7 loops=74,052)

16. 0.003 1.803 ↓ 2.3 7 1

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

17. 1.795 1.795 ↑ 1.0 1 1

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

18. 0.005 0.005 ↓ 2.3 7 1

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

  • Filter: (isdeleted = 0)