Re: Inappropriate inner table for nested loop join

Thank you for your quick reply. Your solution works for me!

On 2017-06-23 20:20, Albe Laurenz wrote:
> PostgreSQL`s plan is to use the index on "posts"."timestamp" to find the
> rows with the lowest "timestamp", match with rows from "posts" in
> a nested loop and stop as soon as it has found 100 matches.
>
> Now it must be that the rows in "posts" that match with rows in "follows"
> have high values of "timestamp".

I mistakenly dropped DESC. The actual query should be:

SELECT "posts".*
FROM "posts"
JOIN "follows" ON "follows"."target_account" = "posts"."account"
WHERE "follows"."owner_account" = $1
ORDER BY "posts"."timestamp" DESC
LIMIT 100

I note that here since that may be confusion to understand the later
part of my first post.

> PostgreSQL doesn't know that, because it has no estimates how
> values correlate across tables, so it has to scan much more of the index
> than it had expected to, and the query performs poorly.

That is exactly the problem what I have encountered.

> You could either try to do something like
>
> SELECT *
> FROM (SELECT "posts".*
> FROM "posts"
> JOIN "follows" ON "follows"."target_account" =
"posts"."account"
> WHERE "follows"."owner_account" = $1
> OFFSET 0) q
> ORDER BY "posts"."timestamp"
> LIMIT 100;

It works. I had to replace "posts"."timestamp" with "timestamp", but
that is trivial. Anything else is fine.

> Or you could frop the index on "posts"."timestamp" and see if that helps.

That is not a solution for me because it was used by other queries, but
may make sense in other cases.