public inbox for [email protected]  

help / color / mirror / Atom feed

From: Albe Laurenz <[email protected]>
To: 'Akihiko Odaki  *EXTERN*' <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Inappropriate inner table for nested loop join
Date: Mon, 26 Jun 2017 08:55:00 +0000
Message-ID: <A737B7A37273E048B164557ADEF4A58B53A5E687@ntex2010i.host.magwien.gv.at> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<A737B7A37273E048B164557ADEF4A58B53A5D88F@ntex2010i.host.magwien.gv.at>
	<[email protected]>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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

That should be fine.

It fetches all rows from "follows" that match the condition,
Then joins them will all matching rows from "posts", sorts the
result descending by "id" and returns the 100 rows with the largest
value for "id".

So you will get those 100 rows from "posts" with the largest "id"
that have a match in "follows" where the condition is fulfilled.

It is just a different plan to do the same thing that is more efficient
in your case.

Yours,
Laurenz Albe

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Inappropriate inner table for nested loop join
  In-Reply-To: <A737B7A37273E048B164557ADEF4A58B53A5E687@ntex2010i.host.magwien.gv.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox