ERROR: Gin doesn't support full scan due to it's awful inefficiency

From: Charlie Savage <cfis(at)savagexi(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ERROR: Gin doesn't support full scan due to it's awful inefficiency
Date: 2006-09-05 22:23:25
Message-ID: 44FDF8DD.9000703@savagexi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've run across another GIN index issue - using postgresql 8.1.4 on
Window/Linux with the GIN/tsearch2 patch.

I have two tables like this:

CREATE TABLE maps
(
id serial,
query tsearch2.tsquery
)

CREATE TABLE features
(
id serial,
vector tsearch2.tsvector
)

CREATE INDEX idx_features_tags_vector ON features USING gin (tags_vector);

Where maps.query contains cached tsquery (they are cached for
performance reasons).

When I run this query:

select *
from maps, features
where to_tsquery('test') @@ features.tags_vector

I get this error:

ERROR: Gin doesn't support full scan due to it's awful inefficiency

Here is explain (from a very small test database):

Nested Loop (cost=0.00..1878.71 rows=370 width=208)
-> Seq Scan on maps (cost=0.00..14.80 rows=480 width=136)
-> Index Scan using idx_features_tags_vector on features
(cost=0.00..3.87 rows=1 width=72)
Index Cond: ("outer".query @@ features.tags_vector)

I thought that this would solve my problem:

set enable_indexscan to off;

But it does not.

Interestingly, this does work:

select *
from features
where to_tsquery('test') @@ features.tags_vector;

Explain:

Index Scan using idx_features_tags_vector on features (cost=0.00..3.87
rows=1 width=72)
Index Cond: ('''test'''::tsquery @@ tags_vector)

At first I thought the issue was that you couldn't use an Index Scan on
gin index, but that now seems like an incorrect conclusion.

So, two things:

1. How do I work around this issue?
2. Seems like postgresql should be smart enough to pick a query that
will run.

Thanks,

Charlie

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Charlie Savage 2006-09-05 22:27:37 Re: ERROR: Gin doesn't support full scan due to it's awful
Previous Message Ron Johnson 2006-09-05 21:55:42 Re: Syntax for converting double to a timestamp