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.0 on 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 LOOP
r := ( 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=1 width=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.24 rows=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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
