pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEfficiency question: VARCHAR with empty string vs NULL
3+ messages / 2 participants
[nested] [flat]
* Efficiency question: VARCHAR with empty string vs NULL
@ 2002-05-24 17:20 Doug Fields <dfields-pg-general@pexicom.com>
2002-05-24 18:54 ` Re: Efficiency question: VARCHAR with empty string vs NULL Tom Lane <tgl@sss.pgh.pa.us>
2002-05-24 19:10 ` Re: Efficiency question: VARCHAR with empty string Doug Fields <dfields-pg-general@pexicom.com>
0 siblings, 2 replies; 3+ messages in thread
From: Doug Fields @ 2002-05-24 17:20 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hello,
I have a table with about 15-20 VARCHAR items. I use VARCHAR on the
assumption that it is more space efficient and not too much slower than
CHAR, especially when you expect many of those VARCHAR to be null or empty
strings.
Is there a performance difference with VARCHAR elements of value NULL and
zero-length string? If so, can I assume that NULL is the better performer,
and thus re-code my INSERTS to replace empty strings with NULLs (or vice
versa)?
Now that I've had to re-write all my data to substring it to fit in the
VARCHARs, one more "thing" won't matter much.
Many Thanks,
Doug
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Efficiency question: VARCHAR with empty string vs NULL
2002-05-24 17:20 Efficiency question: VARCHAR with empty string vs NULL Doug Fields <dfields-pg-general@pexicom.com>
@ 2002-05-24 18:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-05-24 18:54 UTC (permalink / raw)
To: Doug Fields <dfields-pg-general@pexicom.com>; +Cc: pgsql-general@postgresql.org
Doug Fields <dfields-pg-general@pexicom.com> writes:
> Is there a performance difference with VARCHAR elements of value NULL and
> zero-length string?
These are not semantically equivalent (if you think they are, you've
been using Oracle too long). You will almost certainly regret it
if you try recoding your app to make them equivalent. An example:
regression=# select 'foo'::varchar || ''::varchar;
?column?
----------
foo
(1 row)
regression=# select 'foo'::varchar || null::varchar;
?column?
----------
(1 row)
But to answer your question, NULLs might save a couple bytes if there
are multiple NULLs per row. I think that replacing a single empty
varchar with a NULL would net out to no change (you lose the 4-byte
varchar length word, but have to store a null-values bitmap instead),
depending on alignment issues and how many columns there are in the
table.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Efficiency question: VARCHAR with empty string
2002-05-24 17:20 Efficiency question: VARCHAR with empty string vs NULL Doug Fields <dfields-pg-general@pexicom.com>
@ 2002-05-24 19:10 ` Doug Fields <dfields-pg-general@pexicom.com>
1 sibling, 0 replies; 3+ messages in thread
From: Doug Fields @ 2002-05-24 19:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org
At 02:54 PM 5/24/2002, you wrote:
>Doug Fields <dfields-pg-general@pexicom.com> writes:
> > Is there a performance difference with VARCHAR elements of value NULL and
> > zero-length string?
>
>These are not semantically equivalent (if you think they are, you've
>been using Oracle too long). You will almost certainly regret it
>if you try recoding your app to make them equivalent. An example:
Sorry, I haven't used Oracle in quite some time so I don't have any bad
Oracle-isms. :)
Thanks, Tom. Yes, I am aware of this already and my application largely
doesn't care (i.e. in the places it does it asks = '' as well as OR IS
NULL, but in most places it doesn't bother because it doesn't matter).
I'm merely trying to wring the last ounce of performance from the database,
especially given that most of these columns are rarely searched and
certainly not indexed.
>But to answer your question, NULLs might save a couple bytes if there
>are multiple NULLs per row. I think that replacing a single empty
>varchar with a NULL would net out to no change (you lose the 4-byte
>varchar length word, but have to store a null-values bitmap instead),
>depending on alignment issues and how many columns there are in the
>table.
So, in essence, having a zero-length VARCHAR requires no additional page
loads (from the VARCHAR heap, if such a thing is used) and does not
increase the record size over having NULLs? My intent is to make this query
as fast as possible and require as few disk hits: SELECT * FROM table with
this many VARCHAR'd table.
Thanks,
Doug
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-05-24 19:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-24 17:20 Efficiency question: VARCHAR with empty string vs NULL Doug Fields <dfields-pg-general@pexicom.com>
2002-05-24 18:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-24 19:10 ` Re: Efficiency question: VARCHAR with empty string Doug Fields <dfields-pg-general@pexicom.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox