News for hackers. Stuff that matters.
Re:Improving Postgres text search speed by rdoherty on Friday August 12, 2022 @09:23PM (#32445999)
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?
Re:Improving Postgres text search speed by hinkley on Friday August 12, 2022 @09:28PM (#32446030)
> 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 by dealforager on Friday August 12, 2022 @09:51PM (#32446175)
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 by jerryjerryjerry on Saturday August 13, 2022 @12:16AM (#32446842)
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 by nl on Friday August 12, 2022 @09:53PM (#32446180)
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 by tpetry on Saturday August 13, 2022 @03:44AM (#32447749)
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 by nl on Saturday August 13, 2022 @04:10AM (#32447868)
> 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 by hinkley on Friday August 12, 2022 @10:05PM (#32446269)
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 by nl on Saturday August 13, 2022 @03:40AM (#32447724)
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 by stickfigure on Saturday August 13, 2022 @05:15AM (#32448155)
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 by kuzee on Saturday August 13, 2022 @01:55PM (#32452161)
I'll gladly read anything you've written on the topic, sounds like you're pretty knowledgeable.