Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YIAkM-0007qt-A8 for pgsql-performance@arkaria.postgresql.org; Mon, 02 Feb 2015 06:44:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YIAkK-0004zy-Ub for pgsql-performance@arkaria.postgresql.org; Mon, 02 Feb 2015 06:44:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YIAkJ-0004zm-Nc for pgsql-performance@postgresql.org; Mon, 02 Feb 2015 06:44:31 +0000 Received: from mail-am1on0673.outbound.protection.outlook.com ([2a01:111:f400:fe00::673] helo=emea01-am1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YIAkF-00005U-Db for pgsql-performance@postgresql.org; Mon, 02 Feb 2015 06:44:30 +0000 Received: from AMSPR03MB518.eurprd03.prod.outlook.com (10.242.19.153) by AMSPR03MB519.eurprd03.prod.outlook.com (10.242.19.154) with Microsoft SMTP Server (TLS) id 15.1.75.20; Mon, 2 Feb 2015 06:31:24 +0000 Received: from AMSPR03MB518.eurprd03.prod.outlook.com ([10.242.19.153]) by AMSPR03MB518.eurprd03.prod.outlook.com ([10.242.19.153]) with mapi id 15.01.0075.002; Mon, 2 Feb 2015 06:31:24 +0000 From: Christian Weyer To: Tom Lane CC: Josh Berkus , "pgsql-performance@postgresql.org" Subject: Re: Unexpected (bad) performance when querying indexed JSONB column Thread-Topic: [PERFORM] Unexpected (bad) performance when querying indexed JSONB column Thread-Index: AQHQPYh+MPtYv9v9GUulrT5Ss+d0jZzcStTlgAAUZYCAAABpgIAAmcsA Date: Mon, 2 Feb 2015 06:31:24 +0000 Message-ID: References: <7F9BEAD7-C190-4E21-8240-04687FA88435@thinktecture.com> <54CE954F.7070904@agliodbs.com> <4791.1422829257@sss.pgh.pa.us> In-Reply-To: <4791.1422829257@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-MacOutlook/15.6.0.150113 x-originating-ip: [84.174.99.225] authentication-results: sss.pgh.pa.us; dkim=none (message not signed) header.d=none; x-microsoft-antispam: BCL:0;PCL:0;RULEID:;SRVR:AMSPR03MB519; x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:;SRVR:AMSPR03MB519; x-forefront-prvs: 0475418F50 x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(6009001)(51704005)(76176999)(33656002)(2950100001)(2656002)(40100003)(122556002)(62966003)(86362001)(77096005)(102836002)(77156002)(83506001)(82746002)(50986999)(87936001)(106116001)(110136001)(36756003)(92566002)(83716003)(54356999)(66066001)(46102003)(2900100001)(104396002); DIR:OUT; SFP:1101; SCL:1; SRVR:AMSPR03MB519; H:AMSPR03MB518.eurprd03.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; Content-Type: text/plain; charset="utf-8" Content-ID: <591E18EA1C9A464B9904EA2C325F6FAE@eurprd03.prod.outlook.com> Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-OriginatorOrg: thinktecture.com X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Feb 2015 06:31:24.1361 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: ed1f07e9-ad7c-4ea4-842e-2e16ed4db6e9 X-MS-Exchange-Transport-CrossTenantHeadersStamped: AMSPR03MB519 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 Pj4iQml0bWFwIEhlYXAgU2NhbiBvbiBhcnRpY2xlcyAgKGNvc3Q9MTYuMjUu LjEzNS42NCByb3dzPTMzIHdpZHRoPTQyNykgDQo+PiAoYWN0dWFsIHRpbWU9 Ni40MjUuLjQzLjYwMyByb3dzPTE4NTg0IGxvb3BzPTEpIg0KPj4gIiAgUmVj aGVjayBDb25kOiAoZGF0YSBAPiDigJh7ImxvY2tlZCI6IHRydWV9Jzo6anNv bmIpIg0KPj4gIiAgSGVhcCBCbG9ja3M6IGV4YWN0PTE0OTYiDQo+PiAiICBC dWZmZXJzOiBzaGFyZWQgaGl0PTE1MDQiDQo+PiAiICAtPiAgQml0bWFwIElu ZGV4IFNjYW4gb24gaWR4X2RhdGEgIChjb3N0PTAuMDAuLjE2LjI0IHJvd3M9 MzMgDQo+PndpZHRoPTApIA0KPj4gKGFjdHVhbCB0aW1lPTYuMDkwLi42LjA5 MCByb3dzPTE4NTg0IGxvb3BzPTEpIg0KPj4gIiAgICAgICAgSW5kZXggQ29u ZDogKGRhdGEgQD4g4oCYeyJsb2NrZWQiOiB0cnVlfSc6Ompzb25iKSINCj4+ ICIgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9OCINCj4+ICJQbGFubmlu ZyB0aW1lOiAwLjM0OCBtcyINCj4+ICJFeGVjdXRpb24gdGltZTogNDcuNzg4 IG1zIg0KPg0KPlNvIHRoYXQncyBzaG93aW5nIGEgcnVudGltZSBvZiA0OCBt cywgbm90IDkwMC4gIEZvciByZXRyaWV2aW5nIDE4NTg0DQo+cm93cywgZG9l c24ndCBzb3VuZCB0aGF0IGJhZCB0byBtZS4NCj4NCj4oSWYgdGhlIHBsYW5u ZXIgaGFkIGhhZCBhIGJldHRlciByb3djb3VudCBlc3RpbWF0ZSwgaXQnZCBs aWtlbHkgaGF2ZQ0KPm5vdCBib3RoZXJlZCB3aXRoIHRoZSBpbmRleCBhdCBh bGwgYnV0IGp1c3QgZG9uZSBhIHNlcXNjYW4uICBUaGlzIGlzDQo+YSBjb25z ZXF1ZW5jZSBvZiB0aGUgbGFjayBvZiBhbnkgdmVyeSB1c2VmdWwgc3RhdHMg Zm9yIEpTT05CIGNvbHVtbnMsDQo+d2hpY2ggaXMgc29tZXRoaW5nIHdlIGhv cGUgdG8gYWRkcmVzcyBzb29uOyBidXQgaXQncyBub3QgZG9uZSBpbiA5LjQN Cj5hbmQgbGlrZWx5IHdvbid0IGJlIGluIDkuNSBlaXRoZXIgLi4uKQ0KPg0K PgkJCXJlZ2FyZHMsIHRvbSBsYW5lDQoNClRoYW5rcyBmb3IgeW91ciBpbnNp Z2h0cy4NClRoaXMgZ3JlYXRseSBoZWxwZWQuDQoNCi1DLg0KDQoKLS0gClNl bnQgdmlhIHBnc3FsLXBlcmZvcm1hbmNlIG1haWxpbmcgbGlzdCAocGdzcWwt cGVyZm9ybWFuY2VAcG9zdGdyZXNxbC5vcmcpClRvIG1ha2UgY2hhbmdlcyB0 byB5b3VyIHN1YnNjcmlwdGlvbjoKaHR0cDovL3d3dy5wb3N0Z3Jlc3FsLm9y Zy9tYWlscHJlZi9wZ3NxbC1wZXJmb3JtYW5jZQo=