Re: [PERFORM] 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.