Re: GIN vs. Partial Indexes

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I thought we fixed this in 8.4.4, but apparently not. In the event that
> you have a GIN index containing a WHERE clause which is sufficiently
> restrictive, PostgreSQL will attempt to use the index even though it
> can't.

We could probably kluge the planner to avoid that case, but in view
of the other issues explained here:
http://developer.postgresql.org/pgdocs/postgres/gin-limit.html
I'm not sure it's worth the trouble. There's nothing the planner can do
to guard against the equivalent issue of non-restrictive queries, ie
there is a WHERE clause but it's something like "array-column contains
empty-array". The fact that the comparison value is empty might not be
known until runtime.

IMO, what's needed is to fix GIN so it doesn't go insane for empty
values or non-restrictive queries, by ensuring there's at least one
index entry for every row. This has been discussed before; see the TODO
section for GIN.

regards, tom lane