Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YHaUD-0004iy-EK for pgsql-performance@arkaria.postgresql.org; Sat, 31 Jan 2015 16:01:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YHaUB-0007Vx-TK for pgsql-performance@arkaria.postgresql.org; Sat, 31 Jan 2015 16:01:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YHaUA-0007Vq-Bd for pgsql-performance@postgresql.org; Sat, 31 Jan 2015 16:01:26 +0000 Received: from mail-am1on0632.outbound.protection.outlook.com ([2a01:111:f400:fe00::632] helo=emea01-am1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YHaU1-0000CJ-RA for pgsql-performance@postgresql.org; Sat, 31 Jan 2015 16:01:23 +0000 Received: from AMSPR03MB518.eurprd03.prod.outlook.com (10.242.19.153) by AMSPR03MB517.eurprd03.prod.outlook.com (10.242.19.139) with Microsoft SMTP Server (TLS) id 15.1.75.20; Sat, 31 Jan 2015 16:00:42 +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; Sat, 31 Jan 2015 16:00:42 +0000 From: Christian Weyer To: "pgsql-performance@postgresql.org" Subject: Unexpected (bad) performance when querying indexed JSONB column Thread-Topic: Unexpected (bad) performance when querying indexed JSONB column Thread-Index: AQHQPW8QYSaMNliBxUyfW8E8A6GfWw== Date: Sat, 31 Jan 2015 16:00:42 +0000 Message-ID: <0776A628-6450-4E46-B059-1BAD56BA50AE@thinktecture.com> 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.116.144] authentication-results: postgresql.org; dkim=none (message not signed) header.d=none; x-microsoft-antispam: BCL:0;PCL:0;RULEID:;SRVR:AMSPR03MB517; x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:;SRVR:AMSPR03MB517; x-forefront-prvs: 0473A03F3F x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(6009001)(53754006)(164054003)(229853001)(83506001)(50986999)(107886001)(106116001)(110136001)(82746002)(83716003)(2656002)(2351001)(54356999)(450100001)(16236675004)(66066001)(40100003)(77156002)(62966003)(2900100001)(86362001)(102836002)(46102003)(92566002)(87936001)(77096005)(33656002)(122556002)(36756003)(104396002); DIR:OUT; SFP:1101; SCL:1; SRVR:AMSPR03MB517; H:AMSPR03MB518.eurprd03.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; Content-Type: multipart/alternative; boundary="_000_0776A62864504E46B0591BAD56BA50AEthinktecturecom_" MIME-Version: 1.0 X-OriginatorOrg: thinktecture.com X-MS-Exchange-CrossTenant-originalarrivaltime: 31 Jan 2015 16:00:42.5221 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: ed1f07e9-ad7c-4ea4-842e-2e16ed4db6e9 X-MS-Exchange-Transport-CrossTenantHeadersStamped: AMSPR03MB517 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 --_000_0776A62864504E46B0591BAD56BA50AEthinktecturecom_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 SGkgYWxsLA0KDQpUaGUgcGcgdmVyc2lvbiBpbiBxdWVzdGlvbiBpcyB0aGUgbGF0ZXN0IDkuNC4s IHJ1bm5pbmcgb24gV2luZG93cy4NCg0KRm9yIHRlc3Rpbmcgb3V0IHRoZSBOb1NRTCBmZWF0dXJl cyBvZiBwZyBJIGhhdmUgYSBzaW1wbGUgdGFibGUgY2FsbGVkIOKAmGFydGljbGVz4oCZIHdpdGgg YSBjb2x1bW4gY2FsbGVkIOKAmGRhdGHigJkuDQoNClRoZXJlIGlzIGFuIGluZGV4IG9uIOKAmGRh dGHigJkgbGlrZSB0aGlzOg0KICBDUkVBVEUgSU5ERVggaWR4X2RhdGEgT04gYXJ0aWNsZXMgVVNJ TkcgZ2luIChkYXRhIGpzb25iX3BhdGhfb3BzKTsNCg0KVGhlIGN1cnJlbnQgdGVzdCBkYXRhIHNl dCBoYXMgMzI1NzAgZW50aXJlcyBvZiBKU09OIGRvY3MgbGlrZSB0aGlzOg0Kew0KInRpdGxlIjog IkZvbyBCYXIiLA0KImxvY2tlZCI6IHRydWUsDQoidmFsaWRfdW50aWwiOiAiMjA0OS0xMi0zMVQw MDowMDowMCIsDQoiYXJ0X251bWJlciI6IDEyMzQ1Njc4LA0KInZhbGlkX3NpbmNlIjogIjIwMTMt MTAtMDVUMDA6MDA6MDAiLA0KIm51bWJlcl92YWxpZCI6IGZhbHNlLA0KImNvbWJpbmF0aW9ucyI6 IHsNCiJ2YXIxIjogIjQ3MTEiLA0KInZhcjIiOiAiNDcxMSIsDQoidmFyMyI6ICIwODE1IiwNCiJp bnRfYXJ0X251bWJlciI6ICIwMDAwMDEiDQp9DQp9DQoNCk5vdGhpbmcgdG9vIGNvbXBsZXgsIEkg dGhpbmsuDQoNCldoZW4gSSBydW4gYSBzaW1wbGUgcXVlcnk6DQogIFNFTEVDVCBkYXRhICAjPj4g 4oCYeyJ0aXRsZSJ9Jw0KICBGUk9NIGFydGljbGVzDQogIFdIRVJFIGRhdGEgQD4gJ3sg4oCcbG9j a2VkIiA6IHRydWUgfSc7DQoNClJlcHJvZHVjaW5nbHksIGl0IHRha2VzIGFwcHJveC4gOTAwbXMg dG8gZ2V0IHRoZSByZXN1bHRzIGJhY2suDQpIb25lc3RseSwgSSB3YXMgZXhwZWN0aW5nIGEgbXVj aCBmYXN0ZXIgcXVlcnkuDQoNCkFueSBvcGluaW9ucyBvbiB0aGlzPw0KDQpUaGFua3MsDQotQy4N Cg0K --_000_0776A62864504E46B0591BAD56BA50AEthinktecturecom_ Content-Type: text/html; charset="utf-8" Content-ID: Content-Transfer-Encoding: base64 PGh0bWw+DQo8aGVhZD4NCjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRlbnQtVHlwZSIgY29udGVudD0i dGV4dC9odG1sOyBjaGFyc2V0PXV0Zi04Ij4NCjwvaGVhZD4NCjxib2R5IHN0eWxlPSJ3b3JkLXdy YXA6IGJyZWFrLXdvcmQ7IC13ZWJraXQtbmJzcC1tb2RlOiBzcGFjZTsgLXdlYmtpdC1saW5lLWJy ZWFrOiBhZnRlci13aGl0ZS1zcGFjZTsgY29sb3I6IHJnYigwLCAwLCAwKTsgZm9udC1zaXplOiAx NHB4OyBmb250LWZhbWlseTogQ2FsaWJyaSwgc2Fucy1zZXJpZjsiPg0KPGRpdj4NCjxkaXY+DQo8 ZGl2PkhpIGFsbCw8L2Rpdj4NCjxkaXY+PGJyPg0KPC9kaXY+DQo8ZGl2PlRoZSBwZyB2ZXJzaW9u IGluIHF1ZXN0aW9uIGlzIHRoZSBsYXRlc3QgOS40LiwgcnVubmluZyBvbiBXaW5kb3dzLjwvZGl2 Pg0KPGRpdj48YnI+DQo8L2Rpdj4NCjxkaXY+Rm9yIHRlc3Rpbmcgb3V0IHRoZSBOb1NRTCBmZWF0 dXJlcyBvZiBwZyBJIGhhdmUgYSBzaW1wbGUgdGFibGUgY2FsbGVkIOKAmGFydGljbGVz4oCZIHdp dGggYSBjb2x1bW4gY2FsbGVkIOKAmGRhdGHigJkuPC9kaXY+DQo8ZGl2Pjxicj4NCjwvZGl2Pg0K PGRpdj5UaGVyZSBpcyBhbiBpbmRleCBvbiDigJhkYXRh4oCZIGxpa2UgdGhpczo8L2Rpdj4NCjxk aXY+Jm5ic3A7IENSRUFURSBJTkRFWCBpZHhfZGF0YSBPTiBhcnRpY2xlcyBVU0lORyBnaW4gKGRh dGEganNvbmJfcGF0aF9vcHMpOzwvZGl2Pg0KPGRpdj4NCjxkaXYgaWQ9IiI+DQo8ZGl2IHN0eWxl PSJjb2xvcjogcmdiKDAsIDAsIDApOyBmb250LXNpemU6IDE0cHg7IGZvbnQtZmFtaWx5OiBDYWxp YnJpLCBzYW5zLXNlcmlmOyI+DQo8YnI+DQo8L2Rpdj4NCjxkaXYgc3R5bGU9ImNvbG9yOiByZ2Io MCwgMCwgMCk7IGZvbnQtc2l6ZTogMTRweDsgZm9udC1mYW1pbHk6IENhbGlicmksIHNhbnMtc2Vy aWY7Ij4NClRoZSBjdXJyZW50IHRlc3QgZGF0YSBzZXQgaGFzIDMyNTcwIGVudGlyZXMgb2YgSlNP TiBkb2NzIGxpa2UgdGhpczo8L2Rpdj4NCjxkaXYgc3R5bGU9ImNvbG9yOiByZ2IoMCwgMCwgMCk7 IGZvbnQtc2l6ZTogMTRweDsgZm9udC1mYW1pbHk6IENhbGlicmksIHNhbnMtc2VyaWY7Ij4NCjxk aXY+ezwvZGl2Pg0KPGRpdj48c3BhbiBjbGFzcz0iQXBwbGUtdGFiLXNwYW4iIHN0eWxlPSJ3aGl0 ZS1zcGFjZTpwcmUiPjwvc3Bhbj4mcXVvdDt0aXRsZSZxdW90OzogJnF1b3Q7Rm9vIEJhciZxdW90 Oyw8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFuIiBzdHlsZT0id2hpdGUt c3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7bG9ja2VkJnF1b3Q7OiB0cnVlLCZuYnNwOzwvZGl2Pg0K PGRpdj48c3BhbiBjbGFzcz0iQXBwbGUtdGFiLXNwYW4iIHN0eWxlPSJ3aGl0ZS1zcGFjZTpwcmUi Pjwvc3Bhbj4mcXVvdDt2YWxpZF91bnRpbCZxdW90OzogJnF1b3Q7MjA0OS0xMi0zMVQwMDowMDow MCZxdW90OywmbmJzcDs8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFuIiBz dHlsZT0id2hpdGUtc3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7YXJ0X251bWJlciZxdW90OzogMTIz NDU2NzgsJm5ic3A7PC9kaXY+DQo8ZGl2PjxzcGFuIGNsYXNzPSJBcHBsZS10YWItc3BhbiIgc3R5 bGU9IndoaXRlLXNwYWNlOnByZSI+PC9zcGFuPiZxdW90O3ZhbGlkX3NpbmNlJnF1b3Q7OiAmcXVv dDsyMDEzLTEwLTA1VDAwOjAwOjAwJnF1b3Q7LCZuYnNwOzwvZGl2Pg0KPGRpdj48c3BhbiBjbGFz cz0iQXBwbGUtdGFiLXNwYW4iIHN0eWxlPSJ3aGl0ZS1zcGFjZTpwcmUiPjwvc3Bhbj4mcXVvdDtu dW1iZXJfdmFsaWQmcXVvdDs6IGZhbHNlLCZuYnNwOzwvZGl2Pg0KPGRpdj48c3BhbiBjbGFzcz0i QXBwbGUtdGFiLXNwYW4iIHN0eWxlPSJ3aGl0ZS1zcGFjZTpwcmUiPjwvc3Bhbj4mcXVvdDtjb21i aW5hdGlvbnMmcXVvdDs6IHs8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFu IiBzdHlsZT0id2hpdGUtc3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7dmFyMSZxdW90OzogJnF1b3Q7 NDcxMSZxdW90OywmbmJzcDs8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFu IiBzdHlsZT0id2hpdGUtc3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7dmFyMiZxdW90OzogJnF1b3Q7 NDcxMSZxdW90OywmbmJzcDs8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFu IiBzdHlsZT0id2hpdGUtc3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7dmFyMyZxdW90OzogJnF1b3Q7 MDgxNSZxdW90OywmbmJzcDs8L2Rpdj4NCjxkaXY+PHNwYW4gY2xhc3M9IkFwcGxlLXRhYi1zcGFu IiBzdHlsZT0id2hpdGUtc3BhY2U6cHJlIj48L3NwYW4+JnF1b3Q7aW50X2FydF9udW1iZXImcXVv dDs6ICZxdW90OzAwMDAwMSZxdW90OzwvZGl2Pg0KPGRpdj48c3BhbiBjbGFzcz0iQXBwbGUtdGFi LXNwYW4iIHN0eWxlPSJ3aGl0ZS1zcGFjZTpwcmUiPjwvc3Bhbj59PC9kaXY+DQo8ZGl2Pn08L2Rp dj4NCjxkaXY+PGJyPg0KPC9kaXY+DQo8ZGl2Pk5vdGhpbmcgdG9vIGNvbXBsZXgsIEkgdGhpbmsu PC9kaXY+DQo8ZGl2Pjxicj4NCjwvZGl2Pg0KPGRpdj5XaGVuIEkgcnVuIGEgc2ltcGxlIHF1ZXJ5 OjwvZGl2Pg0KPGRpdj4NCjxkaXY+Jm5ic3A7IFNFTEVDVCBkYXRhICZuYnNwOyMmZ3Q7Jmd0OyDi gJh7JnF1b3Q7dGl0bGUmcXVvdDt9JyZuYnNwOzwvZGl2Pg0KPGRpdj4mbmJzcDsgRlJPTSBhcnRp Y2xlczwvZGl2Pg0KPGRpdj4mbmJzcDsgV0hFUkUgZGF0YSBAJmd0OyAneyDigJxsb2NrZWQmcXVv dDsgOiB0cnVlIH0nOzwvZGl2Pg0KPC9kaXY+DQo8ZGl2Pjxicj4NCjwvZGl2Pg0KPGRpdj5SZXBy b2R1Y2luZ2x5LCBpdCB0YWtlcyBhcHByb3guIDkwMG1zIHRvIGdldCB0aGUgcmVzdWx0cyBiYWNr LjwvZGl2Pg0KPGRpdj5Ib25lc3RseSwgSSB3YXMgZXhwZWN0aW5nIGEgbXVjaCBmYXN0ZXIgcXVl cnkuPC9kaXY+DQo8ZGl2Pjxicj4NCjwvZGl2Pg0KPGRpdj5Bbnkgb3BpbmlvbnMgb24gdGhpcz88 L2Rpdj4NCjxkaXY+PGJyPg0KPC9kaXY+DQo8ZGl2PlRoYW5rcyw8L2Rpdj4NCjwvZGl2Pg0KPGRp diBzdHlsZT0iY29sb3I6IHJnYigwLCAwLCAwKTsgZm9udC1zaXplOiAxNHB4OyBmb250LWZhbWls eTogQ2FsaWJyaSwgc2Fucy1zZXJpZjsiPg0KLUMuPC9kaXY+DQo8ZGl2IHN0eWxlPSJjb2xvcjog cmdiKDAsIDAsIDApOyBmb250LXNpemU6IDE0cHg7IGZvbnQtZmFtaWx5OiBDYWxpYnJpLCBzYW5z LXNlcmlmOyI+DQo8YnI+DQo8L2Rpdj4NCjwvZGl2Pg0KPC9kaXY+DQo8L2Rpdj4NCjwvZGl2Pg0K PC9ib2R5Pg0KPC9odG1sPg0K --_000_0776A62864504E46B0591BAD56BA50AEthinktecturecom_--