pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Null 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