Improving Postgres text search speed • Hacker News Board

Re: Improving Postgres text search speed

#11

Post by jxf » Sat, Aug 13, 2022, 2:21 AM UTC

eric4smith wrote: post #6

PostgreSQL text search is awesome - for English and Roman type languages. But Asian languages such as Thai, Japanese, Korean, etc are not going to work at all. PostgreSQL is weird about joins. Joining on certain columns could be super fast but others dog slow. And this can flip depending on size of table and this index Cardinality. That’s why it’s important on databases that grow quickly to check the performance of e…

> But Asian languages such as Thai, Japanese, Korean, etc are not going to work at all.

I understand that there are likely to be many significant differences in graphemes in these languages from Roman text but I'm not familiar enough with any Asian language to construct an example. Can you give an illustrative example that explains why the search doesn't work as well (or at all) in those cases?

Re: Improving Postgres text search speed

#12

Post by wahnfrieden » Sat, Aug 13, 2022, 2:34 AM UTC

jxf wrote: post #11

eric4smith wrote: post #6

PostgreSQL text search is awesome - for English and Roman type languages. But Asian languages such as Thai, Japanese, Korean, etc are not going to work at all. PostgreSQL is weird about joins. Joining on certain columns could be super fast but others dog slow. And this can flip depending on size of table and this index Cardinality. That’s why it’s important on databases that grow quickly to check the performance of e…

> But Asian languages such as Thai, Japanese, Korean, etc are not going to work at all. I understand that there are likely to be many significant differences in graphemes in these languages from Roman text but I'm not familiar enough with any Asian language to construct an example. Can you give an illustrative example that explains why the search doesn't work as well (or at all) in those cases?

no spaces in japanese, it's tricky to determine word boundaries and conjugations and multiple "spellings" of the same word. the libs that tokenize and de-inflect languages are usually highly specialized technologies for east asian languages (maybe others but i'm particularly familiar with japanese, korean, chinese)

Re: Improving Postgres text search speed

#13

Post by jrochkind1 » Sat, Aug 13, 2022, 2:44 AM UTC

How can querying for PK's than doing a second query on the FK be faster than a join that's semantically equivalent? Postgres optimizer gone terribly wrong or something? Or am I misunderstanding what's going on?

It would never have occurred to me to even try this, I would have assumed postgres would do as well as could be done at what looks like a very standard join. So I guess that's a lesson not to make assumptions?

Not sure, without even having a theory of why pg would behave this way, I'd be worried that as the data changes it might regress again. Or if the phase of the moon changes or something -- since it seems like magic!

Re: Improving Postgres text search speed

#14

Post by norwalkbear » Sat, Aug 13, 2022, 2:53 AM UTC

Has anyone overcome the 16382 positional limits of tsvector?

That and the automatic stemming and lemming of search words even in phrase searches makes postgres awful for any software where accurate search is critical.

Re: Improving Postgres text search speed

#15

Post by hombre_fatal » Sat, Aug 13, 2022, 3:06 AM UTC

norwalkbear wrote: post #14

Has anyone overcome the 16382 positional limits of tsvector? That and the automatic stemming and lemming of search words even in phrase searches makes postgres awful for any software where accurate search is critical.

I remember when I ran into that first issue on my forum, I felt like I was the first person to ever use Postgres full-text search. (8?) Years ago, googling the exact error brought up nothing except some dev email/listserv chatter. Nobody else was indexing longer text documents? Wasn't very encouraging.

Oh well. The vast majority of forum posts don't hit that limit so I just excluded the exceptionally long posts from the index. I never revisited it again.

Re: Improving Postgres text search speed

#16

Post by vosper » Sat, Aug 13, 2022, 3:07 AM UTC

PGSync might be useful for those who don't mind also running Elasticsearch

https://github.com/toluaina/pgsync

Re: Improving Postgres text search speed

#17

Post by netcraft » Sat, Aug 13, 2022, 3:14 AM UTC

vosper wrote: post #16

PGSync might be useful for those who don't mind also running Elasticsearch https://github.com/toluaina/pgsync

Thank you for sharing. Does anyone know of something similar for dynamodb?

Re: Improving Postgres text search speed

#18

Post by simlevesque » Sat, Aug 13, 2022, 3:23 AM UTC

netcraft wrote: post #17

vosper wrote: post #16

PGSync might be useful for those who don't mind also running Elasticsearch https://github.com/toluaina/pgsync

Thank you for sharing. Does anyone know of something similar for dynamodb?

Indexing dynamodb with elasticsearch ? Amplify makes it super easy.

https://docs.amplify.aws/cli-legacy/graphql-transformer/sear...

Re: Improving Postgres text search speed

#19

Post by netcraft » Sat, Aug 13, 2022, 3:30 AM UTC

simlevesque wrote: post #18

netcraft wrote: post #17

Earlier quoted context omitted.

Thank you for sharing. Does anyone know of something similar for dynamodb?

Indexing dynamodb with elasticsearch ? Amplify makes it super easy. https://docs.amplify.aws/cli-legacy/graphql-transformer/sear...

Sorry, no, I mean sync pg to dynamodb instead of elasticsearch.

Re: Improving Postgres text search speed

#20

Post by anshul » Sat, Aug 13, 2022, 3:40 AM UTC

You might want to test using the first query as a sub-query or cte in the second one. That would likely give you the same / better perf. It would avoid the join and save a round trip.