GIN vs. Partial Indexes - Mailing list pgsql-hackers

All,

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.  Since this is completely out of the control of the user, it
effectively prohibits using partial GIN indexes:

Setup:

postgres=# select version();                                                              version


--------------------------------------------------------------------------------------------------------------------------------------PostgreSQL
9.0.0on i386-apple-darwin9.8.0, compiled by GCC
 
i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465), 32-bit

postgres=# create table gin_test ( id serial not null primary key, type
CREATE TABLE

DO $$
DECLARE i INT := 1;r INT;qts tsvector;del BOOLEAN;
BEGIN

qts := to_tsvector('The most anticipated PostgreSQL version in five
years has been released. With built-in binary replication and over a
dozen new major features, PostgreSQL 9.0 has compelling reasons to
upgrade or migrate for every database user and developer.');

WHILE i < 1000 LOOPr := ( random() * 20 )::INT;INSERT INTO gin_test ( "type", deleted, some_ts )VALUES ( r,    ( r % 2
)= 0,    qts );i := i + 1;
 
END LOOP;

END;$$;

create index gin_test_type ON gin_test("type");
create index gin_test_text ON gin_test USING GIN ( some_ts)WHERE deleted = FALSE AND "type" = 1;

postgres=# SELECT COUNT(*) from gin_test WHERE deleted = FALSE and
"type" = 1;
ERROR:  GIN indexes do not support whole-index scans

postgres-# EXPLAIN SELECT COUNT(*) from gin_test WHERE deleted = FALSE
and "type" = 1;                                    QUERY PLAN

------------------------------------------------------------------------------------Aggregate  (cost=54.01..54.02
rows=1width=0)  ->  Bitmap Heap Scan on gin_test  (cost=12.38..53.95 rows=24 width=0)        Recheck Cond: ((NOT
deleted)AND (type = 1))        ->  Bitmap Index Scan on gin_test_text  (cost=0.00..12.37
 
rows=24 width=0)
(4 rows)


I find the above error interesting, because: (a) I didn't actually
select the some_ts column, and (b) I can do an actual TS search which
hits the whole index with no problem:

postgres=# SELECT COUNT(*) from gin_test WHERE some_ts @@
to_tsquery('replication');
count
-------  999


Note that if I add the perfect index for that query, it works:

postgres=# create index gin_test_type_undeleted on gin_test("type")
where not deleted;
CREATE INDEX

postgres=# SELECT COUNT(*) from gin_test WHERE deleted = FALSE and
"type" = 1;                                               count
-------   46
(1 row)

postgres=# EXPLAIN
                                         SELECT COUNT(*) from gin_test
WHERE deleted = FALSE and "type" = 1;                                        QUERY PLAN

---------------------------------------------------------------------------------------------Aggregate
(cost=46.23..46.24rows=1 width=0)  ->  Bitmap Heap Scan on gin_test  (cost=4.60..46.17 rows=24 width=0)        Recheck
Cond:((type = 1) AND (NOT deleted))        ->  Bitmap Index Scan on gin_test_type_undeleted
 
(cost=0.00..4.60 rows=24 width=0)              Index Cond: (type = 1)
(5 rows)


Clearly the answer here seems to be that our planner should not pick GIN
indexes for any query in which the indexed column is not referenced.  Is
that practical to implement?

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com