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

Re: Dept of ugly hacks: eliminating padding space in system indexes

From: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Dept of ugly hacks: eliminating padding space in system indexes
Date: 2008-06-24 00:19:59
Message-ID: 48603DAF.6000501@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
>> Were you able to time any speedup?
>>     
>
> I didn't try; can you suggest any suitable benchmark?
>
>   

Unfortunately - no. I kind of think it won't benefit any of my databases 
in any noticeable way. My numbers are similar to yours:

> pccyber=# select pg_database_size('postgres');
>           4468332
>
> pccyber=# select pg_relation_size('pg_class_relname_nsp_index');
>             90112
>
> pccyber=# select pg_relation_size('pg_proc_proname_args_nsp_index');
>            294912

Not that I disagree with your change, but < 5 Mbytes in 4 Gbytes of RAM 
for my main PostgreSQL system that I manage seems like a drop in the 
bucket. Even if 40% of pg_class_relname and pg_proc_proname indices was 
saved - we're talking about 154 Kbytes saved on both those indices 
combined. Minor? Major? I bet I wouldn't notice unless my database 
requirements used up all RAM, and even then I'm suspecting it wouldn't 
matter except for border line cases (like all pages required for 
everything else happened to equal 4 Gbytes near exactly).

> The performance impact is probably going to be limited by our extensive
> use of catalog caches --- once a desired row is in a backend's catcache,
> it doesn't take a btree search to fetch it again.  Still, the system
> indexes are probably "hot" enough to stay in shared buffers most of the
> time, and the smaller they are the more space will be left for other
> stuff, so I think there should be a distributed benefit.
>   

In my opinion it is 'do the right thing', rather than a performance 
question. It seems to me that an index keeping tracking of space 
characters at the end of a name, char, varchar, or text does not make 
sense, and the right thing may be to do a more generic version of your 
patch? In the few cases that space at the end matters, couldn't that be 
determined by re-checking the table row after querying it?

Cheers,
mark

-- 
Mark Mielke <mark(at)mielke(dot)cc>

In response to

Responses

pgsql-hackers by date

Next:From: ITAGAKI TakahiroDate: 2008-06-24 03:14:57
Subject: Re: pg_stat_statements
Previous:From: Tom LaneDate: 2008-06-23 23:45:52
Subject: Re: Dept of ugly hacks: eliminating padding space in system indexes

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