Re: SELECT AND AGG huge tables

On Mon, Oct 15, 2012 at 1:59 PM, houmanb <houman(at)gmx(dot)at> wrote:
> Dear all,
> We have a DB containing transactional data.
> There are about *50* to *100 x 10^6* rows in one *huge* table.
> We are using postgres 9.1.6 on linux with a *SSD card on PCIex* providing us
> a constant seeking time.
>
> A typical select (see below) takes about 200 secs. As the database is the
> backend for a web-based reporting facility 200 to 500 or even more secs
> response times are not acceptable for the customer.
>
> Is there any way to speed up select statements like this:
>
> SELECT
> SUM(T.x),
> SUM(T.y),
> SUM(T.z),
> AVG(T.a),
> AVG(T.b)
> FROM T
> GROUP BY
> T.c
> WHERE
> T.creation_date=$SOME_DATE;
>
> There is an Index on T.c. But would it help to partition the table by T.c?

Probably not.

But an index on creation_date, or on (creation_date, c) might. How
many records are there per day? If you add a count(*) to your select,
what would typical values be?

Cheers,

Jeff