# 每日报表优化

# 每日报表

统计过去一段时间的数据，你发现 payment 表上每天的订单量很大，下面这个查询的统计过程占用了大多数数据库资源，查询不会
早止当天，总是在历史上某一个日期段内查询

```postgresql
select payment_date::date as day, sum(amount)
from payment
where (payment_date::date) between $1 and $2
group by payment_date::date;
```

payment 表的信息如下：

```text
                                             Table "public.payment"
    Column    |            Type             | Collation | Nullable |                   Default
--------------+-----------------------------+-----------+----------+---------------------------------------------
 payment_id   | integer                     |           | not null | nextval('payment_payment_id_seq'::regclass)
 customer_id  | smallint                    |           | not null |
 staff_id     | smallint                    |           | not null |
 rental_id    | integer                     |           | not null |
 amount       | numeric(5,2)                |           | not null |
 payment_date | timestamp without time zone |           | not null |
Indexes:
    "payment_pkey" PRIMARY KEY, btree (payment_id)
```

怎样做是最有效的？

##  答案

建立物化视图并建立索引。

```postgresql
create materialized view view_daily_payment as 
    select payment_date::date as day, sum(amount) as amount from payment group by day;
    
create index on view_daily_payment(day);

```

使用

```postgresql
select day, amount from view_daily_payment where day between $1 and $2;
```

进行查询。并且每天定时执行一次刷新命令

```postgresql
REFRESH MATERIALIZED VIEW view_daily_payment;
```

## 选项

### 不会优化 sum 和 group by

在 payment_date 列上建立索引

```postgresql
create index on payment(payment_date);
```

### 不会优化 sum 

建立计算列

```postgresql
alter table payment add day date generated always as ( payment_date::date ) stored 
```

然后使用它改写查询

```postgresql
select day as day, sum(amount)
from payment
where day between $1 and 'yesterday'::date
group by day;
```

### 优化了日期查询，但是不会优化统计过程

建立表达式索引

```postgresql
create index on payment((payment_date::date));
```

### 不做物化，对查询速度不会有显著改善

建立视图

```postgresql
create view view_daily_payment as select payment_date::date as day, amount from payment;
```

然后在视图 view_daily_payment 上执行

```postgresql
select day, sum(amount) 
from view_daily_payment
where day between $1 and 'yesterday'::date
group by day 
```