Re: unexpected query failure: ERROR: GIN indexes do not support whole-index scans

On Mon, Oct 18, 2010 at 6:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jon Nelson <jnelson+pgsql(at)jamponi(dot)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