Support fulltext search using indexes in PostgreSQL

[pimeys]

This requires some ts_vector magic.

[janpio]

An example for index usage in PostgreSQL is in #8950 (comment)

[janpio]

Internal discusssion: https://prisma-company.slack.com/archives/C02FZENHD4N/p1639421790039900?thread_ts=1639420382.038800&cid=C02FZENHD4N

[matthewmueller]

Can someone share why these extensions are necessary? I'm surprised that adding the index:

CREATE INDEX book_content_index ON "Book" USING GIN (to_tsvector('english', "content"));

Doesn't just work. In my testing:

explain SELECT "public"."Book"."id", "public"."Book"."title", "public"."Book"."content" FROM "public"."Book" WHERE to_tsvector("public"."Book"."content") @@ to_tsquery('great') OFFSET 0

It looks like it still does a sequential scan (1-by-1):

Seq Scan on "Book"  (cost=0.00..29.65 rows=1 width=56)
  Filter: (to_tsvector(content) @@ to_tsquery('great'::text))

[AzSiAz]

I think it's because you are using to_tsvector('english', "content"), the index is defined with english type, but you are not using it in your query

The query should be

EXPLAIN
SELECT
	"public"."Book"."id",
	"public"."Book"."title",
	"public"."Book"."content"
FROM
	"public"."Book"
WHERE
	to_tsvector('english', "public"."Book"."content") @@ to_tsquery('great')
	OFFSET 0

That's also why (here) I added a point about language being dynamic in @prisma/client

[matthewmueller]

It worked! Beautiful @AzSiAz!

Took my query (above) from 9s to 280ms.

Bitmap Heap Scan on "Book"  (cost=16.25..20.76 rows=1 width=56)
  Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('great'::text))
  ->  Bitmap Index Scan on book_content_index  (cost=0.00..16.25 rows=1 width=0)
      Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('great'::text))

We'll take a look at your original issue, thanks again!

[floelhoeffel]

Hello everyone 👋

We are starting design work to improve Prisma's support for Full Text Search (FTS)!

If you would like to help, please tell us about your needs via a short FTS user research survey.

Thank you!

[younes-alouani]

@floelhoeffel , I think Django ORM handles FTS very well. And Django is used by big companies, it can be good to get inspiration from its ORM code source.

[deep-randhawa]

Any updates here? Or is there a timeline to when this FTS feature will be completed?

[gruckion]

Time line guys? Come on it’s been 2 years. Where can we find the timeline for indexes on fts

[janpio]

Generally GIN indexes (and similar) can already be configured via @@index: https://www.prisma.io/docs/orm/prisma-schema/data-model/indexes#configuring-the-access-type-of-indexes-with-type-postgresql But the usage in the full text search API (behind fullTextSearch preview feature) is currently problematic: #8950

[winterprime]

Flipping my whole database to MySQL because of this.