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

Re: caching indexes and pages?

From: Thomas Markus <t(dot)markus(at)proventis(dot)net>
To: Thomas Finneid <tfinneid(at)fcon(dot)no>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: caching indexes and pages?
Date: 2009-01-22 07:06:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

Thomas Finneid schrieb:
> 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?
i use this query:
      t.spcname as "tablespace"
    , pg_get_userbyid(c.relowner) as "owner"
    , n.nspname as "schema"
    , relname::text as "name"
    , pg_size_pretty(pg_total_relation_size(c.oid))::text as "total size"
    , case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end as "type"
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
    (pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
    c.relkind desc, pg_total_relation_size(c.oid) desc
> 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?
try to reorganize your data with CLUSTER and create appropriate indixes 
(dont forget to check statistics). there are several threads about 
memory configuration. look for shared_buffers
> 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

Attachment: t_markus.vcf
Description: text/x-vcard (255 bytes)

In response to


pgsql-performance by date

Next:From: Thomas FinneidDate: 2009-01-22 11:12:15
Subject: caching written values?
Previous:From: A. KretschmerDate: 2009-01-22 07:01:36
Subject: Re: caching indexes and pages?

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