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

caching indexes and pages?

From: Thomas Finneid <tfinneid(at)fcon(dot)no>
To: pgsql-performance(at)postgresql(dot)org
Subject: caching indexes and pages?
Date: 2009-01-22 06:23:38
Message-ID: 497810EA.2000504@fcon.no (view raw or flat)
Thread:
Lists: pgsql-performance
Hi I am developing a database and have a couple of questions I havent 
found an answer to yet.

1) how do I find the size of an index, i.e. the size on disk?

2) I have a query that is taking a long time to complete because the 
table is about 120GB large. Its only returning 2000 rows, so in 
principle it should be fast. But because the data is spread across the 
table, I am assuming it needs to do a lot of disk access to fetch the 
appropriate pages. Since the amount of data is so large I am also 
assuming that whenever I do a query all memory caches have to be 
replaced to make room for the new pages. What I am wondering is which 
config settings can I use to increase the amount of memory postgres 
keeps to cache pages and indexes?

I tried work_mem and maintenance_work_mem but it does not seem to make 
much difference yet. Admittedly I had set it to 100M and 80M, so after 
reading a little bit more I have found that I could easily set it to 
several GBs. But I am not sure those are the correct config parameters 
to use for this. I havent found any others that are relevant so far.

regards

thomas

Responses

pgsql-performance by date

Next:From: M. Edward (Ed) BoraskyDate: 2009-01-22 06:23:45
Subject: Re: linux, memory (mis)accounting/reporting, and the planner/optimizer
Previous:From: Greg SmithDate: 2009-01-22 00:02:43
Subject: Re: linux, memory (mis)accounting/reporting, and the planner/optimizer

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