Re: GIN vs. Partial Indexes

On Thu, Oct 7, 2010 at 10:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

That seems like it could waste an awful lot of disk space (and
therefore I/O, etc.). No?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company