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

[Charlie Savage <cfis@xxxxxxxxxxxx>]

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

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature