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.