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

Question

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 sequential scan:

(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
(5 rows)

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
(7 rows)

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 have Postgres use the GIN index?

I'm using PostgreSQL 9.4.

EDIT:

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=1684 loops=1)
        Index Cond: (fulltext @@ '''exampl'''::tsquery)
Planning time: 0.180 ms
Execution time: 10.564 ms

EDIT #2

Tried it with Postgres 9.5, same result

Answer 1

I suggest a solution with partial expression indexes:

CREATE TABLE search (
   search_id serial PRIMARY KEY
 , language  regconfig NOT NULL  -- order of columns matters a bit
 , content   text NOT NULL
   --  *no* redundant fulltext tsvector
);

No redundant fulltext column - makes the table smaller, which helps overall performance.
Create one partial expression index for every relevant language:

CREATE INDEX search_fulltext_dutch ON search USING GIN(to_tsvector('dutch', content))
WHERE language = 'dutch'::regconfig;
CREATE INDEX search_fulltext_english ON search USING GIN(to_tsvector('english', content))
WHERE language = 'english'::regconfig;
-- more?

All of the partial indexes together are only about as big as your total index.

Then match index conditions in the query:

SELECT * FROM search  -- does not return useless column fulltext now
WHERE  language = 'dutch'::regconfig  -- match partial index condition
AND    to_tsvector('dutch', content) @@ to_tsquery('dutch', 'vliegen')

UNION ALL
SELECT * FROM search
WHERE  language = 'english'::regconfig
AND    to_tsvector('english', content) @@ to_tsquery('english', 'vliegen');

-- more?

You get bitmap index or index scans this way.
Another index on language may be useful for other purposes, this query does not need it.

Answer 2

@3manuek got me thinking... what if localise the query beforehand so the query isn't row dependent. So I came up with this:

SELECT 
  *
FROM 
  search s 
LEFT JOIN (
  SELECT 'english'::regconfig AS language, to_tsquery('english', 'vliegen') as q
  UNION ALL SELECT 'dutch'::regconfig AS language, to_tsquery('dutch', 'vliegen') as q
  UNION ALL SELECT 'simple'::regconfig AS language, to_tsquery('simple', 'vliegen') as q
) q ON (s.language=q.language) WHERE fulltext @@ q;

The queryplan looks like this (on the real database):

Nested Loop  (cost=205.36..1327.05 rows=188 width=1590) (actual time=3.726..7.045 rows=16 loops=1)
  ->  Append  (cost=0.00..0.06 rows=3 width=36) (actual time=0.001..0.006 rows=3 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
  ->  Bitmap Heap Scan on search s  (cost=205.36..441.70 rows=63 width=1554) (actual time=2.323..2.331 rows=5 loops=3)
        Recheck Cond: ((fulltext @@ ('''vliegen'''::tsquery)) AND ((language)::oid = (('english'::regconfig))::oid))
        Heap Blocks: exact=16
        ->  BitmapAnd  (cost=205.36..205.36 rows=63 width=0) (actual time=2.316..2.316 rows=0 loops=3)
              ->  Bitmap Index Scan on search_fulltext  (cost=0.00..17.41 rows=188 width=0) (actual time=0.022..0.022 rows=9 loops=3)
                    Index Cond: (fulltext @@ ('''vliegen'''::tsquery))
              ->  Bitmap Index Scan on search_language  (cost=0.00..187.67 rows=12539 width=0) (actual time=2.284..2.284 rows=12539 loops=3)
                    Index Cond: ((language)::oid = (('english'::regconfig))::oid)
Planning time: 0.234 ms
Execution time: 7.088 ms

It seems to work correctly, but I'm not super confident about it.

EDIT Updated to use UNION ALL instead of UNION, this removes the need for unique/sort in the subquery

EDIT 2 It seems that at one point I created an index on language which is also used by the query planner:

CREATE INDEX search_language ON search USING BTREE(language);

This helps out this query a bit.

EDIT 3 Using LEFT JOIN which might technically be more correct, and made the query match the query plan more closely

Answer 3

It has sense that Pg is forcing a seqscan when querying the language column using to_tsquery as every iteration needs the value of the language columns of all the examined rows.

For this particular case, you may want to change the way that you are querying:

so=# EXPLAIN ANALYZE SELECT * FROM search WHERE 
to_tsvector(language,fulltext::text) @@ 'shine'::tsquery;

As you are dynamically querying using language, you may want to issue to_tsvector on the fly. I tested this index and it is a bit faster than indexing the tsvector. You can either do this over content, but the parsing will take a bit extra time (depending on the size of the text, the executions may vary a lot).

CREATE EXTENSION btree_gin;
CREATE INDEX search_fulltext_new ON search USING 
GIN(to_tsvector(language,fulltext::text), language);

Here is the explain:

so=# EXPLAIN ANALYZE SELECT * FROM search WHERE 
to_tsvector(language,fulltext::text) @@ 'shine'::tsquery;
Bitmap Heap Scan on search  (cost=12.95..410.41 rows=123 width=168) (actual time=0.652..9.256 rows=4096 loops=1)
   Recheck Cond: (to_tsvector(language, (fulltext)::text) @@ '''shine'''::tsquery)
   Heap Blocks: exact=512
    ->  Bitmap Index Scan on search_fulltext_new  (cost=0.00..12.92 rows=123 width=0) (actual time=0.584..0.584 rows=4096 loops=1)
     Index Cond: (to_tsvector(language, (fulltext)::text) @@ '''shine'''::tsquery)
 Planning time: 0.164 ms
 Execution time: 10.416 ms

Also it will allow you to filter by language:

so=# EXPLAIN ANALYZE SELECT * FROM search WHERE 
to_tsvector(language,fulltext::text) @@ 'shine'::tsquery AND language = 
'english'::regconfig;

What I could recommend depending on the value distribution, is to partition the table by language (this is for further optimization, not to fix this issue). By doing that, you won't need to do this conversion on the fly and the language filtering will be done by constraint exclusion instead, avoiding any seqscan over the parent table.

Answer 4

Another option here is to join pg_ts_config or just joining on a custom list of languages. It is odd that using the language column doesn't hit the index while this approach does, but that is the case in my experience.

So, instead of:

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

You can do:

SELECT s.*
FROM search s
JOIN pg_ts_config c ON c.cfgname::regconfig = s.language
WHERE fulltext @@ to_tsquery(c.cfgname::regconfig, 'shine');