From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | sarlav kumar <sarlavk(at)yahoo(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: difference between not null and <> null |
Date: | 2004-11-23 21:59:03 |
Message-ID: | 580.1101247143@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
sarlav kumar <sarlavk(at)yahoo(dot)com> writes:
> Is there a difference between the usages of NOT NULL and <> NULL??
They are not even remotely the same thing. = NULL and <> NULL are never
correct (they both always yield NULL). You want IS NULL or IS NOT NULL
instead.
> Do the following in anyway affect the use of index scan?
> 1) use of NOT NULL
> 2) use of now() instead of the actual date
NULL tests aren't currently indexable (maybe someday they will be), but
if you really need it, you can fake it by creating a partial index over
just the null or nonnull rows.
"timestampcol = now()" is fine, but something like
"timestampcol > now() - interval '1 day'" usually won't get indexed,
because the planner thinks it will scan too much of the table.
See the pgsql-performance archives for more discussion and workarounds.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Pradeep Kumar | 2004-11-24 05:30:35 | Replacing old tuples on insert |
Previous Message | Fred Wohlfert | 2004-11-23 21:36:07 | Converting file -Invalid data format on input DATE |