Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id AAA73043 for ; Mon, 13 Dec 1999 00:14:53 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id AAA07973; Mon, 13 Dec 1999 00:14:20 -0500 (EST) To: Alex Howansky cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] how to tell the difference between empty field and null field In-reply-to: Your message of Sun, 12 Dec 1999 22:17:01 -0600 (CST) Date: Mon, 13 Dec 1999 00:14:20 -0500 Message-ID: <7971.945062060@sss.pgh.pa.us> From: Tom Lane Alex Howansky writes: > select * from users where domain = '' or domain = null; OK, that'll work, but if you'll pardon a nitpick: "= NULL" is not standard, it is Microsoft brain damage. "IS NULL" is standard. > Here's my question: if I have a zillion records in this table, and > it's indexed by user+domain, how can I run this query without losing > the benefit of the index? An index on (user, domain) is perfectly useless for the above query, because the user field isn't mentioned anywhere in the query. An index on domain alone could be used, though, and should be pretty effective. (We do have some performance problems if you get into dozens of OR terms, but for just a couple, no sweat.) As a rule of thumb, multi-column indexes are quite inflexible, and you will not find that they are worth their cost of upkeep unless you know that you have a specific kind of query you use a *lot* that can exploit the multi-column index. Not only that, but that the heavily used query is significantly faster than it'd be if it only had an index on the first column named in the multi-column index. In most scenarios, the first column gives you most of the gain and any extra columns are marginal. In short, unless you've done careful analysis and testing, you should not make an index on (user, domain) but two indexes on user and domain separately. The latter setup will be a lot more flexible in terms of being reasonably quick for a variety of queries. regards, tom lane