[Tobias Brox - Tue at 11:02:07AM +0800]
> I read in the manual today:
>
> Indexes are not used for IS NULL clauses by default. The best way to use
> indexes in such cases is to create a partial index using an IS NULL
> predicate.
I have summarized this thread in a postgresql doc user commen
> 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 ...
This is wonderful, it seems like mo
Greg Stark <[EMAIL PROTECTED]> writes:
> then it would be a candidate because the ORDER BY or the other > 0 make the
> index look relevant. But I don't think (again I'm not 100% sure) that the
> partial index WHERE clause is considered in picking which indexes to consider.
Nope, the partial index
Tobias Brox <[EMAIL PROTECTED]> writes:
> [Bruno Wolff III - Mon at 10:36:33PM -0500]
> > You want something like this:
> > CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;
>
> Oh, cool. I wasn't aware that this is possible. This would probably help
> us a lot of places. :-)
Yeah it's a cool
Bruno Wolff III <[EMAIL PROTECTED]> 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 b
On Tue, May 31, 2005 at 11:31:58 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox]
> > test=# set enable_seqscan=off;
>
> [Bruno Wolff III - Mon at 10:16:53PM -0500]
> > It isn't surprising that an index wasn't used since a sequential scan is
> > going to be faster in your test case
On Tue, May 31, 2005 at 11:21:20 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox - Tue at 11:02:07AM +0800]
> > test=# explain select * from mock where b is NULL;
> > QUERY PLAN
> >
[Bruno Wolff III - Mon at 10:36:33PM -0500]
> You want something like this:
> CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;
Oh, cool. I wasn't aware that this is possible. This would probably help
us a lot of places. :-)
--
Tobias Brox, Beijing
---(end of broadcas
[Tobias Brox]
> test=# set enable_seqscan=off;
[Bruno Wolff III - Mon at 10:16:53PM -0500]
> It isn't surprising that an index wasn't used since a sequential scan is
> going to be faster in your test case.
>
> If you want to test this out, you to want use realistically sized tables.
Wrong. In
[Tobias Brox - Tue at 11:02:07AM +0800]
> test=# explain select * from mock where b is NULL;
> QUERY PLAN
>
> Seq Scan on mock (cost=1.00..10020.00 rows=6 widt
On Tue, May 31, 2005 at 11:02:07 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> I read in the manual today:
>
> Indexes are not used for IS NULL clauses by default. The best way to use
> indexes in such cases is to create a partial index using an IS NULL
> predicate.
>
> This is from t
11 matches
Mail list logo
