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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
