Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a8XGZ-0006Fz-GT for pgsql-performance@arkaria.postgresql.org; Mon, 14 Dec 2015 17:50:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a8XGY-0005az-13 for pgsql-performance@arkaria.postgresql.org; Mon, 14 Dec 2015 17:50:30 +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 1a8XGW-0005aV-Po for pgsql-performance@postgresql.org; Mon, 14 Dec 2015 17:50:29 +0000 Received: from mail-bn1on0056.outbound.protection.outlook.com ([157.56.110.56] helo=na01-bn1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a8XGN-00024r-Ho for pgsql-performance@postgresql.org; Mon, 14 Dec 2015 17:50:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=svLTrElsP10rMdkGPCbsN51SWpy8dCriuXus5CAUfVM=; b=BMLcif5gesusTMCCYvSuEzEQrgG4yEF+4dqIrTd4MO7VkrF30XAREeLXbd1KHswkqXBt2yimbwpHlAtMEl48r9yhLBOK9uMb96/1765IP+6gAM9CcloeVXgXBf0las5IOzafWHJRP7ST5BUyuZQ45Sz0L0mX486vUQPQyrqyQBU= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (2602:306:ce8b:3460:bcd1:d221:18af:af11) by BY2PR11MB0679.namprd11.prod.outlook.com (10.163.111.21) with Microsoft SMTP Server (TLS) id 15.1.355.16; Mon, 14 Dec 2015 17:50:03 +0000 Subject: Re: Performance difference between Slon master and slave To: Matthew Lunnon , References: <566EF984.5070404@rwa-net.co.uk> From: Jim Nasby Message-ID: <566F0145.30707@BlueTreble.com> Date: Mon, 14 Dec 2015 11:49:57 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: <566EF984.5070404@rwa-net.co.uk> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:bcd1:d221:18af:af11] X-ClientProxiedBy: CO2PR06CA036.namprd06.prod.outlook.com (10.141.242.36) To BY2PR11MB0679.namprd11.prod.outlook.com (25.163.111.21) X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 2:OFGRuqj2TY/cxHR21Lb6I+fTFOBweZeJzNQPhFn2PHrAjunjF5sh76DLyAmZl0RYjABW0mEkG0sn+bsdZYqo0Tbkfb6VmScvwzSe4LSRh9kbBbki+rhYrv/kOgtQW6GGeCTsZ9JQjD8GzsWO+OzePw==; 3:qtFDq7rSDstchlN4fIxdoL23Dw6gMVPqbj/kgMtTM7lmM69Cedkw2i62i8egAsQaFbw6L3M5sMFTiC44L8Egh7pmBW4MW6mGnvky50mKiUSjqdB4DFZK4e3kf7xBQjzW; 25:nCYZnfMtrB0xi+KhqB+YIPghFw82bc/bG6qkRLSQB4dB4AgVUuozvzkT451sjAPk0wwRe9gn1t7FIhgpTc9s39Alspmc4PfW5rXxKlilhpaZrOwaf84ptbNZc6INe03SVmdhaJbFJdcADgakf7Wx8O8utwinfKl48hKqa2IwTMvhdxXQQlGQjrZA1l+xlRr7wnunA17kEVxfheNWBEFVBnrVxdzrCrINxnbGp4tmMUbBgGmxJg7VLWEmKsRztjM3n/8wskmLNYVCW2HGq8KQBA==; 20:UFZ52ei/DsOvtlb2hMgIdEzM3Wat1l9Wu1Ce8llleXH34B9EyAcpFyhzU1yJzuYXWot+DeR/ynVjxW+m/+zh13+afTNs+VyWCiXYiKG8HXwJDMHMBjRNojQTYzKj91F9X468RjBVqlmVitXxi9dAm3FCXaV208Cf/D30G9He9S4= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY2PR11MB0679; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(5005006)(520078)(8121501046)(3002001)(10201501046); SRVR:BY2PR11MB0679; BCL:0; PCL:0; RULEID:; SRVR:BY2PR11MB0679; X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 4:sszWpi/4/kG0D4H3TQ2CbM6G87+bvmHoFakGYLvodZycLNVxC3yPz7wEqVzz+gwHJGGdhIG+J4j7lh21AyVisHCEp2r0bUs1lItEXgHS1X+Qvej8mEqSL8XIV0/FLhwbSux+jNPArwFFKz9BTqO2bzYv6fbFYeLgt5/6WRF3Ax2O/0PAQSjn8A+XRgyKHvDDnCXR3E39qVRV13qcBHdZTmDIR5JE7PxE2S/XmruVlwFTh4sud2dr4vxm7dZZ3xdgmqb/pXecxvYj5dxyaGBxQx/9Seehp4zwdmQs2j2vkAf7qez4YGG83lbnBY6l7jXx1Afk5Ek2iNJKgBKJ7iGMmf0B/CGnkJK6/GtuKOVRS2kr4GaPl+7nRCJ4zg+RlAf7 X-Forefront-PRVS: 0790FB1F33 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(189002)(377454003)(199003)(24454002)(479174004)(77096005)(76176999)(92566002)(1096002)(105586002)(42186005)(64126003)(65806001)(5001770100001)(4001350100001)(47776003)(40100003)(81156007)(107886002)(97736004)(19580395003)(50466002)(5001960100002)(189998001)(230700001)(65956001)(87976001)(50986999)(54356999)(86362001)(122386002)(33656002)(2950100001)(106356001)(83506001)(5004730100002)(101416001)(586003)(23676002)(65816999)(15975445007)(5008740100001)(36756003)(6116002)(3826002)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BY2PR11MB0679; H:decina.local; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCWTJQUjExTUIwNjc5OzIzOkI1R0h3YUltdVJvNlR3U0hCUTVWbGc3UHNa?= =?utf-8?B?a016bzVObjRqbDVoQm5WYzYyd3NpbFFJRDRlLzNWeVlCWjh0Y2ZURTV5YzFw?= =?utf-8?B?YU1EcGgzc2pSUVhIWWRqbU1MaWdWcGRoZXlKelpjSGZRaFhENHVZbHhxcWhn?= =?utf-8?B?M2VkdXFEREpLeWZJTE91SVFJWlJZczFNQkNEeHJSNmExQlZJSGlCVW14Z2pU?= =?utf-8?B?TEg0MXpOT1pFeTlNNVdZZmNwbGxzV1Jmc1hmR3VKRjNZbGxHTWxYak9hZzhX?= =?utf-8?B?YVlGbDRLQXU2T3hnVkY0OEJBbHIwQ3NjcExGbDU2MFo4ZnpRK0dLcHE1Z0Nq?= =?utf-8?B?cDhSZm5vQWRLeXVVZUtyY0h1YnJJSVl4OWpiZ3lVRDNaSG5NK0c5K3VaUCt4?= =?utf-8?B?dGFYcjY5OUQ0cWN0WDFWTE1ITUJuZU8wdW1vR0xYZktVRDZhSWFOZXhuR2tt?= =?utf-8?B?amszOUQ4M1M1WFV3SGdkVURRRmd1TmhxN2ZORVdGWE1POWdzZElKVmVPanNw?= =?utf-8?B?MjhpOTFzZUw3d0w5ZDlISmZDL2ZVemRUbExvNWl0WHlSdU9lRENUSFRQMHcz?= =?utf-8?B?MkprRDZmSmNxUWJ2TDRKVWJFZkMxcVBZU1Bvek4rRkpxWGI3Zko1NG4zcmRr?= =?utf-8?B?MHFpSmdFQjFTS1lOYTVDc2lkYVRKMnU0U2lOWEZuMXZRZlNZNWZiQTRMTHJh?= =?utf-8?B?OGZSTWpaVFFScnBXT2FtNWZ4VU5lT2NQQ2krWXMxQ0lRVFplM3dwcHcvYThC?= =?utf-8?B?eU92dk9TSHBkb2JXelNHMSs3aE0rR1dnRm05V2tvU2tYeDhTZUsyZmM4bVVJ?= =?utf-8?B?bFZLQVJULzRnVS9ybGRscjN5L3R5YVFhRmhtSml1ZmZQRVd4QkF2V0YrbUpM?= =?utf-8?B?cEN6L3hJMDZOL2dib1NvQ0cxSFJWUG15SWNBOFRGd3RHdXVOQzduLy8yczlM?= =?utf-8?B?UXVlTEhQdHJXV0M0OTQ5OHBTcEZTUkJCanoybG1zNSs2QkpEa0wxUlIvcktD?= =?utf-8?B?M3Q3OVlTbUVRUHJwQ1ljZWJPZW5udEQweDgxT1ljRzJVeWJ0dldrQzZIcjlB?= =?utf-8?B?V1kvSy8rR1FaVEZGYVFrMWhoMHZQRjJqakloM2dlRlorZGxSQS9uY2dmUEhS?= =?utf-8?B?ejg0anBhbit4Q0Z2SXpSU3VGNFhlbnJrdXRkMk0zWXJWbkVJckZFU0NsYWxt?= =?utf-8?B?dXJvUStyeHJHTEFvMzh6eGEzeWxTNnFyODRYVWpOaStOUkdmSnUyOUVTNi9E?= =?utf-8?B?Q0JlYmQ5VzM5aGdZNVNma0dXWG5NVGU2bXBjS3JRSFoxM0JJYUpxSGtTZDBp?= =?utf-8?B?R21KaGNYWGhXMnorb2RSaVJSS1drdTZ6RDk2Rld4a2Vlc28wT1RqSTI4U1V6?= =?utf-8?B?ZjRXbStZakVodjlKNzkvMmpSS2RlSHQ0U0ozZmptV0RkaUNCOW9NUjd6Zzlv?= =?utf-8?B?aTUydkh1bGJGQjZySGlNbXp0Mk5VMG9nVFFSOWFkZ056d2xxZStaZ3pxYUZ1?= =?utf-8?B?bmoxdXpteFdFeWdSTVNSWVA2R1BVSGE4enIyQ2Zqd1RWNGZaMmw1M2RCT0xG?= =?utf-8?B?VUxuRGlUbEMzMU5pUVJCYkxXOEpsV2Z1amhUVzczakowMTNHalRyd3ZydW5n?= =?utf-8?B?Y2ZqQmVNb285b2hrczdaMXU1ZXFwSzFQdTZwRTViRlUvVXkrZFQ4MGd2UnE2?= =?utf-8?B?bVJCcndPSnJ3OGRhcEhRb0hVaHlSOHdycURxcE1wdmszRytwMjU3UUlBN09x?= =?utf-8?B?aE9MWHFnemtsYW1QZnJqZz09?= X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 5:+mTdhfCGeyBdoUFpPDLSTgNEa1xpj+orCRNHi5J9lI0NfFKqmcRv0a6hYP0saqgnvdp8LfCnefzlrgDTF1O0gqxuW5xcxLyJvSuDpqtOwd4Z8j/f5kzjLkhp2pbMwmkt/5ZlKSS2WER1PgMFnD8bHw==; 24:JrupocQzSnR866/+eWg+MFdi3qFz7UCeP7F2c4hkNxcJTu7J9RTP/TNlSyHPrhmO4CshW+9lZnhRciEvCh5U48+6NV24W70VrJpauv+w+us= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Dec 2015 17:50:03.5459 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0679 X-Pg-Spam-Score: -1.9 (-) 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 On 12/14/15 11:16 AM, Matthew Lunnon wrote: > Inspecting the execution plan shows that there are some differences, for > example, the slave is using a HashAggregate when the master is simply > grouping. There also seems to be a difference with the ordering of the > sub plans. Have you tried analyzing the tables on the slave? Also, keep in mind that the first time you access rows on a Slony slave after they're replicated Postgres will need to write hint bits out, which will take some time. But that's clearly not the issue here. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance