Improving Postgres text search speed
charityapi.org
Improving Postgres text search speed
1–10 of 59 posts
Re: Improving Postgres text search speed
#2I wrote up my recent experience optimizing Postgres text search on a database with a few million records without relying on a new service like Elastic Search.
Re: Improving Postgres text search speed
#3I'd love to see some details on the why using EXPLAIN ANALYZE on each query and schema. It seems like the changes were done with a hunch as to why they were slow?
Re: Improving Postgres text search speed
#4I wrote up my recent experience optimizing Postgres text search on a database with a few million records without relying on a new service like Elastic Search.
Awesome thanks for the write up. Definitely cool to see breaking up the query into 2 queries had such a marked improvement! I know I can often get too obsessed with making just a single query. Cheers.
Re: Improving Postgres text search speed
#5I'd love to see some details on the why using EXPLAIN ANALYZE on each query and schema. It seems like the changes were done with a hunch as to why they were slow?
> 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 guiding things.
Or a lot of hunches and a little thinking.
Re: Improving Postgres text search speed
#6PostgreSQL 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 even simple queries as those can balloon in execution time as the profile of the data changes.
Re: Improving Postgres text search speed
#7I'd love to see some details on the why using EXPLAIN ANALYZE on each query and schema. It seems like the changes were done with a hunch as to why they were slow?
> 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.
Re: Improving Postgres text search speed
#8I'd love to see some details on the why using EXPLAIN ANALYZE on each query and schema. It seems like the changes were done with a hunch as to why they were slow?
> 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…
It's a good point though. EXPLAIN should point exactly what was going wrong in their first attempt (where it was slower).
Postgres should be able to do that join in a single query faster than two queries with the latency in between.
So why isn't it?
Re: Improving Postgres text search speed
#9Earlier 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…
It's a good point though. EXPLAIN should point exactly what was going wrong in their first attempt (where it was slower). Postgres should be able to do that join in a single query faster than two queries with the latency in between. So why isn't it?
EXPLAIN shows gross problems. There are plenty of things well under an order of magnitude that won’t necessarily show up. It’s a very coarse grained yardstick. If you think it’s the only yardstick that matters, then you and I probably disagree about a great number of other things too.
Re: Improving Postgres text search speed
#10PostgreSQL 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…
Can you give more information about your JOINs performance? It’s mostly dependent on the presence and optimization of INDEXes and cardinality as you mention. JOIN ON indexed integers is usually fastest.