explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yvzs

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 483.569 ↑ 3.0 3 1

Sort (cost=419,255.12..419,255.15 rows=9 width=104) (actual time=483.569..483.569 rows=3 loops=1)

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

CTE metricstoal

3. 0.582 126.571 ↑ 1.0 1 1

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

4. 125.989 125.989 ↓ 6.5 4,752 1

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

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3))
  • Filter: (((year = 2,019) AND (day = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((year = 2,018) AND (day = ANY ('{8,9,10,11,12}'::integer[]))))
  • Rows Removed by Filter: 284,328
5. 20.389 483.556 ↑ 3.0 3 1

GroupAggregate (cost=209,997.14..209,997.86 rows=9 width=104) (actual time=465.498..483.556 rows=3 loops=1)

  • Group Key: materialisedview.channel_id, mt.total, ch.channelname
6. 98.499 463.167 ↓ 1,645.6 74,052 1

Sort (cost=209,997.14..209,997.25 rows=45 width=84) (actual time=456.470..463.167 rows=74,052 loops=1)

  • Sort Key: materialisedview.channel_id, mt.total, ch.channelname
  • Sort Method: external sort Disk: 4,024kB
7. 51.994 364.668 ↓ 1,645.6 74,052 1

Nested Loop (cost=0.56..209,995.91 rows=45 width=84) (actual time=132.706..364.668 rows=74,052 loops=1)

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

Index Scan using ocpppcv_planpage_idx_2_day on orgunit_customer_product_payer_plan_channel_day_view materialisedview (cost=0.56..209,543.29 rows=9,654 width=20) (actual time=6.115..164.570 rows=74,052 loops=1)

  • Index Cond: ((sales_force_id = 1) AND (orgunit_id = 174) AND (customer_id = 3))
  • Filter: (((year = 2,019) AND (month = ANY ('{1,2,3,4,5,6,7}'::integer[]))) OR ((year = 2,018) AND (day = ANY ('{8,9,10,11,12}'::integer[]))))
  • Rows Removed by Filter: 215,028
9. 21.522 148.104 ↓ 2.3 7 74,052

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

10. 0.003 126.582 ↓ 2.3 7 1

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

11. 126.574 126.574 ↑ 1.0 1 1

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

12. 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)