fixed-length row

[David Garamond]

The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)

Is there a command/query in psql which can show storage requirement for
each field? For example:

db1=# \d t1;
Table "public.t1"
Column | Type | Modifiers | Storage size
--------+-------------+-----------+--------------
id | inet | not null | 24
i | integer | | 4
c | varchar(10) | | variable
Indexes:
"t1_pkey" primary key, btree (id)

--
dave
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Thursday 15 January 2004 14:17, David Garamond wrote:

The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)
Not really - there have been various discussions about timing differences
between char() and varchar() and I don't recall one being noticably faster
than the others.
Is there a command/query in psql which can show storage requirement for
each field? For example:

No, but there's stuff in the archives, and I think something on techdocs too.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

David Garamond <li***@zara.6.i sreserved.com> writes:

The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). Is there a similar recommendation in PostgreSQL?

No. There are some marginal optimizations that take place if your
columns are fixed-width and not null, but I wouldn't suggest contorting
your database design to enable them to occur.

In particular, people who have taken this bait generally think that
it's a good idea to substitute char(n) for varchar(n). That is almost
inevitably a pessimization, because the extra I/O time for all those
padding blanks will surely swamp the few CPU cycles saved by using
precalculated field offsets. (Not to mention that char(n) is not really
fixed-width anyway, in Postgres or any other implementation that
supports variable-length character encodings.)

I'd be willing to speculate that the MySQL manual's advice is bad
even for MySQL, but I haven't benchmarked the case there.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Martijn van Oosterhout]

On Thu, Jan 15, 2004 at 09:17:55PM +0700, David Garamond wrote:

The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).
Just remember that there are no fixed length string types. Your assumption
that char(M) = M bytes is wrong. M characters can take essentially any
length in bytes depending on the encoding. Storing strings as CHAR a field
takes the same amount of space as in a VARCHAR or TEXT field. The only
difference is in the contraint checking on insert and the handling of
trailing spaces.

That said, somewhere in the pg_attriute table is a column that says the size
in bytes or variable.
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFABwLWY5T wig3Ge+YRAhsAAK DfEqRLRs3PcBynS WCCqQRCr4AcwQCf Z7ac
P5hZEXDAFWkizSC zzrhKuvs=
=OgyT
-----END PGP SIGNATURE-----

[David Garamond]

Martijn van Oosterhout wrote:

That said, somewhere in the pg_attriute table is a column that says the size
in bytes or variable.

Perfect, thanks. It shows that cidr and inet are indeed variable-length.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruce Momjian]

Richard Huxton wrote:

On Thursday 15 January 2004 14:17, David Garamond wrote:

The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)

Not really - there have been various discussions about timing differences
between char() and varchar() and I don't recall one being noticably faster
than the others.

Is there a command/query in psql which can show storage requirement for
each field? For example:

No, but there's stuff in the archives, and I think something on techdocs too.

FAQ item 4.14 covers this, and reports CHAR() and VARCHAR() have the
same performance characteristics .

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.ph a.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend