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

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>