Subject: Re: Many-to-many performance problem
To: pgsql-performance@postgresql.org
References: 
 <CAEhK25pHh7nrGUyQYwqCinrJ=6PXRgHo5DuzGXxgO-DvY8_Yqg@mail.gmail.com>
 <22656.1465568025@sss.pgh.pa.us>
From: Yves Dorfsman <yves@zioup.com>
Message-ID: <575ACE40.8070703@zioup.com>
Date: Fri, 10 Jun 2016 08:27:12 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.2
MIME-Version: 1.0
In-Reply-To: <22656.1465568025@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


I thought this was a really interesting case, and would love to learn from it, please bare with me if my questions are naive.

On 2016-06-10 08:13, Tom Lane wrote:
> Rowan Seymour <rowanseymour@gmail.com> writes:
>> Most of time, this query performs like https://explain.depesz.com/s/ksOC
>> (~15ms). It's no longer using the using the msgs_inbox index, but it's
>> plenty fast. However, sometimes it performs like
>> https://explain.depesz.com/s/81c (67000ms)
>> And if you run it again, it'll be fast again.
> 
> 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)?
Is the 18ms from the Buffers: read=3235? That's 3235 rows read from disk?

-- 
http://yves.zioup.com
gpg: 4096R/32B0F416 


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