Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gf2ZA-0003a0-Vm for pgsql-performance@arkaria.postgresql.org; Thu, 03 Jan 2019 12:57:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gf2Z8-0007Wz-DA for pgsql-performance@arkaria.postgresql.org; Thu, 03 Jan 2019 12:57:38 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1gf2Z8-0007Wm-2k for pgsql-performance@lists.postgresql.org; Thu, 03 Jan 2019 12:57:38 +0000 Received: from smtp3.iter.org ([193.51.56.5]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gf2Z0-0002ks-GC for pgsql-performance@lists.postgresql.org; Thu, 03 Jan 2019 12:57:37 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iter.org; l=8946; q=dns/txt; s=20170425; t=1546520651; x=1547125451; h=from:to:cc:subject:date:message-id:references: in-reply-to:content-transfer-encoding:mime-version; bh=Vw8VjAL9TjAAPI9GD517PVa8qyel+RP+PUa4Vp4w76w=; b=mV5Y1pRXGFbrP6A7mtc7Hm9mzB0zzKF8LahMVCETyiXZ+s8HdyEdpKhT TmajaKeTR9PtIHA0ujbGL2TGZ0tfAlgGcVZaz1BDnAJF/uHrDeSHm3Jxs Z3BluRluhws6W+vwjXsrVqjwwQuPxgB7JUolzrwNpBfnxTjE7leLiq0jP E=; Received: from XCH-CLU1N3.iter.org (10.10.242.164) by XCH-CLU1N4.iter.org (10.10.243.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1261.35; Thu, 3 Jan 2019 13:57:28 +0100 Received: from XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74]) by XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74%16]) with mapi id 15.01.1261.036; Thu, 3 Jan 2019 13:57:28 +0100 From: Abadie Lana To: David Rowley CC: "pgsql-performance@lists.postgresql.org" Subject: RE: select query does not pick up the right index Thread-Topic: select query does not pick up the right index Thread-Index: AdSitwt5/9TyB7hoTX++E94tUlkiMgAOgIkAABh1BzA= Date: Thu, 3 Jan 2019 12:57:27 +0000 Message-ID: <49406fb0c239480980ffc6c2a250d4e9@iter.org> References: In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.14.32.57] Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk DQoNCg0KTGFuYSBBQkFESUUNCkRhdGFiYXNlIEVuZ2luZWVyDQpDT0RBQyBTZWN0aW9uDQoNCklU RVIgT3JnYW5pemF0aW9uLCBCdWlsZGluZyA3Mi80MTA4LCBTQ09ELCBDb250cm9sIFN5c3RlbSBE aXZpc2lvbg0KUm91dGUgZGUgVmlub24tc3VyLVZlcmRvbiAtIENTIDkwIDA0NiAtIDEzMDY3IFN0 IFBhdWwgTGV6IER1cmFuY2UgQ2VkZXggLSBGcmFuY2UNClBob25lOiArMzMgNCA0MiAxNyA4NCAw Mg0KR2V0IHRoZSBsYXRlc3QgSVRFUiBuZXdzIG9uIGh0dHA6Ly93d3cuaXRlci5vcmcvd2hhdHNu ZXcNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IERhdmlkIFJvd2xleSA8ZGF2 aWQucm93bGV5QDJuZHF1YWRyYW50LmNvbT4gDQpTZW50OiAwMyBKYW51YXJ5IDIwMTkgMDE6MTYN ClRvOiBBYmFkaWUgTGFuYSA8TGFuYS5BYmFkaWVAaXRlci5vcmc+DQpDYzogcGdzcWwtcGVyZm9y bWFuY2VAbGlzdHMucG9zdGdyZXNxbC5vcmcNClN1YmplY3Q6IFJlOiBzZWxlY3QgcXVlcnkgZG9l cyBub3QgcGljayB1cCB0aGUgcmlnaHQgaW5kZXgNCg0KT24gVGh1LCAzIEphbiAyMDE5IGF0IDA1 OjI4LCBBYmFkaWUgTGFuYSA8TGFuYS5BYmFkaWVAaXRlci5vcmc+IHdyb3RlOg0KPiBJIHdvdWxk IGFwcHJlY2lhdGUgYW55IGhpbnRzIGFzIHRoaXMgcHJvYmxlbSBsb29rcyB0byBtZSByYXRoZXIg c3RyYW5nZeKApkkgdHJpZWQgdG8gZ29vZ2xlIGl0IGJ1dCBpbiB2YWluLg0KPg0KPiBzZWxlY3Qg dC5uYW1lLCBjLnNtcGxfdGltZSxjLm5hbm9zZWNzLGMuZmxvYXRfdmFsLGMubnVtX3ZhbCxjLnN0 cl92YWwsYy5kYXRhdHlwZSxjLmFycmF5X3ZhbCBmcm9tIHNhbXBsZSBjLCBjaGFubmVsIHQgd2hl cmUgdC5jaGFubmVsX2lkPWMuY2hhbm5lbF9pZCBhbmQgdC5uYW1lPSdCVUlMLUIzNi1WQS1SVC1S VDE6Q0wwMDAxLTItQUJXJyAgIG9yZGVyIGJ5IGMuc21wbF90aW1lIGRlc2MgbGltaXQgNTsNCj4N Cj4gdGFrZXMgMjBtbiB0byBleGVjdXRlIGJlY2F1c2UgaXQgcGlja3MgdXAgdGhlIHdyb25nIGlu ZGV44oCmc2VlIGV4cGxhaW4gYW5hbHlzZSBiZWxvdy4gSSB3b3VsZCBleHBlY3QgdGhpcyBxdWVy eSB0byB1c2UgdGhlIChjaGFubmVsX2lkLHNtcGxfdGltZSkgYnV0IGl0IHVzZXMgdGhlIHNtcGxf dGltZSBpbmRleC4NCg0KWy4uLl0NCg0KPiBBbnkgaWRlYXMsIHdoeSB0aGUgcGxhbm5lciBpcyBu b3QgdGFraW5nIHRoZSByaWdodCBpbmRleD8NCg0KVGhlIHBsYW5uZXIgYXNzdW1lcyB0aGF0IHRo ZSByZXF1aXJlZCBjaGFubmVsIHZhbHVlcyBhcmUgZXZlbmx5IGRpc3RyaWJ1dGVkIHRocm91Z2gg dGhlIHNjYW4gb2YgdGhlIGluZGV4IG9uIHNtcGxfdGltZS4gIElmIHlvdXIgcmVxdWlyZWQgNSBy b3dzIHdlcmUgZm91bmQgcXVpY2tseSAoaS5lIGNoYW5uZWxzIHdpdGggcmVjZW50IHNhbXBsZSB2 YWx1ZXMpLCB0aGVuIHRoZSBwbGFuIHdvdWxkIGhhdmUgd29ya2VkIG91dCB3ZWxsLiAgSXQgbG9v a3MgbGlrZSAnQlVJTC1CMzYtVkEtUlQtUlQxOkNMMDAwMS0yLUFCVycgaXMgcHJvYmFibHkgYSBj aGFubmVsIHdoaWNoIGhhcyBzb21lIHZlcnkgb2xkIHNhbXBsZSB2YWx1ZXMuIEkgY2FuIHNlZSB0 aGF0IGZyb20gIlJvd3MgUmVtb3ZlZCBieSBKb2luDQpGaWx0ZXI6IDMyMjA5OTQ3MSIsICBtZWFu aW5nIHRoYXQgb24gYmFja3dhcmRzIHNjYW5uaW5nIHRoZSBzbXBsX3RpbWUgaW5kZXgsIHRoYXQg bWFueSByb3dzIHdlcmUgZm91bmQgbm90IHRvIG1hdGNoIHRoZSBjaGFubmVsIHlvdSByZXF1ZXN0 ZWQuDQoNClRoZSBwbGFubmVyLCBieSBkZWZhdWx0IG9ubHkgaGFzIHN0YXRpc3RpY3MgdG8gc2F5 IGhvdyBjb21tb24gZWFjaCBjaGFubmVsIGlzIGluIHRoZSBzYW1wbGUgdGFibGUuIEkgdGhpbmsg aW4gdGhpcyBjYXNlIHNpbmNlIHRoZSBwbGFubmVyIGhhcyBubyBrbm93bGVkZ2Ugb2Ygd2hpY2gg Y2hhbm5lbF9pZCBpdCB3aWxsIGJlIHNlYXJjaGluZyBmb3IgKHRoYXQncyBvbmx5IGRldGVybWlu ZWQgZHVyaW5nIGV4ZWN1dGlvbiksIHRoZW4gSSBzdXBwb3NlIGl0IG11c3QgYmUgdXNpbmcgdGhl IG5fZGlzdGluY3Qgb2YgdGhlIHNhbXBsZS5jaGFubmVsX2lkIHRhYmxlLiAgSXQgd291bGQgYmUg aW50ZXJlc3RpbmcgdG8ga25vdyBob3cgZmFyIG9mZiB0aGUgbl9kaXN0aW5jdCBlc3RpbWF0aW9u IGlzLiBZb3UgY2FuIGZpbmQgb3V0IHdpdGg6DQoNCnNlbGVjdCBzdGFkaXN0aW5jdCBmcm9tIHBn X3N0YXRpc3RpYyB3aGVyZSBzdGFyZWxpZD0nc2FtcGxlJzo6cmVnY2xhc3MgYW5kIHN0YWF0dG51 bSA9IDE7IHNlbGVjdCBjb3VudCgqKSBmcm9tIChzZWxlY3QgZGlzdGluY3QgY2hhbm5lbF9pZCBm cm9tIHNhbXBsZSkgczsgLS0gdGhpcyBtYXkgdGFrZSBhIHdoaWxlIHRvIHJ1bi4uLg0KDQpJZiB0 aGUgc3RhZGlzdGluY3QgZXN0aW1hdGUgaXMgZmFyIG91dCBmcm9tIHRoZSByZWFsaXR5LCB0aGVu IHlvdSBjb3VsZCBjb25zaWRlciBzZXR0aW5nIHRoaXMgbWFudWFsbHkgd2l0aDoNCg0KYWx0ZXIg dGFibGUgc2FtcGxlIGFsdGVyIGNvbHVtbiBjaGFubmVsX2lkIHNldCAobl9kaXN0aW5jdCA9IDxh Y3R1YWwgdmFsdWUgaGVyZT4pOw0KDQpidXQga2VlcCBpbiBtaW5kLCB0aGF0IGFzIHRoZSB0YWJs ZSBldm9sdmVzLCB3aGF0ZXZlciB5b3Ugc2V0IHRoZXJlIGNvdWxkIGJlY29tZSBvdXRkYXRlZC4N Cg0KQW5vdGhlciBtZXRob2QgdG8gZml4IHlvdSBjb3VsZCB0cnkgd291bGQgYmUgdG8gY29heCB0 aGUgcGxhbm5lciBpbnRvIGRvaW5nIHNvbWV0aGluZyBkaWZmZXJlbnQgd291bGQgYmUgdG8gZ2l2 ZSBpdCBhIGJldHRlciBpbmRleCB0byB3b3JrIHdpdGguDQoNCmNyZWF0ZSBpbmRleCBvbiBjaGFu bmVsKG5hbWUsIGNoYW5uZWxfaWQpOw0KDQpZb3UgZGlkbid0IHNob3cgdXMgdGhlIGRldGFpbHMg ZnJvbSB0aGUgY2hhbm5lbCB0YWJsZSwgYnV0IGlmIHRoZXJlJ3Mgbm90IGFuIGluZGV4IGxpa2Ug dGhpcyB0aGVuIHRoaXMgbWlnaHQgcmVkdWNlIHRoZSBjb3N0IG9mIGEgTWVyZ2UgSm9pbiwgYnV0 IHNpbmNlIHRoZSBvcmRlciByb3dzIG91dHB1dCBmcm9tIHRoYXQgam9pbiB3b3VsZCBiZSBpbiBj aGFubmVsX2lkIG9yZGVyLCBhIFNvcnQgd291bGQgYmUgcmVxdWlyZWQsIHdoaWNoIHdvdWxkIHJl cXVpcmUgam9pbmluZyBhbGwgbWF0Y2hpbmcgcm93cywgbm90IGp1c3QgdGhlIGZpcnN0IDUgbWF0 Y2hlcy4gRGVwZW5kaW5nIG9uIGhvdyBtYW55IHJvd3MgYWN0dWFsbHkgbWF0Y2ggd2lsbCBkZXRl cm1pbmUgaWYgdGhhdCdzIGZhc3RlciBvciBub3QuDQoNCklmIHlvdSBkb24ndCBoYXZlIGx1Y2sg d2l0aCBlaXRoZXIgb2YgdGhlIGFib3ZlIHRoZW4sIG9uZSBvdGhlciB0aGluZyB5b3UgY291bGQg dHJ5IHdvdWxkIGJlIHRvIGRpc2FsbG93IHRoZSBwbGFubmVyIGZyb20gdXNpbmcgdGhlIHNtcGxf dGltZSBpbmRleCBieSBjaGFuZ2luZyB0aGUgb3JkZXIgYnkgdG8gIk9SREVSIEJZIGMuc21wbF90 aW1lICsNCklOVEVSVkFMICcwIHNlYyc7ICAgdGhhdCdzIGEgYml0IG9mIGEgaGFjaywgYnV0IHdl IGRvbid0IGhhdmUgYW55dGhpbmcNCndlIG9mZmljaWFsbHkgY2FsbCAicXVlcnkgaGludHMiIGlu IFBvc3RncmVTUUwsIHNvIG9mdGVuIHdlJ3JlIGxlZnQgdG8gc29sdmUgaXNzdWVzIGxpa2UgdGhp cyB3aXRoIHVnbHkgdHJpY2tzIGxpa2UgdGhhdC4NCg0KQWxzbywgZ29pbmcgYnk6DQoNCj4gLT4g IFNlcSBTY2FuIG9uIGNoYW5uZWwgdCAgKGNvc3Q9MC4wMC4uOTE1LjgzIHJvd3M9MSB3aWR0aD00 MSkgKGFjdHVhbCANCj4gLT4gdGltZT00LjY4My4uNy44ODUgcm93cz0xIGxvb3BzPTEpDQoNCnBl cmhhcHMgIm5hbWUiIGlzIHVuaXF1ZSBvbiB0aGUgY2hhbm5lbCB0YWJsZT8gIChJIGRvdWJ0IHRo ZXJlJ3MgYW4gaW5kZXgvY29uc3RyYWludCB0byBiYWNrIHRoYXQgdXAsIGhvd2V2ZXIsIHNpbmNl IHN1Y2ggYW4gaW5kZXggd291bGQgaGF2ZSBsaWtlbHkgYmVlbiB1c2VkIGhlcmUgaW5zdGVhZCBv ZiB0aGUgU2VxIFNjYW4pDQoNCklmIHNvLCBhbmQgeW91IGNhbiBhZGQgYSBjb25zdHJhaW50IHRv IGJhY2sgdGhhdCB1cCwgeW91IG1pZ2h0ICBiZSBhYmxlIHRvIHJlZm9ybSB0aGUgcXVlcnkgdG8g YmU6DQoNCnNlbGVjdCAnQlVJTC1CMzYtVkEtUlQtUlQxOkNMMDAwMS0yLUFCVycsDQpjLnNtcGxf dGltZSxjLm5hbm9zZWNzLGMuZmxvYXRfdmFsLGMubnVtX3ZhbCxjLnN0cl92YWwsYy5kYXRhdHlw ZSxjLmFycmF5X3ZhbA0KZnJvbSBzYW1wbGUgYw0KV0hFUkUgYy5jaGFubmVsX2lkID0gKFNFTEVD VCBjaGFubmVsX2lkIEZST00gY2hhbm5lbCBXSEVSRQ0KbmFtZT0nQlVJTC1CMzYtVkEtUlQtUlQx OkNMMDAwMS0yLUFCVycpDQpvcmRlciBieSBjLnNtcGxfdGltZSBkZXNjIGxpbWl0IDU7DQoNCklm IHlvdSBjYW4gZG8gdGhhdCB0aGVuIGl0J3MgaGlnaGx5IGxpa2VseSB0byBiZSAqdmVyeSogZmFz dCB0byBleGVjdXRlIHNpbmNlIEkgc2VlIHRoZXJlJ3MgYW4gaW5kZXggb24gKGNoYW5uZWxfaWQs IHNtcGxfdGltZSkgb24gZWFjaCBvZiB0aGUgaW5oZXJpdGVkIHRhYmxlcy4NCg0KKElmIG91ciBw bGFubmVyIHdhcyBzbWFydGVyIHRoZW4gaW4gdGhlIHByZXNlbmNlIG9mIHRoZSBjb3JyZWN0IHVu aXF1ZSBpbmRleCwgd2UgY291bGQgaGF2ZSByZXdyaXR0ZW4gdGhlIHF1ZXJ5IGFzIHN1Y2ggYXV0 b21hdGljYWxseS4uLi4gYnV0IGl0J3Mgbm90IC8gd2UgZG9uJ3QuICBJIGJlbGlldmUgSSd2ZSBt ZW50aW9uZWQgYWJvdXQgaW1wcm92aW5nIHRoaXMgc29tZXdoZXJlIGluIHRoZSBkaXN0YW50IHBh c3Qgb2YgdGhlIC1oYWNrZXJzIG1haWxpbmcgbGlzdCwgYWx0aG91Z2ggSSBjYW4ndCBmaW5kIGl0 IHJpZ2h0IG5vdy4gSSByZWNhbGwgbmFtaW5nIHRoZSBpZGVhICJzY2FsYXIgdmFsdWUgbG9va3Vw IGpvaW5zIiwgYnV0IGRldmVsb3BtZW50IGRpZG4ndCBnZXQgbXVjaCBiZXlvbmQgdGhpbmtpbmcg b2YgdGhhdCBuYW1lKQ0KDQotLSANCiBEYXZpZCBSb3dsZXkgICAgICAgICAgICAgICAgICAgaHR0 cDovL3d3dy4ybmRRdWFkcmFudC5jb20vDQogUG9zdGdyZVNRTCBEZXZlbG9wbWVudCwgMjR4NyBT dXBwb3J0LCBUcmFpbmluZyAmIFNlcnZpY2VzDQoNCg0KDQoNCkhpIERhdmlkDQpUaGFua3MgZm9y IHlvdXIgdGlwcy4gU28gYW5zd2VycyB0byB5b3VyIHF1ZXN0aW9ucy9jb21tZW50cw0KMSkgQWJv dXQgbl9kaXN0aW5jdA0KZmlyc3QgcXVlcnkgcmV0dXJucyAyMTM2LCBzZWNvbmQgcXVlcnkgcmV0 dXJucyAzMzQyNS4gU28gaXQgc2VlbXMgdGhhdCB0aGVyZSBpcyBzb21lIGRpc2NyZXBhbmNpZXMu Li5BbHNvIHRoZSBzYW1wbGUgdGFibGUgaXMgdmVyeSBiaWcuLi5yb3VnaGx5IDMyMjA5OTQ3NCBy b3dzLg0KSSBkaWQgdGhlIGFsdGVyIHN0YXRlbWVudCBidXQgd2l0aG91dCBzdWNjZXNzLiBTdGls bCBsb25nIGV4ZWN1dGlvbiB0aW1lIHdpdGggd3JvbmcgaW5kZXgNCjIpIGluZGV4IG9uIGNoYW5u ZWwobmFtZSxjaGFubmVsX2lkKQ0KVGhlcmUgd2FzIG5vIGluZGV4ZXMgb24gY2hhbm5lbC4gU28g SSBjcmVhdGVkIGl0LiBTYW1lIGV4ZWN1dGlvbiB0aW1lLCBzdGlsbCB3cm9uZyBpbmRleCB1c2Vk IHJlZ2FyZGxlc3Mgb2YgdGhlIG5fZGlzdGluY3QgdmFsdWVzDQozKVRoZSAidHJpY2siICgrIGlu dGVydmFsICcwcycpIGRpZCB0aGUgam9iLiBUaGUgaW5kZXggb24gY2hhbm5lbF9pZCwgc21wbF90 aW1lIGlzIHVzZWQuIFF1ZXJ5IHRpbWUgY2FuIHZhcnkgYmV0d2VlbiBhIGZldyBtcyB0byAyNSBz ZWMNCjQpIG5hbWUgaXMgdW5pcXVlLCBjb25zdHJhaW50IGFuZCBpbmRleCBjcmVhdGVkLiBSaWdo dCBpbmRleCBpcyBwaWNrZWQgdXAgYW5kIHF1ZXJ5IHRpbWUgaXMgcmF0aGVyIGNvbnN0YW50IHRo ZXJlIDQwc2VjLg0KDQpBIGZldyBjb21tZW50cyA6IA0KLSBJIGhhdmUgZGlzYWJsZWQgZm9yY2Vf cGFyYWxsZWxfbW9kZSB3aGVuIHJ1bm5pbmcgYWxsIHRoZSB0ZXN0cy4gDQotIFRoZSBkaWZmZXJl bmNlIGJldHdlZW4gdGhlIHR3byBwbGFucyBpcyBpbiB0aGUgY2FzZSBvZiBxdWVyeSB3aXRoIHRo ZSB0cmljaywgdGhlIHBsYW5uZXIgaXMgdXNpbmcgYSBiaXRtYXAgaW5kZXggc2NhbiwgaW4gdGhl IHNlY29uZCBvbmUgaXQgdXNlcyBpbmRleCBzY2FuIGJhY2t3YXJkLg0KLSB3aGVuIEkgZXhlY3V0 ZSB0aGUgaW5pdGlhbCBxdWVyeSwgdGhlcmUgaXMgYSBiaWcgcmVhZCBhY2Nlc3Mgb24gZGlzayBh bG1vc3QgMTcuNyBHQi4uLndoZXJlYXMgdGhlIHRvdGFsIHNpemUgb2YgdGhlIHNtcGxfdGltZSBp bmRleCBpcyByb3VnaGx5IDdHQi4uLkNvdWxkIGl0IGJlIGEgd3JvbmcgY29uZmlndXJhdGlvbiBv biBteSBzaWRlPw0KRHVyaW5nIHRoZSB0ZXN0cywgbm8gaW5zZXJ0L2RlbGV0ZS9vciB1cGRhdGUg d2FzIHBlcmZvcm1lZC4uLm9ubHkgbXkgc2VsZWN0IHF1ZXJpZXMuLi4NCk1haW4gcGFyYW1ldGVy cyA6IGVmZmVjdGl2ZV9jYWNoZV9zaXplIDogNEdCLCBzaGFyZWRfYnVmZmVycyA0R0IsIHdvcmtf bWVtIDRNQg0KDQpUaGFua3MgYSBsb3QgIQ0KDQoNCg0KDQoNCg0K