Thread: Index size
Hi, compared to Sybase, wich compresses indices, the index size in PostgrSQL seems huge. To determine the Index size I used "pg_relation_size" and "pg_total_relation_size". In fact I used a query like: SELECT pg_size_pretty( pg_total_relation_size('tablename') - pg_relation_size('tablename') ); This will give me the index size *plus* the toast size. Is there a way to retrieve /only/ the index size? As a more practical example: mydb=# SELECT pg_size_pretty( pg_total_relation_size('mytable') ); pg_size_pretty ---------------- 5032 MB (1 row) mydb=# SELECT pg_size_pretty( pg_relation_size('mytable') ); pg_size_pretty ---------------- 2382 MB (1 row) Note that these are not the real table-/db-names. I'd shoot myself in the foot if I had names like these in production ;) As you can see, this result would mean that indexes with toast tables combined are larger than the data itself. This comes from a database which has an awful schema design, and that could very well be the cause.But I really would like to examine this case further and see where the disk- space is allocated. To re-iterate the question: How can I see the (on-disk) size of one specific index?
On Aug 3, 12:52 pm, Michel Albert <exh...@gmail.com> wrote: > Hi, > > compared to Sybase, wich compresses indices, the index size in > PostgrSQL seems huge. To determine the Index size I used > "pg_relation_size" and "pg_total_relation_size". In fact I used a > query like: > > SELECT pg_size_pretty( pg_total_relation_size('tablename') - > pg_relation_size('tablename') ); > > This will give me the index size *plus* the toast size. Is there a way > to retrieve /only/ the index size? > > As a more practical example: > > mydb=# SELECT pg_size_pretty( pg_total_relation_size('mytable') ); > pg_size_pretty > ---------------- > 5032 MB > (1 row) > > mydb=# SELECT pg_size_pretty( pg_relation_size('mytable') ); > pg_size_pretty > ---------------- > 2382 MB > (1 row) > > Note that these are not the real table-/db-names. I'd shoot myself in > the foot if I had names like these in production ;) As you can see, > this result would mean that indexes with toast tables combined are > larger than the data itself. This comes from a database which has an > awful schema design, and that could very well be the cause.But I > really would like to examine this case further and see where the disk- > space is allocated. > > To re-iterate the question: How can I see the (on-disk) size of one > specific index? Nevermind.... by looking at http://www.postgresql.org/docs/8.2/interactive/disk-usage.html I tracked down the culprit. It's got nothing to do with the TOAST tables. The index itself is that large. The PK of the table spans over 13 columns. With the table having only 17 colums ;) It still surprises me that the index is larger than the table though... Anyways. It seems it's a design related problem and there's noting I can do about that :( I'd still be interested to know if the indexes are compressed in Postgres... ;)
On Mon, Aug 3, 2009 at 12:06 PM, Michel Albert<exhuma@gmail.com> wrote: > On Aug 3, 12:52 pm, Michel Albert <exh...@gmail.com> wrote: >> This will give me the index size *plus* the toast size. Is there a way >> to retrieve /only/ the index size? You can just use pg_relation_size() on the index name itself. > The PK of the table spans over > 13 columns. With the table having only 17 colums ;) > > It still surprises me that the index is larger than the table > though... Anyways. It seems it's a design related problem and there's > noting I can do about that :( Indeed. That may be useful (marginally) for enforcing a unique constraint. But it's going to be seriously impaired at being useful for the usual index things like satisfying where clauses and order by clauses quickly. And it'll slow down updates and inserts noticably to have to maintain an index like that. > I'd still be interested to know if the indexes are compressed in > Postgres... ;) Indexes in Postgres look a lot like tables. The per-row overhead is lower but you have such large index rows here that the overhead will be irrelevant anyways. The actual data takes the same amount of space in the index that it does in the table. Individual columns can be compressed if they're big but if so then they'll already be compressed in the table anyways. Usually a big part of the performance advantage of an index is precisely that it has a lot fewer columns than the table. What i think you're really looking for is either a kind of index organized table or prefix compression. There have been attempts at both but nothing that's been accepted for various reasons. -- greg http://mit.edu/~gsstark/resume.pdf