Re: Memory usage - indexes

On 10-09-24 12:46 PM, Tobias Brox wrote:
> On 24 September 2010 18:23, Bob Lunney<bob_lunney(at)yahoo(dot)com> wrote:
>> Consult pg_statio_user_indexes to see which indexes have been used
>> and how much.
> What is the main differences between pg_statio_user_indexes and
> pg_stat_user_indexes?
>

The pg_stat_* views give you usage information (for indexes - number of
scans, numbers of tuples read/fetched). The pg_statio_* views give you
information about block reads and block hits

> I'm not at all concerned about 1 and 2 above - we don't have any
> performance issues on the write part, and we have plenty of disk
> capacity. We are still doing the nightly vacuum thing, and it does
> hurt us a bit since it's dragging ever more out in time.

Why is the vacuum dragging out over time? Is the size of your data
increasing, are you doing more writes that leave dead tuples, or are
your tables and/or indexes getting bloated?

Also, is there a reason why you do nightly vacuums instead of letting
autovacuum handle the work? We started doing far less vacuuming when we
let autovacuum handle things.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.