Improving Postgres text search speed

Post by eric4smith » Sat, Aug 13, 2022, 5:17 PM 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?

Post by kuzee » Sat, Aug 13, 2022, 5:55 PM UTC

stickfigure wrote: post #38

hinkley wrote: post #5

Earlier quoted context omitted.

> It seems like the changes were done with a hunch as to why they were slow? You say that like it’s a bad thing. If the hunch works does that cheapen the effect? The why can come after. It often does. Mastery isn’t pondering things faster, it’s using system 1 thinking for most of the process with some system 2 sprinkled on top. Or to use different terminology, intuition with a bit of high level executive function gui…

Sure, you could spend all day guessing what will improve the query or you can just add two words to your query and postgres will tell you. EXPLAIN ANALYZE is the bare minimum competent effort required to tune queries. I use postgres FTS heavily on large datasets, have done a lot of performance tuning, so I was excited by the title. Unfortunately the article failed to deliver any useful information.

Post by kuzee » Sat, Aug 13, 2022, 6:02 PM UTC

jrochkind1 wrote: post #13

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?…

Post by oskarpearson » Sat, Aug 13, 2022, 10:36 PM UTC

netcraft wrote: post #19

simlevesque wrote: post #18

Earlier quoted context omitted.

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.

Post by zasdffaa » Sun, Aug 14, 2022, 6:02 AM UTC

rubyist5eva wrote: post #44

zasdffaa wrote: post #42

Earlier quoted context omitted.

> Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries interesting! I like it. > or appends a "random()" clause to an existing "order by" clause That one I don't understand. If the Order By is there, why randomise it? I guess order by p, q, r, random() then it sort of allows you to find out if it's been ordered sufficiently for your needs - is that it?

Sufficient yes because if, for example, you’re simply sorting by a value that’s shared/duplicated like enums values, the ordering of the ordered groups is basically up to the query planner and can change (one query may use an index while another does a sequential scan, for example). The most common use case where I’ve seen this be significant is offset based pagination where the query plan actually changes depending…