Re: Memory usage - indexes

On 24 September 2010 21:06, Bob Lunney <bob_lunney(at)yahoo(dot)com> wrote:
> First off, what version of PostgreSQL are you running?  If you have 8.4, nightly vacuuming shouldn't be necessary with properly tuned autovacuum jobs.

8.3. We'll upgrade to 9.0 during the December holidays fwiw. But
point taken, I will continue to push for autovacuum to be turned on.

Anyway, I think the nightly vacuuming does have some merit. For some
of the queries, most of the daytime we're quite sensitive to latency.
Well, I guess the proper solution to that is to tune the autovacuum
configuration so it acts less aggressively at the times of the day
where we need low latency...

> You're correct, unused indexes will
> remain on disk, but indexes that don't completely fit into memory must be
> read from disk for each index scan, and that hurts performance.  (In fact, it
> will suddenly drop like  a rock.  BTDT.)

Sounds quite a lot like our problems nowadays - as well as previous
time when I found that overlapping index that could be dropped.

> By making smaller equivalent indexes on partitioned data the indexes for
> individual partitions are more likely to stay in memory, which is particularly
> important when multiple passes are made over the index by a query.

I was looking a bit into table partitioning some years ago, but didn't
really find any nice way to partition our tables. One solution would
probably be to partition by creation date and set up one partition for
each year, but it seems like a butt ugly solution, and I believe it
would only help if the select statement spans a date range on the
creation time.

> You are correct on all the points you make concerning indexes, but point 4
> is the one I'm referring to.  You discovered this independently yourself,
> according to your anecdote about the overlapping indexes.

Yes, but that was the heavily used index ... my belief is that the
_unused_ index, or infrequently used index wouldn't cause such memory
problems. (Then again, I suppose it would be faster to scan a
non-optimal index that is in memory than an optimal index that is on
disk?) Well, if both you and Greg Smith recommends to drop those
indexes, I suppose we probably should do that ... ;-)