Subject: Re: Inappropriate inner table for nested loop join
From: Akihiko Odaki <akihiko.odaki.4i@stu.hosei.ac.jp>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: <6a997766-470e-393e-3605-e55d2b41d1a9@stu.hosei.ac.jp>
 <A737B7A37273E048B164557ADEF4A58B53A5D88F@ntex2010i.host.magwien.gv.at>
 <4b3fe214-6756-26d1-82d2-1882f7418033@stu.hosei.ac.jp>
Message-ID: <81a17ec0-4556-8d41-e043-777433d8ba10@stu.hosei.ac.jp>
Date: Fri, 23 Jun 2017 20:54:39 +0900
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.0
MIME-Version: 1.0
In-Reply-To: <4b3fe214-6756-26d1-82d2-1882f7418033@stu.hosei.ac.jp>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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".

Regards,
Akihiko Odaki


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance