Re: Large databases, performance

On 7 Oct 2002 at 16:10, Manfred Koizar wrote:
> if i'm not mistaken, a char(n)/varchar(n) column is stored as a 32-bit
> integer specifying the length followed by as many characters as the
> length tells. On 32-bit Intel hardware this structure is aligned on a
> 4-byte boundary.

That shouldn't be necessary for a char field as space is always pre-allocated.
Sounds like a possible area of imporvement to me, if that's the case..

> Hackers, do you think it's possible to hack together a quick and dirty
> patch, so that string length is represented by one byte? IOW can a
> database be built that doesn't contain any char/varchar/text value
> longer than 255 characters in the catalog?

I say if it's a char field, there should be no indicator of length as it's not
required. Just store those many characters straight ahead..

>
> If I'm not told that this is impossibly, I'd give it a try. Shridhar,
> if such a patch can be made available, would you be willing to test
> it?

Sure. But the server machine is not available this week. Some other project is
using it. So the results won't be out unless at least a week from now.

> What can you do right now? Try using v7.3 beta and creating your
> table WITHOUT OIDS. This saves 8 bytes per tuple; not much, but
> better save 4% than nothing.

IIRC there was some header optimisation which saved 4 bytes. So without OIDs
that should save 8. Would do that as first next thing.

I talked to my friend regarding postgresql surpassing mysql substantially in
this test. He told me that the last test where postgresql took 23000+/150 sec
for load/index and mysql took 18,000+/130 index, postgresql was running in
default configuration. He forgot to copy postgresql.conf to data directory
after he modified it.

This time results are correct. Postgresql loads data faster, indexes it faster
and queries in almost same time.. Way to go..

Regards,
Shridhar

-----------------------------------------------------------
Shridhar Daithankar
LIMS CPE Team Member, PSPL.
mailto:shridhar_daithankar(at)persistent(dot)co(dot)in
Phone:- +91-20-5678900 Extn.270
Fax :- +91-20-5678901
-----------------------------------------------------------