Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bQMKk-0002Y3-73 for pgsql-performance@arkaria.postgresql.org; Thu, 21 Jul 2016 22:20:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bQMKj-0001QK-H9 for pgsql-performance@arkaria.postgresql.org; Thu, 21 Jul 2016 22:20:45 +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_2) (envelope-from ) id 1bQMKi-0001P6-RG for pgsql-performance@postgresql.org; Thu, 21 Jul 2016 22:20:44 +0000 Received: from mail-co1nam03on0078.outbound.protection.outlook.com ([104.47.40.78] helo=NAM03-CO1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bQMKe-0004uB-5z for pgsql-performance@postgresql.org; Thu, 21 Jul 2016 22:20:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=c7vNCoXeFCSGxp9VkZJs+ykDcbeFxquaSAx8F59gXc0=; b=JRZlr6en0+yzEm/2ndinSOBvv3DwajYa+MtiTwJBU8wV2gbg8XmidwOUZ9SAVFtxlvrOeaVJ0IhMXiLvfm9bTjbVxfLl5ZYr+Dcx/6yN8uu62usD2fqbBxLnWK46SaMfbzLHBogun3ot03FTNRg9E3g111gu1q6rDAY9zxtZWrg= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from [192.168.1.47] (97.77.122.57) by BN6PR11MB1633.namprd11.prod.outlook.com (10.172.23.22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.10; Thu, 21 Jul 2016 22:20:34 +0000 Subject: Re: less than 2 sec for response - possible? To: trafdev , References: <38b8542e-e011-370d-3863-207126ac9b33@mail.ru> <94532c82-1dfa-5904-da2c-09adcffcf4f3@toco-domains.de> <0461b3e8-cb38-0ded-6e8c-48e6dc545ea6@mail.ru> <0027eadb-4d83-14e5-e6a3-15fcf4376841@toco-domains.de> <1d480b23-b7ed-413e-5ce9-cbf974bd8a9f@mail.ru> <1dcc1237-6134-e8e7-124e-ce5fffcff30b@toco-domains.de> <613bc616-7a2a-d1a2-25d4-da06e82c4347@mail.ru> From: Jim Nasby Message-ID: <4fe2428d-1a6a-f637-65fd-78696a9eed7d@BlueTreble.com> Date: Thu, 21 Jul 2016 17:20:32 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.2.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: SN2PR80CA031.lamprd80.prod.outlook.com (10.141.39.169) To BN6PR11MB1633.namprd11.prod.outlook.com (10.172.23.22) X-MS-Office365-Filtering-Correlation-Id: d8659c9b-83c4-4299-8b9e-08d3b1b53c7b X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;2:DSEtrGxFpBQxHE3eSyFR0Gh2WZD4sbcwqe/4B2LH8TB02zY5rm0fm3Ku1HhgLWh+aVGgEGffdHbF8IwhH0EP6tOFycRQAKlnPbdNon3RrLH+WNjcam3MgbgE7Zt23L0DEgi0jrF+ps8X/154bN2dL+8Y4sGv4joALLurRa4U6+56G/r0JF/TUwpOUiZQLhmy;3:D+ANdUTxwnk+7QTxdjrf8zPBi8RuqnB6fGXQp29ED5KoL/Fl61GwYAhNGqk9Y32DoLxKlZK1aMVKWtb1tjHgEml2P8mPI0QT9yCR8n19AVM00dR89LcFSk6hbPEuKBJY;25:1Z/DgdaDl68I+RSI7MEF+5KgD6n1TN+r0nK7/hbgzRStLp6LNKpa+hhXAf85RNR54E2seQ3oL61lyXfIxXE93h9c5YpbONBnOF+54leZaYj2lJNxoH+FfF45ulH62hJNWMw5go3GZxtJ71cpE9PiLzL2QKncc9tGpFz16rJtFsBtQ2i3UGU2/NDiFDD+ZcPCEIof+gD9FkBpE95Bbs5A7CMzftmvHraYj1I4+XqI+l5MXUZMYtIf06ZJZ8NfE4cRxIAsdsvi572yIzLrhSHI6YUgRnC0WV6RPIpffDKtDZ7wt/r4sOk+mXaJ3UfZ2MkpJFHRcp3vj3vDmxD1pKvamLQOffbbtUOPSoVqNq/DA+aPpjXXK4PRCcVd+q5QqlOpz6sQT+iqcQubW0WmFp+qCAy9bR2OLMpNrr/JbvEPfdo=;31:D/g/XhIqIkv8ufodbEHxkw6H16B6u8f3kNbS16cyOBpl56y1WPiBj71y9g9QSQmasexevplKPP5PqrHStSr3n9NRzc5ytYYiwK7ZNboT8SzFydc2sw6/lN3PnKgN7vveF4c114ay4MWWL+sFdVpmmbcdN3MRTTeuqI/79V0z7zuSy1dOstZP5p+Joiix6sKGuDY+gMCs3Il2NrVpCc7z/g== X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1633; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:(166708455590820); X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040130)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6041072)(6043046);SRVR:BN6PR11MB1633;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1633; X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;4:yagAMNXhVXtXC5phLsEvVS3iwEqawoHPBCQmNQQa1/J3HzlYu6HETcAFjgMFamPfzcb4PmZ5auK7CTd+keCuwBmdVN6eUUmEf+01+D9ERjs89vddzNMnDvfUYqn2i2UgdCxUAHl5eSW0z2IKOllua7Ho+sTSQt5q3DgnAWbhR3uitqJQBlAw9ZvgksPxEg8SfdXernsYRkO246Yy8YGQwRYiXHr8YtejICiIWF8tghxTpJMYGR/90XOqXaNv3aJXjFmw307uSmGGF7zdCt3oBPu3XddGh/k++RVUzNMTButGYCHs57tOQ7Rjm6Ek35TKD4ln28L46B+DgGsRv4eI2exw875px8oG1hzfTRCUmMqqZjD8FfqV+pmXfJepqjLDcFlKkI1stfezuuOVSOlAov3bJ2DBjDdGIF5Vp6q8LbWP6w/p/F/q0t0IzSkDQ9ahDLArDG7F6pbZexmKRQ9VMQ== X-Forefront-PRVS: 0010D93EFE X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(6049001)(7916002)(377454003)(189002)(24454002)(199003)(4001350100001)(47776003)(5001770100001)(97736004)(2950100001)(189998001)(2906002)(64126003)(68736007)(107886002)(77096005)(101416001)(31686004)(117156001)(31696002)(50466002)(54356999)(42186005)(36756003)(50986999)(76176999)(16601075003)(106356001)(15975445007)(586003)(23676002)(86362001)(3846002)(81156014)(8676002)(81166006)(6116002)(66066001)(65806001)(105586002)(65956001)(305945005)(7846002)(93886004)(83506001)(7736002)(92566002)(33646002)(230700001)(19580395003)(65826006)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:BN6PR11MB1633;H:[192.168.1.47];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?MTtCTjZQUjExTUIxNjMzOzIzOkF1UGFObENaSGpvWXU3ZFZQcG1YUkdVS0h6?= =?utf-8?B?V1VaMXZ4dTJRMTJST0REYzh3dTFQYmJGSHlqUUpxb2RQTGEyWWdzZEZVY2Nq?= =?utf-8?B?LzBkNVZ0RmpXejF4bjRtZ0NSYnkyQ0QzQ0NlN0E3SE1VQUd3aHBSWnN1VVpF?= =?utf-8?B?ZWdhTzlheSt6a0l1R0krcHlicjNYOVZWUlVveGxvK0UzcmlZYUdmS2pFUWdX?= =?utf-8?B?cURMc0M1M0ZhckRINjMwRnFBdWthNVIvV0FZVjFTQjRnZ0UwZWJwUm5LTnor?= =?utf-8?B?ZmVQNk1pd1cyMFZEMVdIbVA0MkJacWU5aEZ5WkRQT1ozYWI3azBYRUc2WVpQ?= =?utf-8?B?NDh5enRwOW5DcTE1MEtBelpua0szcjRsRU1DbFR4U2pXSnlNM2NRYXVsWnJF?= =?utf-8?B?UUtDcDY4NkV4VWVZcG9zeXlEMXpjeVZqS2kvSVpiaUpMZitReTExRXRuRktD?= =?utf-8?B?alkwMzZibnY4MVdpUWVKVmc3ZTJqbVJMZTBtQVc1ajJkTExSREV1clY1Nzhu?= =?utf-8?B?eEVZdzNxMHk4aW5ocGVRNHhxeHQxOFNZZTRqUDBGanZ6dmhkNVBKSnFTZTlM?= =?utf-8?B?aEJjM1NHcVMxQmg3VkRlY0lGdXNzOUczT0dacWRPTnZNV0duZTRlOUlucVNx?= =?utf-8?B?algyT1RidmhrWmRraWJ1SkZwc3FRLzJ2RWk4NUdsUnAvRGt2UVUwTENYVTBO?= =?utf-8?B?V3owRU1JY3NEcExFZ0VuRXJ4a2t5dENyWXI3LzNOMG5mZmZneEdHOGhiNFow?= =?utf-8?B?Qzk3em5WUktrSnBlMG9wVDd2N1NpMHIzcXI3Z1dNSjFhM1VsbVJmUndCemhl?= =?utf-8?B?VlJlRnNlTklGUElScWtZTTJvdmdvbFdiT0Rzajkwb0RUU21NaHBYZ1RQbkha?= =?utf-8?B?ZVV0bU54L2xBWWxzMGlBTm1LTVR3ZXNydUZxWFhyTkZQa1JIK1RlNHRNVG5v?= =?utf-8?B?MGNSeUhxeS81ZmxuM2JsSjhrQ1V1V1BDU0drVmhrTHBxa0g1TElYeW4vcTZu?= =?utf-8?B?bXVPdnlBWkYwSmFmc2VKek9aczhQcmVKb0FwU3R0bHMvK3FKUlVsK1JRMmpy?= =?utf-8?B?UWJBamZDYkNRU2JhMU9SOXgzbGthNDRObXNSazhqV3FaaWJ0TnoyMVZibC9G?= =?utf-8?B?L1FVTmRXYjR5czkyRCtqQXROa1VLRmlGK1AyUlJRVGYxN1NDbkxERXQ0NW5Y?= =?utf-8?B?ZEY3ZFdZTGZQeGRuQzVveHo4c1l1MFA5OVpYT2NOL084QzdTaGZZbWlCeHRz?= =?utf-8?B?UEhCdks1VmwwSGdsWG9FWkRKd0NpZnV3TFpkR2RIV3lUZUpmK0VSdzhyclNS?= =?utf-8?B?ZFkrYldVblhzdU1BRzl1OEFzcWF2MWJ0SzBObWdLUUgreCtKZk1Xb1dFaVpU?= =?utf-8?B?WUFSVk1haVJmb3FaaHVydXlYRVFlK2xnQzNrcmdtb1l0Y2RaQ01qSll2Sk02?= =?utf-8?B?VEl4VkN5aFE1Qm0zRlRQMVRQSlhwdDRLL0xHaVlYdjdWd1lFc2Y4WmdPdnM4?= =?utf-8?B?Y0FTODhkU1NQYldjUHVJakhla3ovbFQrVGNTWjdSOEpwZEg2Qkx1VzRvaTJ3?= =?utf-8?B?ZmtRU0pZN2hZdm1pZGtneithRDA2eDBxY0t5anQwMEdxYTlIM2lKcldaMWQ2?= =?utf-8?B?OE5sbm9tRDd2Q3VRbkRoa3VrUmpvZkZwQ204WU1ncGMybTBjalBaUkFaQTlv?= =?utf-8?B?OTFwTXEwUXIvZXRTeHdmaklYNzlBb2o5NFQydS9QTUg5UHc4VlNkVzhHdGp6?= =?utf-8?B?enFwMEg4aFVBd0tobGNqM29RSVF6cXZIOHNhd09DWFB5UkZSTzA0cGlGMDFI?= =?utf-8?B?eWJsS2g4UkZDcmM3S0xNNjJsVVQrd214OFZlWmM5aXNWR2FHV3NYb2s3S1kz?= =?utf-8?Q?inVa7cCdLAE=3D?= X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;6:BO7zOLM4a1WsdV9T6LR7tAZu80/xjVfyy98CpSm3NPwZTcJY3Xzo5a+/6m7Y9aO0xW06OfLq3WgAcLTnUM0XxBrHgDzJkFP4uofODCxHv6H+7BVAZ5fl4y7QUsfmmK//H9TM45tuEHIeG8DgKE7+x23q8dahFfsRZRXyCkj8MaHnZE/UFqq3EEEG8ZdKscFRiBn7apKAcFKjRNd77YNdQLuXnBOvDcOb3WEjXjaT0uLMyztUGLoUZLdO6LTzezqGkiBMsdFkufp7+M9itF+V3VOOHVqE6blY5RpzfrNum6wEtbbm6wet04zVPnWNnw4R;5:EDlLBVqQuVrkZFi42DbN+rzxy2zjfmJ5dFhVjlLuxYwgN8DCfTmBMQiuVphFdeOEOXRUn8Gbu5UXFyB9WuPd82Ltb0lZl/6THCxRjZvQk11TrrviwS8IhIynLln+l+YjyW/VdI8CzJD1gTh6BMJjJg==;24:3tCzNZkyDKmQz2mQkypKHrsq8FDj1C763w13tc8Giw01dDoChebe2otPSS3qjNWYLyRUxFMgtmwXM2e0lGSAyNOZXXyVLg4zVVzh2NobN+o=;7:gqwl4NPUVpxuxUdCge61BfcuLTgHHLZzHvo5dwIOhwQpNbepiFoxM3UwqfKpdnpvWRBn5kG6/BqgZEgHagwOxbgx0ozw02hIDPK/7zzU1ebw++C2rkLOsDnygWc1tdeg9SDZK5qIi3f6k5GdavsqflkmAlTbv2bwWX5q0fgrBOKp38ZDtwoXYVoNjHQkkxh3PmOqA1XwookhO6uLen7VZvEUWknuyP3bMP8XsQ1KIQHfWxqxC76hSMrIi+b1LZmw SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 Jul 2016 22:20:34.6184 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1633 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 7/19/16 9:56 AM, trafdev wrote: > Will extending page to say 128K improve performance? Well, you can't go to more than 32K, but yes, it might. Even then, I think your biggest problem is that the data locality is too low. You're only grabbing ~3 rows every time you read a buffer that probably contains ~20 rows. So that's an area for improvement. The other thing that would help a lot is to trim the table down so it's not as wide. Actually, something else that could potentially help a lot is to store arrays of many data points in each row, either by turning each column into an array or storing an array of a composite type. [1] is exploring those ideas right now. You could also try cstore_fdw. It's not a magic bullet, but it's storage will be much more efficient than what you're doing right now. [1] https://github.com/ElephantStack/ElephantStack -- 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 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance