From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rowan Seymour <rowanseymour@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Many-to-many performance problem
In-reply-to: 
 <CAEhK25pHh7nrGUyQYwqCinrJ=6PXRgHo5DuzGXxgO-DvY8_Yqg@mail.gmail.com>
References: 
 <CAEhK25pHh7nrGUyQYwqCinrJ=6PXRgHo5DuzGXxgO-DvY8_Yqg@mail.gmail.com>
Comments: In-reply-to Rowan Seymour <rowanseymour@gmail.com>
	message dated "Fri, 10 Jun 2016 15:04:01 +0200"
Date: Fri, 10 Jun 2016 10:13:45 -0400
Message-ID: <22656.1465568025@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.

> Server is an Amazon RDS instance with default settings and Postgres 9.3.10,

And I think you just explained your problem.  Did you spring for adequate
guaranteed IOPS on this instance?  If not, you need to, or else live with
erratic performance.  If you did, you have a beef to raise with AWS that
you're not getting the performance you paid for.

You might be able to ameliorate matters by raising shared_buffers, but
unless your database isn't growing that approach has limited future.

			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