Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDSgq-0007hl-Gv for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 08:30:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDSgp-0004Kd-3x for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 08:30:15 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDSgo-0004GZ-1g for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 08:30:14 +0000 Received: from mail-qk0-x234.google.com ([2607:f8b0:400d:c09::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bDSgk-0003pD-4E for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 08:30:12 +0000 Received: by mail-qk0-x234.google.com with SMTP id s186so46147188qkc.1 for ; Thu, 16 Jun 2016 01:30:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=L1xvioE/pxuBd4rppj/uJPF+BJtaxMyR+oXPohe9v9M=; b=gl+bT/QIL4+wccgO0Kk7lFSzsfOhwQpTdM0MjnkQu44oNhLqTqascp/jICJwJHv0G4 qtI8z+Z0v0YXlIPAP78hn7II04F4glDCYcgAi0WJi2e6GMRgNhl/h+5TIXDe/sDCHxiS COdTPR6eqfDH8zD5C1+kfF0wsKnP6JInHGZsF/burU4GckxZZQwDd2kTflPDyQ3yOZkr DLSi7Ap57RTIktXOXuXYKoauOyn2VXgMAwXVhA4DjScSrBu3K8dvz2SFn2l4GYfcHSEd wAS/jw5ZsKKn97lqn21MMDKkEgxWPUplCID898xdWtuMpFfKxHBKiRjC3zlk3cJLyo5J OeDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=L1xvioE/pxuBd4rppj/uJPF+BJtaxMyR+oXPohe9v9M=; b=QtIBetY0J6CPuD4JJW7xU8KDI1A1quNbfWCz86EmDQ1lMNwL1XXAmq6NlTFFZ16Uu7 Aj/VHVSh1YLSltz4nRxgavClwy/soN5IuB1rCZwUxcdyrwphlKm+zhfk0J/eGul/v5w7 ImhzNwfIb71iZkHOZCG2uw7wDg5k+XUniI8GsjmsCOLBLnBWaxsH6xHx1T/RiB8P2YQH m2kKNTgwc4k76g9W9sFg0WPAwV7JljZn1syoixw2FgFQcOpfUY7IoftgckRptTp3PMw4 0vpE2vML7tIaSND2kdflP4KPS2GziwPy/7sIyB5pgSj8NptpommGBKFagT6IvC4ICeUw meAA== X-Gm-Message-State: ALyK8tItBNh+MtK+pg19RYNl2VbU75mTo19/q37hAITQVwkRBKvIAXG3jMIrDQJWshTZiWr5Oe2BFJr87yOqqA== X-Received: by 10.237.54.166 with SMTP id f35mr3321172qtb.78.1466065808811; Thu, 16 Jun 2016 01:30:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.85.8 with HTTP; Thu, 16 Jun 2016 01:29:49 -0700 (PDT) In-Reply-To: <3f3b6180-4c67-7b17-601e-1fb0ad16fb17@postgrespro.ru> References: <3f3b6180-4c67-7b17-601e-1fb0ad16fb17@postgrespro.ru> From: Rowan Seymour Date: Thu, 16 Jun 2016 10:29:49 +0200 Message-ID: Subject: Re: Many-to-many performance problem To: Alex Ignatov Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001a1135dbb2c071970535610bc5 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a1135dbb2c071970535610bc5 Content-Type: text/plain; charset=UTF-8 When you create an Postgres RDS instance, it's comes with a "default.postgres9.3" parameter group which contains substitutions based on the server size. The defaults for the memory related settings are: effective_cache_size = {DBInstanceClassMemory/16384} maintenance_work_mem = GREATEST({DBInstanceClassMemory/63963136*1024},65536) shared_buffers = {DBInstanceClassMemory/32768} temp_buffers = work_mem = According to http://www.davidmkerr.com/2013/11/tune-your-postgres-rds-instance-via.html, the units for effective_cache_size on AWS RDS, are 8kb blocks (am not sure why this is...), so DBInstanceClassMemory/16384 = DBInstanceClassMemory/(2 * 8kb) = 50% of system memory. We upgraded the server over the weekend which doubled the system memory and increased the available IOPS, and that appears to have greatly improved the situation, but there have still been a few timeouts. I'm wondering now if activity on the other database in this instance doesn't occasionally push our indexes out of memory. Thanks, Rowan On 10 June 2016 at 18:11, Alex Ignatov wrote: > > On 10.06.2016 16:04, Rowan Seymour wrote: > > In our Django app we have messages (currently about 7 million in table > msgs_message) and labels (about 300), and a join table to associate > messages with labels (about 500,000 in msgs_message_labels). Not sure > you'll need them, but here are the relevant table schemas: > > CREATE TABLE msgs_message > ( > id INTEGER PRIMARY KEY NOT NULL, > type VARCHAR NOT NULL, > text TEXT NOT NULL, > is_archived BOOLEAN NOT NULL, > created_on TIMESTAMP WITH TIME ZONE NOT NULL, > contact_id INTEGER NOT NULL, > org_id INTEGER NOT NULL, > case_id INTEGER, > backend_id INTEGER NOT NULL, > is_handled BOOLEAN NOT NULL, > is_flagged BOOLEAN NOT NULL, > is_active BOOLEAN NOT NULL, > has_labels BOOLEAN NOT NULL, > CONSTRAINT > msgs_message_contact_id_5c8e3f216c115643_fk_contacts_contact_id FOREIGN KEY > (contact_id) REFERENCES contacts_contact (id), > CONSTRAINT msgs_message_org_id_81a0adfcc99151d_fk_orgs_org_id FOREIGN > KEY (org_id) REFERENCES orgs_org (id), > CONSTRAINT msgs_message_case_id_51998150f9629c_fk_cases_case_id > FOREIGN KEY (case_id) REFERENCES cases_case (id) > ); > CREATE UNIQUE INDEX msgs_message_backend_id_key ON msgs_message > (backend_id); > CREATE INDEX msgs_message_6d82f13d ON msgs_message (contact_id); > CREATE INDEX msgs_message_9cf869aa ON msgs_message (org_id); > CREATE INDEX msgs_message_7f12ca67 ON msgs_message (case_id); > > CREATE TABLE msgs_message_labels > ( > id INTEGER PRIMARY KEY NOT NULL, > message_id INTEGER NOT NULL, > label_id INTEGER NOT NULL, > CONSTRAINT > msgs_message_lab_message_id_1dfa44628fe448dd_fk_msgs_message_id FOREIGN KEY > (message_id) REFERENCES msgs_message (id), > CONSTRAINT > msgs_message_labels_label_id_77cbdebd8d255b7a_fk_msgs_label_id FOREIGN KEY > (label_id) REFERENCES msgs_label (id) > ); > CREATE UNIQUE INDEX msgs_message_labels_message_id_label_id_key ON > msgs_message_labels (message_id, label_id); > CREATE INDEX msgs_message_labels_4ccaa172 ON msgs_message_labels > (message_id); > CREATE INDEX msgs_message_labels_abec2aca ON msgs_message_labels > (label_id); > > Users can search for messages, and they are returned page by page in > reverse chronological order. There are several partial multi-column indexes > on the message table, but the one used for the example queries below is > > CREATE INDEX msgs_inbox ON msgs_message(org_id, created_on DESC) > WHERE is_active = TRUE AND is_handled = TRUE AND is_archived = FALSE AND > has_labels = TRUE; > > So a typical query for the latest page of messages looks like ( > https://explain.depesz.com/s/G9ew): > > SELECT "msgs_message".* > FROM "msgs_message" > WHERE ("msgs_message"."org_id" = 7 > AND "msgs_message"."is_active" = true > AND "msgs_message"."is_handled" = true > AND "msgs_message"."has_labels" = true > AND "msgs_message"."is_archived" = false > AND "msgs_message"."created_on" < '2016-06-10T07:11:06.381000 > +00:00'::timestamptz > ) ORDER BY "msgs_message"."created_on" DESC LIMIT 50 > > But users can also search for messages that have one or more labels, > leading to queries that look like: > > SELECT DISTINCT "msgs_message".* > FROM "msgs_message" > INNER JOIN "msgs_message_labels" ON ( "msgs_message"."id" = > "msgs_message_labels"."message_id" ) > WHERE ("msgs_message"."org_id" = 7 > AND "msgs_message"."is_active" = true > AND "msgs_message"."is_handled" = true > AND "msgs_message_labels"."label_id" IN (127, 128, 135, 136, 137, 138, > 140, 141, 143, 144) > AND "msgs_message"."has_labels" = true > AND "msgs_message"."is_archived" = false > AND "msgs_message"."created_on" < '2016-06-10T07:11:06.381000 > +00:00'::timestamptz > ) ORDER BY "msgs_message"."created_on" DESC LIMIT 50 > > 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. Am I correct in interpreting > that second explain as being slow because msgs_message_pkey isn't cached? > It looks like it read from that index 3556 times, and each time took 18.559 > (?) ms, and that adds up to 65,996ms. The database server says it has lots > of free memory so is there something I should be doing to keep that index > in memory? > > Generally speaking, is there a good strategy for optimising queries like > these which involve two tables? > > - I tried moving the label references into an int array on > msgs_message, and then using btree_gin to create a multi-column index > involving the array column, but that doesn't appear to be very useful for > these ordered queries because it's not an ordered index. > - I tried adding created_on to msgs_message_labels table but I > couldn't find a way of avoiding the in-memory sort. > - Have thought about dynamically creating partial indexes for each > label using an array column on msgs_message to hold label ids, and index > condition like WHERE label_ids && ARRAY[123] but not sure what other > problems I'll run into with hundreds of indexes on the same table. > > Server is an Amazon RDS instance with default settings and Postgres > 9.3.10, with one other database in the instance. > > All advice very much appreciated, thanks > > -- > *Rowan Seymour* | +260 964153686 <%2B260%20964153686> > > Hello! What do you mean by > "Server is an Amazon RDS instance with default settings and Postgres > 9.3.10, with one other database in the instance." > PG is with default config or smth else? > Is it with default config as it is as from compile version? If so you > should definitely have to do some tuning on it. > By looking on plan i saw a lot of disk read. It can be linked to small > shared memory dedicated to PG exactly what Tom said. > Can you share pg config or raise for example shared_buffers parameter? > > > Alex Ignatov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > > > -- *Rowan Seymour* | +260 964153686 | @rowanseymour --001a1135dbb2c071970535610bc5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
When you create an Postgres RDS instance, it's comes w= ith a "default.postgres9.3" parameter group which contains substi= tutions based on the server size. The defaults for the memory related setti= ngs are:

effective_cache_size =3D {DBInstanceClassM= emory/16384}
maintenance_work_mem =3D GREATEST({DBInst= anceClassMemory/63963136*1024},65536)
shared_buffers = =3D {DBInstanceClassMemory/32768}
temp_buffers =3D <not = set>
work_mem =3D <not set>

According to=C2=A0http://www.davidmkerr.com/2013/11/tune-your-= postgres-rds-instance-via.html, the units for effective_cache_size on A= WS RDS, are 8kb blocks (am not sure why this is...), so DBInstanceClassMemo= ry/16384 =3D DBInstanceClassMemory/(2 * 8kb) =3D 50% of system memory.

We upgraded the server over the weekend which doubled = the system memory and increased the available IOPS, and that appears to hav= e greatly improved the situation, but there have still been a few timeouts.= I'm wondering now if activity on the other database in this instance d= oesn't occasionally push our indexes out of memory.

Thanks, Rowan

On 10 June 2016 at 18:11, Alex Ignatov <= a.ignatov@pos= tgrespro.ru> wrote:
=20 =20 =20

On 10.06.2016 16:04, Rowan Seymour wrote:
In our Django app we have messages (currently about 7 million in table msgs_message) and labels (about 300), and a join table to associate messages with labels (about 500,000 in msgs_message_labels). Not sure you'll need them, but here are the relevant table schemas:

CREATE TABLE msgs_message
(
=C2=A0 =C2=A0 id INTEGER PRIMARY KEY NOT NULL,
=C2=A0 =C2=A0 type VARCHAR NOT NULL,
=C2=A0 =C2=A0 text TEXT NOT NULL,
=C2=A0 =C2=A0 is_archived BOOLEAN NOT NULL,
=C2=A0 =C2=A0 created_on TIMESTAMP WITH TIME ZONE NOT NULL,<= /div>
=C2=A0 =C2=A0 contact_id INTEGER NOT NULL,
=C2=A0 =C2=A0 org_id INTEGER NOT NULL,
=C2=A0 =C2=A0 case_id INTEGER,
=C2=A0 =C2=A0 backend_id INTEGER NOT NULL,
=C2=A0 =C2=A0 is_handled BOOLEAN NOT NULL,
=C2=A0 =C2=A0 is_flagged BOOLEAN NOT NULL,
=C2=A0 =C2=A0 is_active BOOLEAN NOT NULL,
=C2=A0 =C2=A0 has_labels BOOLEAN NOT NULL,
=C2=A0 =C2=A0 CONSTRAINT msgs_message_contact_id_5c8e3f216c115643_fk_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES contacts_contact (id),
=C2=A0 =C2=A0 CONSTRAINT msgs_message_org_id_81a0adfcc99151d_fk_orgs_org_id FOREIGN KEY (org_id) REFERENCES orgs_org (id),
=C2=A0 =C2=A0 CONSTRAINT msgs_message_case_id_51998150f9629c_fk_cases_case_id FOREIGN KEY (case_id) REFERENCES cases_case (id)
);
CREATE UNIQUE INDEX msgs_message_backend_id_key ON msgs_message (backend_id);
CREATE INDEX msgs_message_6d82f13d ON msgs_message (contact_id);
CREATE INDEX msgs_message_9cf869aa ON msgs_message (org_id);
CREATE INDEX msgs_message_7f12ca67 ON msgs_message (case_id);

CREATE TABLE msgs_message_labels
(
=C2=A0 =C2=A0 id INTEGER PRIMARY KEY NOT NULL,
=C2=A0 =C2=A0 message_id INTEGER NOT NULL,
=C2=A0 =C2=A0 label_id INTEGER NOT NULL,
=C2=A0 =C2=A0 CONSTRAINT msgs_message_lab_message_id_1dfa44628fe448dd_fk_msgs_message_id FOREIGN KEY (message_id) REFERENCES msgs_message (id),
=C2=A0 =C2=A0 CONSTRAINT msgs_message_labels_label_id_77cbdebd8d255b7a_fk_msgs_label_id FOREIGN KEY (label_id) REFERENCES msgs_label (id)
);
CREATE UNIQUE INDEX msgs_message_labels_message_id_label_id_key ON msgs_message_labels (message_id, label_id);
CREATE INDEX msgs_message_labels_4ccaa172 ON msgs_message_labels (message_id);
CREATE INDEX msgs_message_labels_abec2aca ON msgs_message_labels (label_id);

Users can search for messages, and they are returned page by page in reverse chronological order. There are several partial multi-column indexes on the message table, but the one used for the example queries below is

CREATE INDEX msgs_inbox ON msgs_message(org_id, created_on DESC)
WHERE is_active =3D TRUE AND is_handled =3D TRUE AND is_archived =3D FALSE AND has_labels =3D TRUE;

So a typical query for the latest page of messages looks like (https://explain.depesz.com/s/G9ew):

SELECT "msgs_message".*=C2=A0
FROM "msgs_message"=C2=A0
WHERE ("msgs_message"."org_id" =3D 7=C2=A0=
=C2=A0 =C2=A0 AND "msgs_message"."is_active&quo= t; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."is_handled&qu= ot; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."has_labels&qu= ot; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."is_archived&q= uot; =3D false=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."created_on&qu= ot; < '2016-06-10T07:11:06.381000+00:00'::timestamptz
) ORDER BY "msgs_message"."created_on" DES= C LIMIT 50

But users can also search for messages that have one or more labels, leading to queries that look like:

SELECT DISTINCT "msgs_message".*=C2=A0
FROM "msgs_message"=C2=A0
INNER JOIN "msgs_message_labels" ON ( "msgs_m= essage"."id" =3D "msgs_message_labels"."message_id" )=C2= =A0
WHERE ("msgs_message"."org_id" =3D 7=C2= =A0
=C2=A0 =C2=A0 AND "msgs_message"."is_active&q= uot; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."is_handled&= quot; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message_labels"."labe= l_id" IN (127, 128, 135, 136, 137, 138, 140, 141, 143, 144)=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."has_labels&= quot; =3D true=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."is_archived= " =3D false=C2=A0
=C2=A0 =C2=A0 AND "msgs_message"."created_on&= quot; < '2016-06-10T07:11:06.381000+00:00'::timestamptz
) ORDER BY "msgs_message"."created_on" D= ESC LIMIT 50

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=C2= =A0https://explain.d= epesz.com/s/81c (67000ms)

And if you run it again, it'll be fast again. Am I correct in interpreting that second explain as being slow because msgs_message_pkey isn't cached? It looks like it read from that index 3556 times, and each time took=C2=A018.559 (?)=C2=A0ms= , and that adds up to=C2=A065,996ms. The database server says it has lo= ts of free memory so is there something I should be doing to keep that index in memory?

Generally speaking, is there a good strategy for optimising queries like these which involve two tables?
  • I tried moving the label references into an int array on msgs_message, and then using btree_gin to create a multi-column index involving the array column, but that doesn't appear to be very useful for these ordered querie= s because it's not an ordered index.
  • I tried adding created_on to=C2=A0msgs_message_labels table but I couldn't find a way of avoiding the in-memory sort.=
  • Have thought about dynamically creating partial indexes for each label using an array column on=C2=A0msgs_message to hold label ids, and index condition like WHERE label_ids && ARRAY[123] but not sure what other problems I'll run into with hundreds of indexes on the same table.=
Server is an Amazon RDS instance with default settings and Postgres 9.3.10, with one other database in the instance.

All advice very much appreciated, thanks

--
Rowan Seymour | +260 964153686
Hello! What do you mean by
"Server is an Amazon RDS instance with default settings and Postgr= es 9.3.10, with one other database in the instance."
PG is with default config or smth else?
Is it=C2=A0 with default config as it is as from compile version? If so you should definitely have to do some tuning on it.
By looking on plan i saw a lot of disk read. It can be linked to small shared memory dedicated to PG exactly what Tom said.
Can you share pg config or raise for example shared_buffers parameter?


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




--
Rowan Seymour | +260 96415= 3686 | @rowanseymour
--001a1135dbb2c071970535610bc5--