explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9KpH

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 742.988 ↑ 3.0 3 1

Sort (cost=419,255.12..419,255.15 rows=9 width=72) (actual time=742.988..742.988 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.851 227.068 ↑ 1.0 1 1

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

4. 226.217 226.217 ↓ 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=27.411..226.217 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. 34.090 742.970 ↑ 3.0 3 1

GroupAggregate (cost=209,997.14..209,997.86 rows=9 width=72) (actual time=712.716..742.970 rows=3 loops=1)

  • Group Key: materialisedview.channel_id, ch.channelname
6. 87.982 708.880 ↓ 1,645.6 74,052 1

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

  • Sort Key: materialisedview.channel_id, ch.channelname
  • Sort Method: external sort Disk: 4,024kB
7. 54.733 620.898 ↓ 1,645.6 74,052 1

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

  • Join Filter: (ch.channelid = materialisedview.channel_id)
  • Rows Removed by Join Filter: 444,312
8. 269.957 269.957 ↓ 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=14.116..269.957 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. 69.116 296.208 ↓ 2.3 7 74,052

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

10. 0.005 227.092 ↓ 2.3 7 1

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

11. 227.078 227.078 ↑ 1.0 1 1

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

12. 0.009 0.009 ↓ 2.3 7 1

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

  • Filter: (isdeleted = 0)