Re: Will pg_repack improve this query performance?

On Wed, Oct 15, 2014 at 5:03 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> A CLUSTER would help putting rows with the same to_id together. Disk access would be less random that way, so it would help some.
>
> According to your query plan, accessing disks (assuming that’s what made the difference) was 154 (7700 ms / 50 ms) times slower than accessing memory. I don’t have the numbers for your disks or memory, but that doesn’t look like an incredibly unrealistic difference. That begs the question, how random was that disk access and how much can be gained from clustering that data?

Other than grouping tuples in a more favorable order to minimize I/O,
the big benefit of running a CLUSTER or pg_repack is that you
eliminate any accumulated bloat. (And if bloat is your real problem,
ideally you can adjust your autovacuum settings to avoid the problem
in the future.) You may want to check on the bloat of that table and
its indexes with something like this:

https://wiki.postgresql.org/wiki/Show_database_bloat