Re: Converting MySQL tinyint to PostgreSQL

On Tue, Jul 19, 2005 at 02:02:28AM +0200, Dawid Kuroczko wrote:
> Out of curiosity, do I understand right that if I create table
>
> CREATE TABLE sample1 (
> a boolean,
> b int,
> c boolean
> );
>
> ...it will take more storage than:
>
> CREATE TABLE sample2 (
> b int,
> a boolean,
> c boolean
> );

Yep. If you created a C structure with those types it would add padding
also. It's a fact of life given that some (many/most?) processors can't
directly read a 4 byte value that's not 4-byte aligned. If you think
about it, the processor has to do two 4-byte reads (aligned) and glue
them together to get the value you want. The i386 has microcode to
automate this but RISC processors in particular make the compiler
and/or OS deal with it.

int, bool, bool 6 bytes, no padding
bool, int, bool 9 bytes, including 3 bytes padding
bool, bool, int 8 bytes, including 2 bytes padding

Assuming an int is 4 bytes. If it's 8-bytes it's even more obvious.

I think it would be a really good for postgresql to reorder the fields
in the background (on disk, not in select * statements).

First all fixed width fields, starting from highest alignment and going
down. Then the variable length fields. Note, postgresql actually has
optimisations for lookups on fixed width fields, but only if they are
fixed width from the beginning of the tuple. Ofcourse, NULL fields
screw it up but it would be good to make use of the optimisation
whenever possible.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.