Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Improving Postgres text search speed

charityapi.org

Post reply on HN
Print view View original

31–40 of 59 posts

Re: Improving Postgres text search speed

#31

Post by masklinn »

Groxx wrote: post #27
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?…

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

Maybe disk spill because some joins require a superlinear amount of space and doesn’t fit in memory buffers?

Re: Improving Postgres text search speed

#32

Post by zasdffaa »

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

It's a failure of the optimiser. AS a DB guy (MSSQL, not postgres), there's always a risk of this, you always have to be aware of this, and don't assume. MSSQL's optimiser is pretty good but I've also seen slowdowns on plain queries after adding an index. Optimisers are complex and individual optimisation rules interact in odd and sometimes counterproductive ways. Also if your stats are not up-to-date, trouble is guaranteed.

Re: Improving Postgres text search speed

#33

Post by nl »

hinkley wrote: post #9
nl wrote: post #8

Earlier quoted context omitted.

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.

EXPLAIN shows you how the query is being executed.

It won't always give you a recipe for a solution but it's always useful for an experienced Postgres developer to look at to understand why it isn't performing like you expect.

I still don't understand why the two queries runs faster than a single query. There might be good reasons for that and they might not be able to be fixed but at the same time there might be ways to fix it. EXPLAIN gives you clues.

Re: Improving Postgres text search speed

#34

Post by tpetry »

nl wrote: post #8
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…

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?

The point is the query plan will most probably look totally different. A fulltext search or GIS search is calculated differently than a btree lookup and PostgreSQL choose a complete different joining strategy. This can also happen when combining such a condition with another btree index lookup. It doesn‘t have to be a fault of PostgreSQL, sometimes its not even possible to make it faster. E.g. all fulltext search results need to be calculated and then joined.

Re: Improving Postgres text search speed

#35

Post by manigandham »

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.

The lack of BM25 relevance scoring is the bigger problem. Postgres FTS is fine as a better "LIKE" filter with very little overhead, but it's a poor choice for serious search applications or scale.

Re: Improving Postgres text search speed

#36

Post by nl »

tpetry wrote: post #34
nl wrote: post #8

Earlier quoted context omitted.

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?

The point is the query plan will most probably look totally different. A fulltext search or GIS search is calculated differently than a btree lookup and PostgreSQL choose a complete different joining strategy. This can also happen when combining such a condition with another btree index lookup. It doesn‘t have to be a fault of PostgreSQL, sometimes its not even possible to make it faster. E.g. all fulltext search res…

> The point is the query plan will most probably look totally different.

Different to what?

> PostgreSQL choose a complete different joining strategy. This can also happen when combining such a condition with another btree index lookup. It doesn‘t have to be a fault of PostgreSQL

Yes agree with all that is possible. EXPLAIN will tell us!

Re: Improving Postgres text search speed

#37

Post by Olreich »

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

It’s almost never faster to do two queries. But DBs are complicated query engines with sophisticated compilers, so you have to go in and try to convince the compiler to optimize the way you want that will make the query fast. EXPLAIN_ANALYZE is your best friend for understanding what the compiler thinks is best. And then you can adjust the query to convince it that it should do something faster.

In this specific case, I’d bet that having the FROM and JOIN tables reversed would be enough to get even better performance than 2 queries: `SELECT * FROM os JOIN o …`.

Re: Improving Postgres text search speed

#38

Post by stickfigure »

hinkley wrote: post #5
rdoherty wrote: post #3

I'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…

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.

Re: Improving Postgres text search speed

#39

Post by jmull »

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 don’t know ab out the positional limits, but the built-in stemming and other transformations are a default, which you can change. I think this is the relevant mechanism: https://www.postgresql.org/docs/current/textsearch-dictionar...

Re: Improving Postgres text search speed

#40

Post by icedchai »

zasdffaa wrote: post #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 o…

This does seem fundamental.
Post reply on HN