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

Sorry, mistyped the query causing the problem. It is:

select *
from maps, features
where maps.query @@ features.tags_vector;

Thanks,

Charlie

Charlie Savage wrote:
> 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
>
>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org