Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XpSr7-0004T6-5r for pgsql-performance@arkaria.postgresql.org; Sat, 15 Nov 2014 02:12:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XpSr6-0004c9-MT for pgsql-performance@arkaria.postgresql.org; Sat, 15 Nov 2014 02:12:52 +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 1XpSr5-0004c3-U2 for pgsql-performance@postgresql.org; Sat, 15 Nov 2014 02:12:51 +0000 Received: from mail-by2on0640.outbound.protection.outlook.com ([2a01:111:f400:fc0c::640] helo=na01-by2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XpSr1-0007w7-AC for pgsql-performance@postgresql.org; Sat, 15 Nov 2014 02:12:50 +0000 Received: from decina.local (24.55.12.91) by CY1PR11MB0188.namprd11.prod.outlook.com (25.160.228.18) with Microsoft SMTP Server (TLS) id 15.1.16.15; Sat, 15 Nov 2014 02:12:40 +0000 Message-ID: <5466B690.5000705@BlueTreble.com> Date: Fri, 14 Nov 2014 20:12:32 -0600 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: =?UTF-8?B?QXJ0xatyYXMgTGFwaW5za2Fz?= , Tom Lane CC: postgres performance list Subject: Re: Index order ignored after `is null` in query References: <20141105214243.GA546@public-docking-cx-1771.ethz.ch> <20141106170612.GA527@public-docking-cx-1226.ethz.ch> <10744.1415294592@sss.pgh.pa.us> <20141107111451.GA477@guest-docking-cx-1-0139.ethz.ch> In-Reply-To: <20141107111451.GA477@guest-docking-cx-1-0139.ethz.ch> Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [24.55.12.91] X-ClientProxiedBy: BLUPR05CA0045.namprd05.prod.outlook.com (10.141.20.15) To CY1PR11MB0188.namprd11.prod.outlook.com (25.160.228.18) X-Microsoft-Antispam: UriScan:; X-Microsoft-Antispam: BCL:0;PCL:0;RULEID:;SRVR:CY1PR11MB0188; X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:;SRVR:CY1PR11MB0188; X-Forefront-PRVS: 03965EFC76 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(199003)(189002)(24454002)(479174003)(377454003)(65806001)(87976001)(59896002)(4396001)(46102003)(21056001)(47776003)(40100003)(20776003)(65956001)(99396003)(120916001)(102836001)(66066001)(50466002)(80316001)(19580395003)(97736003)(64126003)(92726001)(76176999)(86362001)(95666004)(15975445006)(23676002)(83506001)(33656002)(101416001)(62966003)(107046002)(77096003)(15202345003)(93886004)(77156002)(122386002)(64706001)(65816999)(105586002)(42186005)(54356999)(50986999)(106356001)(36756003)(87266999)(81973001)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR11MB0188; H:decina.local; FPR:; MLV:sfv; PTR:InfoNoRecords; A:1; MX:1; LANG:en; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:;SRVR:CY1PR11MB0188; X-OriginatorOrg: bluetreble.com 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 11/7/14, 5:14 AM, Artūras Lapinskas wrote: > thanks for your time and answer. Not treating IS NULL as equality operator definitely helps me to make more sense out of previous explains. You can also try creating a partial index WHERE b IS NULL. WHERE b IS NOT NULL can also sometimes be useful, though for different reasons. -- Jim Nasby, Data Architect, Blue Treble Consulting 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