Issue: [Feature extension] Add in language option for postgres Match expressions
Author: alexlatchford
State: closed
Created: 2017-01-05 01:53:59

Hey Charles,

I've got a table I've just added fulltext search to, I've added a GIN index to speed up the query but looks like the `Match` expression in the `playhouse.postgres_ext` library isn't extensible enough to be able to make use of the index unfortunately.

Table structure (simplified for brevity):

``` bash
kb=> \d entity
                    Table "public.entity"
      Column       |            Type             | Modifiers 
-------------------+-----------------------------+-----------
 uuid              | uuid                        | not null
 display_name      | character varying(255)      | not null
Indexes:
    "entity_pkey" PRIMARY KEY, btree (uuid)
    "entity_display_name_idx" btree (display_name)
    "entity_full_text_search" gin (to_tsvector('english'::regconfig, display_name::text))
```

Create index statement used (must specify language, get an error creating index if not. I'm running on Postgres 9.6):

```sql
CREATE INDEX entity_full_text_search ON entity USING gin(to_tsvector('english', display_name));
```

Peewee query:

```python
Entity.select().where(Match(Entity.display_name, prefix))
```

SQL peewee generates:

```sql
kb=> explain analyze SELECT "t1"."uuid", "t1"."display_name" FROM "entity" AS t1 WHERE (to_tsvector("t1"."display_name") @@ to_tsquery('Jes')) LIMIT 50 OFFSET 0;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5820.66 rows=50 width=82) (actual time=14.442..50535.567 rows=47 loops=1)
   ->  Seq Scan on entity t1  (cost=0.00..3402640.35 rows=29229 width=82) (actual time=14.440..50535.486 rows=47 loops=1)
         Filter: (to_tsvector((display_name)::text) @@ to_tsquery('Jes'::text))
         Rows Removed by Filter: 5847315
 Planning time: 0.101 ms
 Execution time: 50535.627 ms
```

SQL needed to use GIN index (Change in `to_tsvector()`):

```sql
kb=> explain analyze SELECT "t1"."uuid", "t1"."display_name" FROM "entity" AS t1 WHERE (to_tsvector('english', "t1"."display_name") @@ to_tsquery('Jes')) LIMIT 50 OFFSET 0;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=31.05..249.70 rows=50 width=82) (actual time=0.060..0.140 rows=47 loops=1)
   ->  Bitmap Heap Scan on entity t1  (cost=31.05..3866.32 rows=877 width=82) (actual time=0.059..0.128 rows=47 loops=1)
         Recheck Cond: (to_tsvector('english'::regconfig, (display_name)::text) @@ to_tsquery('Jes'::text))
         Heap Blocks: exact=47
         ->  Bitmap Index Scan on entity_full_text_search  (cost=0.00..30.83 rows=877 width=0) (actual time=0.048..0.048 rows=47 loops=1)
               Index Cond: (to_tsvector('english'::regconfig, (display_name)::text) @@ to_tsquery('Jes'::text))
 Planning time: 0.219 ms
 Execution time: 0.177 ms
```

Cheers,
Alex

PS. Just using a `SQL()` expression in the meantime :)

Added in df65b09722bb14b884f89235e09709c4f398a7aa - thanks for your patience. Language is now an optional extra parameter to the `match` and `Match` functions.

```python
Entity.select().where(Match(Entity.display_name, prefix, 'english'))
# or
Entity.select().where(Entity.display_name.match(prefix, 'english'))
```

Excellent! Thanks @coleifer 🥇