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.
[deleted]
Improving Postgres text search speed
21–30 of 59 posts
Re: Improving Postgres text search speed
#22Has 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 used a trigger function to detect long text and trim the source before it got indexed. That meant that any text over 500kb just got dropped from the index. I also used one index per long text field rather than combining with other fields.
Re: Improving Postgres text search speed
#23Earlier 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…
If you're discussing performance of SQL queries, showing the output of EXPLAIN ANALYZE is the bare minimum. There's too many variables that can affect performance and if you can't see what's happening under the hood it's not very useful.
Same here, and I'd also like to see what explain analyze shows about the plan and execution details. Also, some system setup may also help, e.g., memory size and check if spill kicks in, etc.
Re: Improving Postgres text search speed
#24PostgreSQL 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…
Support for i18n:
https://www.google.com/search?q=pgroonga
Re: Improving Postgres text search speed
#25You 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.
If you dont need the output of the first query you'll almost always have the best performance in sql using exists syntax eg
select * from query1 as q where exists ( select * from query2 as q2 where q.col = q2.col )
Re: Improving Postgres text search speed
#26PGSync might be useful for those who don't mind also running Elasticsearch https://github.com/toluaina/pgsync
In that case you may also want to take a look at https://github.com/zombodb/zombodb
Re: Improving Postgres text search speed
#27How 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?…
While I generally wonder the same question... I've seen basically every database follow this pattern. There are fairly frequent scenarios where doing a couple separate indexed queries performs better than an equivalent join. Particularly when queries get kinda-large and up.
I can certainly see it when running into memory limits per table, because doing the full join might require more memory at once, but it frequently happens far below where I'd expect that to occur (in the low thousands of rows). Dunno why. Maybe it uses caches more efficiently? Many simple operations are often more cache-able than a few complex ones.
Re: Improving Postgres text search speed
#28PGSync might be useful for those who don't mind also running Elasticsearch https://github.com/toluaina/pgsync
This is so neat! How does it handle updates? Are there similar systems which can sync with Redis for quick caching?
Re: Improving Postgres text search speed
#29You 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.
If you dont need the output of the first query you'll almost always have the best performance in sql using exists syntax eg select * from query1 as q where exists ( select * from query2 as q2 where q.col = q2.col )
Yes although in this specific case you’d lose the order of the results with exists
Re: Improving Postgres text search speed
#30> A key gotcha that tripped us up: when querying with a list of primary keys, Postgres will not return the records in the same order as the IDs provided in a “where … in” clause.
That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on order unless... scratch that onto your eyeballs so you never forget it.
Also, will people please stop posting rainbow-on-black screenshots, especially with the screenshotted text size smaller than the main text.