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 1iJJbj-0006Tm-Om for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:47:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iJJaj-0001jJ-OR for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:46:01 +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 1iJJaj-0001iu-EY for pgsql-performance@lists.postgresql.org; Sat, 12 Oct 2019 15:46:01 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJaY-0002u3-IE for pgsql-performance@postgresql.org; Sat, 12 Oct 2019 15:46:00 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0M6kp0-1i6zN81GyK-00wXLV; Sat, 12 Oct 2019 17:45:46 +0200 Subject: Re: Optimising a two column OR check From: MichaelDBA To: postgres performance list Cc: Jeff Janes , Justin Pryzby , Ivan Voras , Andrew Gierth References: <20191012144336.GA4475@telsasoft.com> <87y2xqvv1x.fsf@news-spur.riddles.org.uk> <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com> Message-ID: <0517fa14-6a74-bcec-0655-9e240618c46f@sqlexec.com> Date: Sat, 12 Oct 2019 11:45:45 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.5.2 MIME-Version: 1.0 In-Reply-To: <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com> Content-Type: multipart/alternative; boundary="------------02BB60CF103BE623E8EBB963" Content-Language: en-US X-Provags-ID: V03:K1:1N4aXC7lYxeI5K4BLz0kL9fupO96kDJfxUvxxvU/wSYYItW3t+F qoXFWGSG7RD/gqb1s+V/7m0npbW+xSdMfCACq6UbfjPMTfXXT8vyDhMwHWEcXlzbl2fn1nP onYEbSywNYeta5yPhWElbXivyVoYeRRr5GknP7C7q/YEqaeI92DvbxuIUovHSEgu2o04qoL J38t4k4OeHLgOwntRyfEQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:mqbM5Yi3/Kc=:+pOo6bcT2329eqPm+8rVDL O5A/XrHDxijCnbhzzmh3/PtBX6AjXDXjCguNWnPRtBmDoWj3dLXrT3e0rM/mAaxgGiRvJssol +R4M5Vc6BynxFao0FloGA3fte3594IzmiuwsBWlGKnctRqF8f52PirFeVr7960YRWGnjBKcOb ge4bM2wGH/CCVKncpuIzVbfjAr60gelP9OsQj68nHKgtB4IWm4MXv9TArX4M3PWBo9X4R/6mq ExAfv86g+Tkjkop0DXVqxagcH1Ghu38G/dSv4KHGknwxq6s9fsGDt7xywGEN+pUCwepY/Qvxf 8jrmo66ZrmrcvXho5OFoDsrhvUdWxhrxxZ2s5bLcd2IKyWv9oI/uoNEzECEP13mFVAcYmV+vf o0DAFS9XWucOGHBTmGe1CLPucNiQb9cunu0Q3+zHg3nAOh9kp0zR+IPCL+EI3XS6lJeoO+6oJ xw74Aq9sK6i1Bfeh5iYBmoTxof+zKGbLVw+t0lizO1EJyJhWMKmSsqgys2fav5sOvEMMmiC9O 8SXXQqrLT2sMgNO2nLjlBha4dM+zFjHdsrKhU5Cm52b6nKnGjbaDZfpTHtiOO2VJxyhFG6wq2 Jo7XZdyAkOdm9ucnQgJHuY6NsNg0G3/FKJMxILiFMIapyzODgQvjYn2+nc+PQIfSk7hc1g78P n7WTOswbR/qymBWPiF3l3hkheJAGvG0WsAjWG0vqBrlBbF9fGf2UPSC7gkJhz+FyHFdbsiaix uU/r1lDM3GfgIEVsEzBI4wojTZc4k3X9kTQc7HxrpHiQcWU1AdqAIuxt7x/wjY2Ym1oUxNAoO HGZzOGLyNaPoCdMuL6+nAKRckRtk7EcOhcvREZyrYnHUeOlAsW1HlHE321WLvIqXKjjarNQuK QbxfQScOzOy5ShXbN9QW9G5VE4nRG4P2BFz6aqkEI= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------02BB60CF103BE623E8EBB963 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Perhaps the fix by Alexander Kuzmenkov in V11 added this VM consideration for having a preference of bitmap index scan over an index only scan.  Looks like I'm goin' down the rabbit hole... Regards, Michael Vitale MichaelDBA wrote on 10/12/2019 11:35 AM: > Nope, vacuumed it and still got the bitmap index scans. > > Andrew Gierth wrote on 10/12/2019 11:33 AM: >>>>>>> "MichaelDBA" == MichaelDBA  writes: >> MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in >>   MichaelDBA> bitmap index scans from my testing. >> >> You probably forgot to vacuum the table. >> > > > --------------02BB60CF103BE623E8EBB963 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Perhaps the fix by Alexander Kuzmenkov in V11 added this VM consideration for having a preference of bitmap index scan over an index only scan.  Looks like I'm goin' down the rabbit hole...

Regards,
Michael Vitale

MichaelDBA wrote on 10/12/2019 11:35 AM:
Nope, vacuumed it and still got the bitmap index scans.

Andrew Gierth wrote on 10/12/2019 11:33 AM:
"MichaelDBA" == MichaelDBA  <MichaelDBA@sqlexec.com> writes:
  MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in
  MichaelDBA> bitmap index scans from my testing.

You probably forgot to vacuum the table.





--------------02BB60CF103BE623E8EBB963--