From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Using index for IS NULL query
Date: Tue, 11 Nov 2008 21:50:49 +0100
Message-ID: <20081111205049.GA15899@tux> (raw)
In-Reply-To: <12244.1226436315@sss.pgh.pa.us>
References: <gfcprb$m40$1@news.hub.org>
<12244.1226436315@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> schrieb:
> "Andrus" <kobruleht2@hot.ee> writes:
> > Index is not used for
> > is null
>
> > How to fix ?
>
> Update to something newer than 8.1 (specifically, you'll need 8.3).
Right. For my example in the other mail:
test=*# create index idx_foo on foo(i);
CREATE INDEX
test=*# explain analyse select * from foo where i is null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=95.11..4780.49 rows=5000 width=8) (actual time=0.052..0.053 rows=1 loops=1)
Recheck Cond: (i IS NULL)
-> Bitmap Index Scan on idx_foo (cost=0.00..93.86 rows=5000 width=0) (actual time=0.047..0.047 rows=1 loops=1)
Index Cond: (i IS NULL)
Total runtime: 0.076 ms
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
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: akretschmer@spamfence.net
Subject: Re: Using index for IS NULL query
In-Reply-To: <20081111205049.GA15899@tux>
* 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