PostgreSQL GIN index not used when ts_query language is fetched from a column

Поиск
Список
Период
Сортировка
От Jaap Roes
Тема PostgreSQL GIN index not used when ts_query language is fetched from a column
Дата
Msg-id 25AE6D3E-2DEA-4B47-83C5-FE94F94D00A1@leukeleu.nl
обсуждение исходный текст
Ответы Re: PostgreSQL GIN index not used when ts_query language is fetched from a column  (Jaap Roes <jroes@leukeleu.nl>)
Re: PostgreSQL GIN index not used when ts_query language is fetched from a column  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general
I've got a table that stores some multilingual content:

    CREATE TABLE search (
      content text NOT NULL,
      language regconfig NOT NULL,
      fulltext tsvector
    );
    CREATE INDEX search_fulltext ON search USING GIN(fulltext);

    INSERT INTO search (language, content) VALUES
      ('dutch', 'Als achter vliegen vliegen vliegen vliegen vliegen vliegen achterna'),
      ('dutch', 'Langs de koele kali liep een kale koeli met een kilo kali op zijn kale koeli-kop.'),
      ('dutch', 'Moeder sneed zeven scheve sneden brood'),
      ('english', 'I saw Susie sitting in a shoe shine shop. Where she sits she shines, and where she shines she
sits.'),
      ('english', 'How can a clam cram in a clean cream can?'),
      ('english', 'Can you can a can as a canner can can a can?');

    UPDATE search SET fulltext = to_tsvector(language, content);

To make sure I always search in the correct language I use these queries:

    SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
    (1 row)

    SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'vlieg');
    (1 row)

Because hardcoding the language doesn't give the correct results:

    SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine');
    (0 rows)

    SELECT FROM search WHERE fulltext @@ to_tsquery('english', 'vlieg');
    (0 rows)

The problem however is that PostgreSQL doesn't use the GIN index when using the first set of queries and instead does a
sequentialscan: 

(Note: I've disabled scanning using SET enable_seqscan = OFF; for these examples because of the low amount of rows)

    EXPLAIN ANALYZE SELECT * FROM search WHERE fulltext @@ to_tsquery(language, 'shine’);

    Seq Scan on search  (cost=0.00..17.35 rows=2 width=136) (actual time=0.040..0.044 rows=1 loops=1)
        Filter: (fulltext @@ to_tsquery(language, 'shine'::text))
        Rows Removed by Filter: 5
    Planning time: 0.039 ms
    Execution time: 0.064 ms

While it does when hardcoding a language:

    EXPLAIN ANALYZE SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'vlieg');
    Bitmap Heap Scan on search  (cost=12.63..23.66 rows=82 width=0) (actual time=0.044..0.044 rows=1 loops=1)
      Recheck Cond: (fulltext @@ '''vlieg'''::tsquery)
      Heap Blocks: exact=1
        ->  Bitmap Index Scan on search_fulltext  (cost=0.00..12.61 rows=82 width=0) (actual time=0.037..0.037 rows=1
loops=1)
          Index Cond: (fulltext @@ '''vlieg'''::tsquery)
    Planning time: 0.128 ms
    Execution time: 0.065 ms

So my question is: Is it at all possible to use a column in the ts_query to use the correct language config and still
havePostgres use the GIN index? 

I’ve tried this on both PostgreSQL 9.4 and 9.5.

Obviously the real table has a lot more rows, so here's the execution plan from the real table:

Using a column for language config:

    Seq Scan on search  (cost=0.00..8727.25 rows=188 width=0) (actual time=0.725..352.307 rows=1689 loops=1)
      Filter: (fulltext @@ to_tsquery(language_config, 'example'::text))
      Rows Removed by Filter: 35928
    Planning time: 0.053 ms
    Execution time: 352.915 ms

When hardcoding the language:

    Bitmap Heap Scan on search  (cost=28.65..4088.92 rows=1633 width=0) (actual time=0.514..10.475 rows=1684 loops=1)
      Recheck Cond: (fulltext @@ '''exampl'''::tsquery)
      Heap Blocks: exact=1522
        ->  Bitmap Index Scan on search_fulltext  (cost=0.00..28.24 rows=1633 width=0) (actual time=0.333..0.333
rows=1684loops=1)         
          Index Cond: (fulltext @@ '''exampl'''::tsquery)
    Planning time: 0.180 ms
    Execution time: 10.564 ms

Note: I previously asked this on stackexhange
(http://dba.stackexchange.com/questions/149765/postgresql-gin-index-not-used-when-ts-query-language-is-fetched-from-a-column)
butI’m assuming there are more knowledgeable people on this mailing list ;-) 

В списке pgsql-general по дате отправления:

Предыдущее
От: Leonardo M. Ramé
Дата:
Сообщение: ERROR: could not read block 4 ...
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Request to share information regarding postgresql pg_xlog file.