Speed up searchcaster full text search about farcaster-indexer HOT 5 CLOSED

Love this!

ilike is case-insensitive. is the query that uses to_tsvector @@ to_tsquery also case-insensitive?

Edit: It IS case-insensitive! I tested it on my own DB on a table with 700k+ rows and got major improvements:

Before - ILIKE: Execution Time: 3617.160 ms
After - to_tsquery: Execution Time: 25.707 ms

from farcaster-indexer.

Here's a query that takes a very long time and this is due to me adding the ORDER subquery

select * from casts WHERE text ILIKE '%of%' AND deleted=false order by reactions_count;

from farcaster-indexer.

• To optimize this call: https://github.com/gskril/searchcaster/blob/d1baa79242e7a3e1db3183a3c8b27c9b30bcf832/src/pages/api/search.js#L158-L179

which I'm simulating as follows

SELECT *
FROM casts
WHERE to_tsvector('english', text) @@ to_tsquery('english', 'searchcaster') AND deleted=false
ORDER BY reactions_count;

where the "reactions_count" slows down the query significantly. Without indexes:

postgres=# SELECT * FROM casts WHERE to_tsvector('english', text) @@ to_tsquery('english', 'searchcaster') AND deleted=false ORDER BY reactions_count;
Time: 2110.693 ms (00:02.111)

So I'm creating an index over all fields that could be queried as follows

CREATE INDEX casts_text_gin_index ON casts USING gin(to_tsvector('english', text));
CREATE INDEX casts_published_at_index ON casts USING btree(published_at, deleted, recasts_count, replies_count, watches_count, author_username);

New performance with indexes

postgres=# \timing
Timing is on.
postgres=# SELECT * FROM casts WHERE to_tsvector('english', text) @@ to_tsquery('english', 'searchcaster') AND deleted=false ORDER BY reactions_count;
Time: 29.832 ms

from farcaster-indexer.

Haven‘t found anything definitive in the docs, we can do tests though. But I think it goes even beyond understanding capitalization as it also understands verb conjugations etc

Converting tokens into lexemes. A lexeme is a string, just like a token, but it has been normalized so that different forms of the same word are made alike. For example, normalization almost always includes folding upper-case letters to lower-case, and often involves removal of suffixes (such as s or es in English).

https://www.postgresql.org/docs/9.4/textsearch-intro.html

from farcaster-indexer.

Yes! That looks to be the case. Funny that the docs say "almost always includes".

Anecdotally, it was case-insensitive when I tried

from farcaster-indexer.