Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux