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?
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.
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?
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.
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!
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.