Skip site navigation (1) Skip section navigation (2)

Re: Memory usage - indexes

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Memory usage - indexes
Date: 2010-09-24 17:16:13
Message-ID: 4C9CDCDD.8000603@ca.afilias.info (view raw or flat)
Thread:
Lists: pgsql-performance
  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.



In response to

Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2010-09-24 17:34:41
Subject: Re: Query much faster with enable_seqscan=0
Previous:From: Tobias BroxDate: 2010-09-24 16:52:42
Subject: Re: Memory usage - indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group