Re: [SQL] how to tell the difference between empty field and null field

Alex Howansky <alex(at)wankwood(dot)com> writes:
> My main concern is with the use of the 'or' in the query. My
> experience is mostly with Progress -- it (at least the ancient version
> that I'm used to) can't utilize the index on a field _at all_ if your
> query uses an 'or' on that field. As a result, I've become extremely
> cautious about doing this with Postgres. You seem to be saying that
> it's no a big deal -- that the index will still be utilized and that
> performance will not suffer significantly. Is this something that I
> can finally forget worrying about?

In current sources it definitely works. For example,

regression=> explain select * from tenk1 where unique1 = 33;
NOTICE: QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1 (cost=9.00 rows=100 width=148)

EXPLAIN

regression=> explain select * from tenk1 where unique1 = 33 or unique1 = 44;
NOTICE: QUERY PLAN:

Index Scan using tenk1_unique1, tenk1_unique1 on tenk1 (cost=18.00 rows=200 width=148)

EXPLAIN

Notice that the index is mentioned twice in the second EXPLAIN. That
means there are actually two index scans being done: the first pulls out
the entries matching the first OR subclause, and the second gets the
entries matching the other clause. (Yes, the right thing happens for
tuples that match both, although this isn't possible in the above query.)

Version 6.5.* is a little flakier about whether it will apply multiple
index scans for an OR where-clause; the capability is in there but I
don't trust the optimizer to recognize it's a good strategy all the
times it should. Check and see what you get from EXPLAIN.

BTW, I think I spoke too soon in claiming that IS NULL would work as
an index OR clause; it doesn't seem to, in some quick tests. I'll have
to see if anything can be done about that...

regards, tom lane