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 00:14:20 -0500
Message-ID: <7971.945062060@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.20.9912122157220.18303-100000@net-srv-0001.bvrd.com>
Alex Howansky <alex@wankwood.com> 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
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: <7971.945062060@sss.pgh.pa.us>
References: <Pine.LNX.4.20.9912122157220.18303-100000@net-srv-0001.bvrd.com> <7971.945062060@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