Message-ID: <4C9CDCDD.8000603@ca.afilias.info>
Date: Fri, 24 Sep 2010 13:16:13 -0400
From: Brad Nicholson <bnichols@ca.afilias.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.9) Gecko/20100920 Lightning/1.0b2 Thunderbird/3.1.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Memory usage - indexes
References: <AANLkTimd0Y7KhnwkSXz4FYjsxC8ocDeRRH=PXvU1a3vv@mail.gmail.com>
	<865426.21884.qm@web39701.mail.mud.yahoo.com>
	<AANLkTinK6jGN5+q=1FYjD8EkTJTB24rc-dd3_YnNgg=5@mail.gmail.com>
In-Reply-To: <AANLkTinK6jGN5+q=1FYjD8EkTJTB24rc-dd3_YnNgg=5@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  On 10-09-24 12:46 PM, Tobias Brox wrote:
> On 24 September 2010 18:23, Bob Lunney<bob_lunney@yahoo.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.