ERROR: GIN indexes do not support whole-index scans
От | Kevin Flanagan |
---|---|
Тема | ERROR: GIN indexes do not support whole-index scans |
Дата | |
Msg-id | 021d01caf85b$3a567f20$af037d60$@com обсуждение исходный текст |
Ответы |
Re: ERROR: GIN indexes do not support whole-index scans
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
<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>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: [RFC][PATCH]: CRC32 is limiting at COPY/CTAS/INSERT ... SELECT + speeding it up
Следующее
От: Stephen FrostДата:
Сообщение: Re: [RFC][PATCH]: CRC32 is limiting at COPY/CTAS/INSERT ... SELECT + speeding it up