Re: Slow query with big tables

On Fri, Aug 26, 2016 at 6:17 AM, Tommi K <t2nn2t(at)gmail(dot)com> wrote:

> Hello,
> thanks for the response. I did not get the response to my email even
> though I am subscribed to the pgsql-performance mail list. Let's hope that
> I get the next one :)
>
> Increasing work_mem did not have great impact on the performance. But I
> will try to update the PostgreSQL version to see if it speeds up things.
>
> However is there way to keep query time constant as the database size
> grows.
>

Not likely. If the number of rows you are aggregating grows, it will take
more work to do those aggregations.

If the number of rows being aggregated doesn't grow, because all the growth
occurs outside of the measurement_time range, even then the new data will
still make it harder to keep the stuff you want cached in memory. If you
really want more-constant query time, you could approach that by giving the
machine as little RAM as possible. This works not by making the large
database case faster, but by making the small database case slower. That
usually is not what people want.

> Should I use partitioning or partial indexes?
>

Partitioning the Feature and Point tables on measurement_time (or
measurement_start_time,
you are not consistent on what it is called) might be helpful. However,
measurement_time does not exist in those tables, so you would first have to
de-normalize by introducing it into them.

More likely to be helpful would be precomputing the aggregates and storing
them in a materialized view (not available in 9.2). Also, more RAM and
better hard-drives can't hurt.

Cheers,

Jeff