Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aUcOJ-0002Uq-IV for pgsql-performance@arkaria.postgresql.org; Sat, 13 Feb 2016 15:45:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aUcOJ-0001q6-4o for pgsql-performance@arkaria.postgresql.org; Sat, 13 Feb 2016 15:45:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aUcOH-0001nf-Oh for pgsql-performance@postgresql.org; Sat, 13 Feb 2016 15:45:45 +0000 Received: from clavin2.langille.org ([199.233.228.197]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aUcOG-0005o6-4f for pgsql-performance@postgresql.org; Sat, 13 Feb 2016 15:45:45 +0000 Received: from (clavin2.int.langille.org (clavin2.int.unixathome.org [10.4.7.7]) (Authenticated sender: hidden) with ESMTPSA id 70373AACF ; Sat, 13 Feb 2016 15:45:42 +0000 (UTC) Subject: Re: Running lots of inserts from selects on 9.4.5 Mime-Version: 1.0 (Mac OS X Mail 9.2 \(3112\)) Content-Type: multipart/signed; boundary="Apple-Mail=_A3910746-6DD5-429F-AE96-752A4071CD0B"; protocol="application/pgp-signature"; micalg=pgp-sha512 X-Pgp-Agent: GPGMail 2.6b2 From: Dan Langille In-Reply-To: Date: Sat, 13 Feb 2016 10:45:46 -0500 Cc: "pgsql-performance@postgresql.org" Message-Id: <94114DAE-08D1-4DF4-98F2-F167AB34167B@langille.org> References: <69012280-1847-426A-8813-D51CA4CC9B02@langille.org> <43C0F377-7A27-455A-B695-D3EA92B81128@langille.org> <6CB3CC7F-859F-4550-983C-074F3617A90F@langille.org> To: Jeff Janes X-Mailer: Apple Mail (2.3112) X-Pg-Spam-Score: -1.2 (-) 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 --Apple-Mail=_A3910746-6DD5-429F-AE96-752A4071CD0B Content-Type: multipart/alternative; boundary="Apple-Mail=_79F6FF0F-AD26-4612-A8AF-3E2F85E62B8B" --Apple-Mail=_79F6FF0F-AD26-4612-A8AF-3E2F85E62B8B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Feb 13, 2016, at 10:43 AM, Dan Langille wrote: >=20 > Today I tackled the production server. After discussion on the Bacula = devel mailing list = (http://marc.info/?l=3Dbacula-devel&m=3D145537742804482&w=3D2 = ) > I compared my schema to the stock schema provided with Bacula. Yes, I = found > extra indexes. I saved the existing schema and proceeded to remove = the indexes > from prod not found in the default. >=20 > The query time went from 223 minute to 4.5 minutes. That is 50 times = faster. The query plans: https://twitter.com/DLangille/status/698528182383804416 -- Dan Langille - BSDCan / PGCon dan@langille.org --Apple-Mail=_79F6FF0F-AD26-4612-A8AF-3E2F85E62B8B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
On = Feb 13, 2016, at 10:43 AM, Dan Langille <dan@langille.org> = wrote:

Today I = tackled the production server.  After discussion on the Bacula = devel mailing list (http://marc.info/?l=3Dbacula-devel&m=3D145537742804482&= w=3D2)
I compared my schema to the = stock schema provided with Bacula.  Yes, I found 
extra indexes.  I saved the existing schema and = proceeded to remove the indexes
from prod not found = in the default.

The query time went from 223 minute to 4.5 minutes. =  That is 50 times faster.

The query plans: https://twitter.com/DLangille/status/698528182383804416
-- 
Dan Langille - BSDCan / PGCon
dan@langille.org

= --Apple-Mail=_79F6FF0F-AD26-4612-A8AF-3E2F85E62B8B-- --Apple-Mail=_A3910746-6DD5-429F-AE96-752A4071CD0B Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=signature.asc Content-Type: application/pgp-signature; name=signature.asc Content-Description: Message signed with OpenPGP using GPGMail -----BEGIN PGP SIGNATURE----- Comment: GPGTools - http://gpgtools.org iQJ8BAEBCgBmBQJWv0+qXxSAAAAAAC4AKGlzc3Vlci1mcHJAbm90YXRpb25zLm9w ZW5wZ3AuZmlmdGhob3JzZW1hbi5uZXQ1MTE2RjM0ODIzRDdERDM4OTY0OUJBNzdF QjIxNTlERUU5NzI3MzlGAAoJEOshWd7pcnOf8QwQALAhBVQ7pTUrp2ByOeOZni1Y 44m8JGxnuRBvifT1qzN7JYKq5a9Hf5q08sfvQ5QKVIxTOw3RYZoeozY/5LdcRnu7 mX37jr4fPORYxORIPl2AKc4+Vi6/QEDFyI79x1C9BJ2giy2GuTToCcq39GYiSHnN eaYcnbEn6H8aSIZctu3XGPLYVc6WgmH5aGEMjwJ6iLG3PMi/6yE5CG1+pMAQa3Z0 3/q5gKF58omwapycfsdus4FECUZGI2XPwCT5TK8AscCQdklbebRr8rwDt8Y5XDDW +9aLsbKTR0HLFCiqRvsYD7MWMSzjXtvLS1MeUnmNdoM78c/kqngNwttUHpASWtcw eerfxX/sOdQNUXwsoIcaQDViKHK6V1Ee0Eb+xNBobRItefO86F5d25AkwtbfB84S cmSU6H55nt/sGSz8CNfd6xU3WLO7XI47bFFUmLEGoXyFgc/nl9cDLXwGH6aPLod4 qdLHmHAauchCHhiyo/wFRnIf5sUlep95Y4MxXkIUZUYRpvHNPKwns9FzXZjt33MV qIcN/672NnU1lx/Ono2QVsrAYUQ+e1ewQDQytQXP3qYhy7AfVHECzQHPtZ/jOQH6 +5nsFU+NLa398Ykec6NfBgenizh96lUTk5rf3E8BQRL+mFxa2Hfq7JXxTiSs0/1S 1n11XxPyt5CIAkfhJ4i6 =dOUF -----END PGP SIGNATURE----- --Apple-Mail=_A3910746-6DD5-429F-AE96-752A4071CD0B--