From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Howansky <alex@wankwood.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] how to tell the difference between empty field and null field
Date: Mon, 13 Dec 1999 02:29:23 -0500
Message-ID: <8370.945070163@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.20.9912122315040.20864-100000@net-srv-0001.bvrd.com>
Alex Howansky <alex@wankwood.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, alex@wankwood.com
Subject: Re: [SQL] how to tell the difference between empty field and null field
In-Reply-To: <8370.945070163@sss.pgh.pa.us>
References: <Pine.LNX.4.20.9912122315040.20864-100000@net-srv-0001.bvrd.com> <8370.945070163@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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