计算高数量级计数(100000以上)的匹配行数

PostgreSQL 9.2之前,MVCC的实现要求任何查询访问表的每一行,以检查该行的版本是否对当前事务可见。即使查询只涉及索引列,也会发生这种情况。这在大型表上表现为计数缓慢,即使对于简单的情况也是如此。

PostgreSQL 9.2实现 index only scans ,这可能有助于缓解某些工作负载的此问题。

如果您被困在v9.2以下,如果您只需要一个简单查询的近似行数,那么有一些已知的解决方法。看见 http://wiki.postgresql.org/wiki/Count_estimate .

保留一个按天汇总的事件表。

create table incidents_agreggated_by_day (
    "day" date primary key, total integer
);

每天跑步:

insert into events_agreggated_by_day ("day", total) values
select date_trunc('day', time_occurred), count(*) total
from incidents
where 
    time_occurred < current_date
    and date_trunc('day', time_occurred) not in (
        select "day" from incidents_agreggated_by_day
    )
group by 1

假设您想要的总数介于“2013-01-01 10:37”和“2013-03-02 11:20”之间:

select
(
    select sum(total)
    from incidents_aggregated_by_day
    where "day" >= '2013-01-02'::date and "day" < '2013-03-02'::date
) +
(
    select count(*)
    from incidents
    where 
        time_ocurred >= '2013-01-01 10:37':timestamp
        and time_ocurred < '2013-01-02'
        or
        time_ocurred <= '2013-03-02 11:20':timestamp
        and time_ocurred >= '2013-01-02'
) total

在中,您将读取数百或数千行,而不是读取1亿行。如果索引正确,它会很快。

另一种方法可能是对表进行分区。这个家伙似乎已经解决了一个非常类似的分区问题:

http://www.if-not-true-then-false.com/2009/performance-testing-between-partitioned-and-non-partitioned-postgresql-tables-part-3/

我对使用他的方法的担忧是可维护性。在他的例子中(你必须点击教程的第1部分,看看他是如何创建分区的),他手动创建了每个子表,并在触发器中对到子表的路由进行了硬编码。如果您的表不断增长,那么您将要做大量的DBA工作。

然而,他的表现似乎确实得到了很大的提升。因此,如果您能弄清楚如何使其更易于维护,这可能是一个很好的方法。

这正是维度建模和数据仓库设计要解决的问题。

我之前参与的一个项目在几周内用Ruby构建了一个数据仓库,以便处理此类查询,并使用一个简单的REST API将其公开给主应用程序。基本上,您提取数据并将其转换为“星型模式”,该模式针对您所描述的查询进行了高度优化。

Postgresql非常适合作为数据仓库数据库。

这是一个非常详细的主题,一个很好的入门资源是: http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247