From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yves Dorfsman <yves@zioup.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Many-to-many performance problem
In-reply-to: <575ACE40.8070703@zioup.com>
References: 
 <CAEhK25pHh7nrGUyQYwqCinrJ=6PXRgHo5DuzGXxgO-DvY8_Yqg@mail.gmail.com>
 <22656.1465568025@sss.pgh.pa.us> <575ACE40.8070703@zioup.com>
Comments: In-reply-to Yves Dorfsman <yves@zioup.com>
	message dated "Fri, 10 Jun 2016 08:27:12 -0600"
Date: Fri, 10 Jun 2016 11:23:42 -0400
Message-ID: <25230.1465572222@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Yves Dorfsman <yves@zioup.com> writes:
> On 2016-06-10 08:13, Tom Lane wrote:
>> It looks like everything is fine as long as all the data the query needs
>> is already in PG's shared buffers.  As soon as it has to go to disk,
>> you're hurting, because disk reads seem to be taking ~10ms on average.

>                     ->  Index Scan using msgs_message_pkey on msgs_message  (cost=0.43..8.04 rows=1 width=47) (actual time=18.550..18.559 rows=0 loops=3556)
>                           Index Cond: (id = msgs_message_labels.message_id)
>                           Filter: (is_active AND is_handled AND has_labels AND (NOT is_archived) AND (created_on < '2016-06-10 07:11:06.381+00'::timestamp with time zone) AND (org_id = 7))
>                           Rows Removed by Filter: 1
>                           Buffers: shared hit=11032 read=3235 dirtied=5

> Do you mean that it reads the index from disk? Or that it looks things up in the index, and fetch data on disk (based on that lookup)?

The "reads" here might be either index pages or table pages; we can't tell
from EXPLAIN's statistics.  It's probably a good bet that more of them are
table pages than index pages though, just because the index should be a
lot smaller than the table and more fully represented in cache.

As for the numbers, we see that 18.559 * 3556 = 65995 ms were spent in
this indexscan plan node, versus negligible time for the same plan node
when no reads happened.  So we can blame pretty much all that time on
the 3235 disk reads, giving an average per read of just over 20ms.  Some
of the other plan nodes show lower averages, though, so I was conservative
and said "~10 ms".

			regards, tom lane


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