On Mon, Oct 18, 2010 at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@jamponi.net> writes:
>> CREATE INDEX foo_idx ON t USING GIN (alternatecodes) WHERE
>> alternatecodes IS NOT NULL;
>> SELECT * FROM t WHERE alternatecodes IS NOT NULL;
>> ERROR: GIN indexes do not support whole-index scans
>
> Yep, this is a known issue. It's going to take major surgery on GIN to
> fix it, so don't hold your breath. In the particular case, what good do
> you think the WHERE clause is doing anyway? GIN won't index nulls at
> all ... which indeed is an aspect of the underlying issue --- see recent
> discussions, eg here:
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg00521.php
OK, so GIN doesn't index NULLs. I guess the "IS NOT NULL" part comes
about as a habit - that particular column is fairly sparse. However,
I'm honestly quite surprised at two things:
1. if GIN indexes ignore NULLs, then either it should grump when one
specifics "WHERE ... IS NOT NULL" or it should be treated as a no-op
2. (and this is by far the more surprising) that the /presence/ of an
INDEX can *break* a SELECT. It's not that the engine ignores the index
- that would be reasonable - but that I can't issue a SELECT with a
WHERE statement that matches the same as the index.
However, I see that this also surprised Josh Berkus, and not that long
ago (11 days!), so I'll just shush.
Thanks!
--
Jon