public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yves Dorfsman <[email protected]>
To: [email protected]
Subject: Re: Many-to-many performance problem
Date: Fri, 10 Jun 2016 08:27:12 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAEhK25pHh7nrGUyQYwqCinrJ=6PXRgHo5DuzGXxgO-DvY8_Yqg@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]> 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 ([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]
Subject: Re: Many-to-many performance problem
In-Reply-To: <[email protected]>
* 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