高级问题: 我能否更快地使用sum
进行order by
和group by
操作? (在非常大的表格中,例如数百万行,使用PG 8.4)
假设我有这样一张表:
Table "public.summary"
Column | Type | Modifiers
-------------+-------------------+------------------------------------------------------
ts | integer | not null default nextval('summary_ts_seq'::regclass)
field1 | character varying | not null
otherfield | character varying | not null
country | character varying | not null
lookups | integer | not null
Indexes:
"summary_pk" PRIMARY KEY, btree (ts, field1, otherfield, country)
"ix_summary_country" btree (country)
"ix_summary_field1" btree (field1)
"ix_summary_otherfield" btree (otherfield)
"ix_summary_ts" btree (ts)
我需要查询的是:
select summary.field1,
summary.country,
summary.ts,
sum(summary.lookups) as lookups,
from summary
where summary.country = 'za' and
summary.ts = 1275177600
group by summary.field1, summary.country, summary.ts
order by summary.ts, lookups desc, summary.field1
limit 100;
(中文:在特定的(ts,country)下,前100个field1的“热门程度”是指与任何匹配行的查找次数之和,而不考虑其他字段的值)
有没有什么办法可以真正加快这个过程?从算法上看,这似乎是一种全表扫描的操作,但我可能会漏掉一些东西。