Thread: ERROR: GIN indexes do not support whole-index scans

ERROR: GIN indexes do not support whole-index scans

From
"Kevin Flanagan"
Date:
<div class="Section1"><p class="MsoNormal">Could anyone advise as to how to avoid this error? I’ll describe the table
andquery below.<p class="MsoNormal"> <p class="MsoNormal">The database contains a table ‘tinytm_segments’, which has
twotext columns, ‘source_text’ and ‘target_text’. These are used to store sentences and their translations. The
languageof the text is specified with typical two-character identifiers (‘en’, ‘fr’ etc.) stored in two further
columns,‘source_lang_code’ and ‘target_lang_code’. Translation in either direction can be stored, so for a given row,
source_textmay contain English and target_text French (with the corresponding values in source_lang_code and
target_lang_code),or the other way round.<p class="MsoNormal"> <p class="MsoNormal">The application needs to search for
(say)French sentences containing a given substring and retrieve any English translation found (or whatever other
languagecombination and direction). To perform better with large datasets, full text indices are defined, such as
these:<pclass="MsoNormal"> <p class="MsoNormal">-- Index English text<p class="MsoNormal">CREATE INDEX
tu_target_text_en_idxON tinytm_segments USING gin(to_tsvector('english', target_text)) where target_lang_code = 'en';<p
class="MsoNormal">CREATEINDEX tu_source_text_en_idx ON tinytm_segments USING gin(to_tsvector('english', source_text))
wheresource_lang_code = 'en';<p class="MsoNormal"> <p class="MsoNormal">-- Index French text<p class="MsoNormal">CREATE
INDEXtu_source_text_fr_idx ON tinytm_segments USING gin(to_tsvector('french', source_text)) where source_lang_code =
'fr';<pclass="MsoNormal">CREATE INDEX tu_target_text_fr_idx ON tinytm_segments USING gin(to_tsvector('french',
target_text))where target_lang_code = 'fr';<p class="MsoNormal"> <p class="MsoNormal">To retrieve (say) sentences that
havebeen translated from French, where the French contains a given substring, a query like this can then be issued:<p
class="MsoNormal"> <pclass="MsoNormal">SELECT * FROM  tinytm_segments WHERE<p class="MsoNormal"
style="text-indent:36.0pt">source_lang_code='fr' AND <p class="MsoNormal"
style="text-indent:36.0pt">to_tsvector('french',source_text) @@ plainto_tsquery('french', 'rien du tout') AND
lower(source_text)LIKE '%rien du tout%'<p class="MsoNormal" style="text-indent:36.0pt"> <p class="MsoNormal">However,
thatwill return sentences translated into whatever language. The error occurs when trying to retrieve only sentences
translatedfrom French into English, using a query like this:<p class="MsoNormal"> <p class="MsoNormal">SELECT * FROM 
tinytm_segmentsWHERE<p class="MsoNormal" style="text-indent:36.0pt">source_lang_code='fr'  AND <p class="MsoNormal"
style="text-indent:36.0pt">to_tsvector('french',source_text) @@ plainto_tsquery('french', 'rien du tout') AND
lower(source_text)LIKE '%rien du tout%'<p class="MsoNormal" style="text-indent:36.0pt"> AND target_lang_code='en'<p
class="MsoNormal"> <pclass="MsoNormal">Why would adding “target_lang_code=’en’” cause this error?<p
class="MsoNormal"> <pclass="MsoNormal">Environment: PostgreSQL 8.4 on Windows (installed with one-click installer),
defaulttext search config used.<p class="MsoNormal"> <p class="MsoNormal">Thanks in advance for any information.<p
class="MsoNormal"> <pclass="MsoNormal">Kevin.<p class="MsoNormal"> </div> 

Re: ERROR: GIN indexes do not support whole-index scans

From
Tom Lane
Date:
"Kevin Flanagan" <kevin-f@linkprior.com> writes:
> Why would adding "target_lang_code='en'" cause this error?

Hard to tell without seeing the index definitions for this table.
Also could we see the EXPLAIN plans for both queries?  (If possible
... I'm not sure whether you'd get this error just from EXPLAINing
the problem query.)

> Environment: PostgreSQL 8.4 on Windows (installed with one-click installer),

8.4.what-exactly?
        regards, tom lane


Re: ERROR: GIN indexes do not support whole-index scans

From
"Kevin Flanagan"
Date:
Ah - you mentioning index definitions has suddenly made it clearer just what
that error message might mean. The source_lang_code and target_lang_code
columns didn't yet each have an index. If I create an index for either one
of them, the error then goes away, I'm guessing because the query processor
can use one index or other to filter table rows before applying the
full-text filter, rather than applying the full-text filter first then
applying the "='code'" filters to the results - which must be what the error
means you can't do.

Strange, though - if I change the "='code'" terms to use LIKE, it works ...
so it obviously can be done without adding another index. 

Still, those two columns both needed an index anyway, and everything then
works just fine, so I shan't worry about that :)

Thank you very much.

Kevin.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: 20 May 2010 22:15
To: Kevin Flanagan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans 

"Kevin Flanagan" <kevin-f@linkprior.com> writes:
> Why would adding "target_lang_code='en'" cause this error?

Hard to tell without seeing the index definitions for this table.
Also could we see the EXPLAIN plans for both queries?  (If possible
... I'm not sure whether you'd get this error just from EXPLAINing
the problem query.)

> Environment: PostgreSQL 8.4 on Windows (installed with one-click
installer),

8.4.what-exactly?
        regards, tom lane