pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNull integer columns
3+ messages / 2 participants
[nested] [flat]
* Null integer columns
@ 2005-01-05 19:09 Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 0 replies; 3+ messages in thread
From: Jeffrey Tenny @ 2005-01-05 19:09 UTC (permalink / raw)
To: pgsql-performance
I have an integer column that is not needed for some rows in the table
(whether it is necessary is a factor of other row attributes, and it
isn't worth putting in a separate table).
What are the performance tradeoffs (storage space, query speed) of using
NULL versus a sentinel integer value?
Not that it matters, but in the event where the column values matter,
the numberic value is a foreign key. Advice on that welcome too.
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Null integer columns
@ 2005-01-08 21:07 Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 1 reply; 3+ messages in thread
From: Jeffrey Tenny @ 2005-01-08 21:07 UTC (permalink / raw)
To: pgsql-performance
I have an integer column that is not needed for some rows in the table
(whether it is necessary is a factor of other row attributes, and it
isn't worth putting in a separate table).
What are the performance tradeoffs (storage space, query speed) of using
a NULL enabled column versus a NOT-NULL column with a sentinel integer
value?
Not that it matters, but in the event where the column values matter,
the numberic value is a foreign key. Advice on that welcome too.
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Null integer columns
@ 2005-01-08 21:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-01-08 21:55 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: pgsql-performance
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> What are the performance tradeoffs (storage space, query speed) of using
> a NULL enabled column versus a NOT-NULL column with a sentinel integer
> value?
> Not that it matters, but in the event where the column values matter,
> the numberic value is a foreign key. Advice on that welcome too.
In that case you want to use NULL, because the foreign key mechanism
will understand that there's no reference implied. With a sentinel
value you'd have to have a dummy row in the master table --- which will
cause you enough semantic headaches that you don't want to go there.
The performance difference could go either way depending on a lot of
other details, but it will be insignificant in any case. Don't screw up
your database semantics for it.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-01-08 21:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-05 19:09 Null integer columns Jeffrey Tenny <jeffrey.tenny@comcast.net>
2005-01-08 21:07 Null integer columns Jeffrey Tenny <jeffrey.tenny@comcast.net>
2005-01-08 21:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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