Re: Inappropriate inner table for nested loop join

Akihiko Odaki wrote:
> On 2017-06-23 20:20, Albe Laurenz wrote:
>> 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;
>
> Now I wonder whether it actually sorted or not. As you said, I want to
> "find rows with the greatest 'timestamp', match with rows from 'posts'
> in a nested loop and stop as soon as it has found 100 matches".
>
> However, it seems to query 100 records without any consideration for
> "timestamp", and then sorts them. That is not expected. Here is a
> abstract query plan:
>
> Limit
> -> Sort
> Sort Key: posts.id DESC
> -> Nested Loop
> -> Seq Scan on follows
> Filter: (owner_account = $1)
> -> Index Scan using index_posts_on_account on posts
> Index Cond: (account_id = follows.target_account)
>
> index_posts_on_account is an obsolete index on "posts" and only for
> "account". So it does nothing for sorting "timestamp".

Yes, if you replace posts.timestamp with q.timestamp, it should
sort by that.

Could you send CREATE TABLE and CREATE INDEX statements so I can try it?

Yours,
Laurenz Albe