From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index on a NULL-value
Date: Tue, 31 May 2005 00:18:33 -0400
Message-ID: <7923.1117513113@sss.pgh.pa.us> (raw)
In-Reply-To: <20050531033633.GA20015@wolff.to>
References: <20050531030207.GD26100@tobias.exoweb.net>
<20050531032119.GH26100@tobias.exoweb.net>
<20050531033633.GA20015@wolff.to>
Bruno Wolff III <bruno@wolff.to> writes:
> Looked back at your first example and saw that you didn't use a partial
> index which is why you had to contort things to make it possible to
> use an indexed search.
FWIW, there is code in CVS tip that recognizes the connection between
an index on a boolean expression and a WHERE clause testing that
expression. It's not quite perfect --- using Tobias' example I see
regression=# explain select * from mock where b is NULL;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using b_is_null on mock (cost=0.00..51.67 rows=10 width=8)
Index Cond: ((b IS NULL) = true)
Filter: (b IS NULL)
(3 rows)
so there's a useless filter condition still being generated. But it
gets the job done as far as using the index, anyway.
> You want something like this:
> CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;
I think best practice for something like this is to make the partial
index's columns be something different from what the partial condition
tests. Done as above, every actual index entry will be a null, so the
entry contents are just dead weight. Instead do, say,
CREATE INDEX b_is_null ON mock(a) WHERE b IS NULL;
where a is chosen as a column that you frequently also test in
conjunction with "b IS NULL". That is, the above index can efficiently
handle queries like
... WHERE a = 42 AND b IS NULL ...
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruno@wolff.to, tobias@nordicbet.com
Subject: Re: Index on a NULL-value
In-Reply-To: <7923.1117513113@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