pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Index on nullable column
3+ messages / 3 participants
[nested] [flat]

* Index on nullable column
@ 2006-03-24 22:13 Daniel Caune <daniel.caune@ubisoft.com>
  2006-03-24 23:48 ` Re: Index on nullable column Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Daniel Caune @ 2006-03-24 22:13 UTC (permalink / raw)
  To: pgsql-sql

Hi,

Is an index on a nullable column useful for retrieving rows having that
column null?

  SELECT PlayerID
    FROM PlayerLoginSession
    WHERE EndTime IS NULL;

Regards,


--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Index on nullable column
  2006-03-24 22:13 Index on nullable column Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-24 23:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-03-25 12:38   ` Re: Index on nullable column Daniel CAUNE <d.caune@free.fr>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2006-03-24 23:48 UTC (permalink / raw)
  To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql

"Daniel Caune" <daniel.caune@ubisoft.com> writes:
> Is an index on a nullable column useful for retrieving rows having that
> column null?

Nope, because IS NULL isn't an indexable operator.

You can make an end-run around that with a partial index, eg

	create index fooi on foo(f1) where f1 is null

This can be used to satisfy queries using "where f1 is null", but it's
not any good for any other purpose.

If you often do "where f1 is null and something-about-f2",
you might get better mileage with

	create index fooi on foo(f2) where f1 is null

but it's still a very specialized index.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Index on nullable column
  2006-03-24 22:13 Index on nullable column Daniel Caune <daniel.caune@ubisoft.com>
  2006-03-24 23:48 ` Re: Index on nullable column Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-25 12:38   ` Daniel CAUNE <d.caune@free.fr>
  0 siblings, 0 replies; 3+ messages in thread

From: Daniel CAUNE @ 2006-03-25 12:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql

> > Is an index on a nullable column useful for retrieving rows having that
> > column null?
> 
> Nope, because IS NULL isn't an indexable operator.
> 
> You can make an end-run around that with a partial index, eg
> 
> 	create index fooi on foo(f1) where f1 is null
> 
> This can be used to satisfy queries using "where f1 is null", but it's
> not any good for any other purpose.
> 
> If you often do "where f1 is null and something-about-f2",
> you might get better mileage with
> 
> 	create index fooi on foo(f2) where f1 is null
> 
> but it's still a very specialized index.
> 

Thanks Tom.  I will use such an index even if it's very specialized; OLAP world is somewhat full of specialized index anyway... :-)

--
Daniel




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2006-03-25 12:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 22:13 Index on nullable column Daniel Caune <daniel.caune@ubisoft.com>
2006-03-24 23:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-25 12:38   ` Daniel CAUNE <d.caune@free.fr>

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