高级：我可以根据总和进行分组
更快？ (PG 8.4,fwiw.,在非小桌子上……想想O(数百万行))
更快？ (PG 8.4,fwiw.,在非小桌子上……想想O(数百万行))
假设我有一个这样的表：
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.ts
order by summary.ts,lookups desc,summary.field1
limit 100;
(英语：在特定(ts,国家)的前100名field1’s,其中’topness’是总和
任何匹配行的查找,无论其他字段的值如何)
我能做些什么来加速这个吗？在算法上
这似乎是一个全表扫描的东西,但我可能会遗漏一些东西.
