Hi,
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:
select
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"
from
pg_class c
left join pg_namespace n on n.oid = c.relnamespace
left join pg_tablespace t on t.oid = c.reltablespace
where
(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
regards
thomas
begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus@xxxxxxxxxxxxx
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
