daily_payment.md

# 每日报表

数据分析组需要经常执行以下查询生成从昨天到之前某一天的交易量统计

```postgresql
select payment_date::date as day, sum(amount)
from payment
where (payment_date::date) between $1 and 'yesterday'::date
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 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 
```

### 对该查询帮助不大

在 payment_date 列上建立索引

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

### 可以简化语句，但对优化没有帮助

建立计算列

```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;
```