Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans
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:t...@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 kevi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ERROR: GIN indexes do not support whole-index scans
Could anyone advise as to how to avoid this error? I'll describe the table
and query below.
The database contains a table 'tinytm_segments', which has two text columns,
'source_text' and 'target_text'. These are used to store sentences and their
translations. The language of 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_text may contain English and
target_text French (with the corresponding values in source_lang_code and
target_lang_code), or the other way round.
The application needs to search for (say) French sentences containing a
given substring and retrieve any English translation found (or whatever
other language combination and direction). To perform better with large
datasets, full text indices are defined, such as these:
-- Index English text
CREATE INDEX tu_target_text_en_idx ON tinytm_segments USING
gin(to_tsvector('english', target_text)) where target_lang_code = 'en';
CREATE INDEX tu_source_text_en_idx ON tinytm_segments USING
gin(to_tsvector('english', source_text)) where source_lang_code = 'en';
-- Index French text
CREATE INDEX tu_source_text_fr_idx ON tinytm_segments USING
gin(to_tsvector('french', source_text)) where source_lang_code = 'fr';
CREATE INDEX tu_target_text_fr_idx ON tinytm_segments USING
gin(to_tsvector('french', target_text)) where target_lang_code = 'fr';
To retrieve (say) sentences that have been translated from French, where the
French contains a given substring, a query like this can then be issued:
SELECT * FROM tinytm_segments WHERE
source_lang_code='fr' AND
to_tsvector('french', source_text) @@ plainto_tsquery('french', 'rien du
tout') AND lower(source_text) LIKE '%rien du tout%'
However, that will return sentences translated into whatever language. The
error occurs when trying to retrieve only sentences translated from French
into English, using a query like this:
SELECT * FROM tinytm_segments WHERE
source_lang_code='fr' AND
to_tsvector('french', source_text) @@ plainto_tsquery('french', 'rien du
tout') AND lower(source_text) LIKE '%rien du tout%'
AND target_lang_code='en'
Why would adding target_lang_code='en' cause this error?
Environment: PostgreSQL 8.4 on Windows (installed with one-click installer),
default text search config used.
Thanks in advance for any information.
Kevin.
Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans
Kevin Flanagan kevi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
