我必须在课上解决一个关于postgresql中查询优化的问题。

我必须优化以下查询。

 select ol_number,
   sum(ol_quantity) as sum_qty,
   sum(ol_amount) as sum_amount,
   avg(ol_quantity) as avg_qty,
   avg(ol_amount) as avg_amount,
   count(*) as count_order
  from   orderline
  where  ol_delivery_d > '2007-01-02 00:00:00.000000'
  group by ol_number order by ol_number

对我来说，它似乎已经被优化了，但我对此并不确定。

是否可以通过索引或其他方式优化查询（也可以使用物化视图）？

执行计划(也在注释中链接)

"Sort  (cost=63652.89..63652.92 rows=11 width=13) (actual time=4026.270..4026.272 rows=15 loops=1)"
"  Sort Key: ol_number"
"  Sort Method: quicksort  Memory: 26kB"
"  ->  HashAggregate  (cost=63652.54..63652.70 rows=11 width=13) (actual time=4026.218..4026.247 rows=15 loops=1)"
"        ->  Seq Scan on order_line  (cost=0.00..44671.46 rows=1265405 width=13) (actual time=0.008..486.399 rows=1259054 loops=1)"
"              Filter: (ol_delivery_d > '2007-01-02 00:00:00'::timestamp without time zone)"
"              Rows Removed by Filter: 538903"
"Total runtime: 4026.346 ms"

我意识到下面的技巧，因为我认为平均是和总和/计数一样的东西，所以我认为这是相关的，意识到这一点还是我错了？

select ol_number, sum_qty, sum_amount, 
sum_qty/count_order as avg_qty,      
sum_amount/count_order as avg_amount, count_order 
from (select   ol_number, sum(ol_quantity) as sum_qty, sum(ol_amount) as sum_amount, count(*) as   count_order 
from order_line 
where ol_delivery_d > '2007-01-02 00:00:00.000000' 
group by ol_number 
order by ol_number) as t