[nl]

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?

[tpetry]

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.

[nl]

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

[hinkley]

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.

[nl]

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.