On 24 September 2010 21:06, Bob Lunney <bob_lunney@xxxxxxxxx> 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 ... ;-) -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
