Thread: 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/ > (... 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
Attachment
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