fixed-length row
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
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
David Garamond <lists@zara.6.isreserved.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
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 <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
(... 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
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
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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073