On Mon, Oct 18, 2010 at 6:01 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
