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 1iJJKP-0005uY-5y for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:29:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iJJJP-0002zg-Ts for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:28:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJJP-0002zC-Gm for pgsql-performance@lists.postgresql.org; Sat, 12 Oct 2019 15:28:07 +0000 Received: from mout.perfora.net ([74.208.4.196]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iJJJM-0000gd-7s for pgsql-performance@postgresql.org; Sat, 12 Oct 2019 15:28:06 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MdvNi-1hldBQ0VfS-00b1d3; Sat, 12 Oct 2019 17:27:58 +0200 Subject: Re: Optimising a two column OR check To: Jeff Janes Cc: Justin Pryzby , Ivan Voras , postgres performance list , Andrew Gierth References: <20191012144336.GA4475@telsasoft.com> From: MichaelDBA Message-ID: Date: Sat, 12 Oct 2019 11:27:55 -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: Content-Type: multipart/alternative; boundary="------------021A6EE0F6D7158984E586D3" Content-Language: en-US X-Provags-ID: V03:K1:bH31Gk91TVqiY261Rgq+IiT+aLMd5Glp5MtlVzeY2jDRTeitI9y KqvCVmxmROQy6qrd4mj9D5fxgh1bsnrf1mJcb9pk0Tg8gZbcoElXtZ3b7LhYoDLkxSRHju+ YWXaayqEyqzOlpEqF+5LaHIlzyn57PHj2jwNy3k1HLvNkReV3IF1/rcu8+3Jr3fMr1xBRIc Lk9wzsIDsiBAT0N3IQnvQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:sKSffclKSM0=:csAbZr3mVk/LEdUOa98lhs Zsho+hZGQjPM+yBslgbP+6F5jfa+aKcplqnIlZ0VTtJ9Wzra4MdjVxsMLVcRIPxBWag8fon2A wPNNA+W8IHFoKo0Ulwcyb61ycM39wHF1NRCezd2l/ty9ToV+wML0krzff9Vt34cdxXPJTjGfW LhfoEGhueZCFcX6wad8sDSbYX70TQu6xDhA05wxV1yo1thfjgS/7rmnE9EOl0vq1yd4vXHISj wL8jbsIZZr3q0XOCj65EC9/qi47a7hO9NMhG48qvjShhE8eyfqzv5yNbryZ/dpmI8sdGT+OE1 HP+weVUwIk5N2vzzQ9XCXKz7HqJKVyUgyzmnT+HHPH+sX2ZRWy50Q2DFY/tIvOx3s/BAAHI98 ZOVELO0sJpwGJ84suWxqcppEtrjgXL+faanI447nNHaVjp6QbATj2RAPY5FVGfxRwCCqjb3SA U6xNw6nfoCL6Sys//LlIktlyGKCAuy8HnUD+lHI+Rwjtd9D59sXTvcq6u7GEtY924zju2LAOM WcxSMzV2hS0fQIhFEBj5VIkVMu2zHrrbPBEI8GJ7ixwvQoiP9oZj2vFfsEZIZ8QkKeLPJ8w+4 9sMYjjIbrY4CUdsbo7kRXVd8HCjBaZSB9Nt8rFyXXNB5vHi59pzieLU5Z/tR5CaMdHZmbGqWR mpLKKRsKH3vkUSlgEM/YBvcvzxSTQd37Bo6/BMell9znhEtu3l52n0p7vB3HdJ78k4KzcR3mk nQZI4g0DtcUupiUoThfWI7n4TF79beCduRpJ34g9yB1eSmNm9xWBGKgfq0xmaxPeCJ3C37Pdt FvB9e1JEVOw2VaCpgssXbRshv5FuUAGf3xUHjLHZGn66S3Wd2kQ7SI15i0pLQCLzk7Igy7dnZ GueVLS4Vm4qtRzXBxocmMS7X0J+5P6II9yu5qBZls= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------021A6EE0F6D7158984E586D3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Another thing to consider is the visibility map.  From what I understand, index only scans are preferred for heavily updated tables, not infrequently updated ones.  Even though index only scans imply ONLY they really aren't in the sense that they may need to visit the Visibility Map for the heap. This can be costly and the planner may remove index only scan consideration if the VM has tuples that are not visible. BTW, to Andrew, the UNION ALL alternative still results in bitmap index scans from my testing. Regards, Michael Vitale Jeff Janes wrote on 10/12/2019 11:17 AM: > On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby > wrote: > > On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote: > > With seqscan disabled, I get this plan on 9.6: > >  Bitmap Heap Scan on friend  (cost=8.42..19.01 rows=14 width=8) > ... > > I expected to get an index-only scan in this situation, as that > would be a > > very common query. Is there a way to actually make this sort of > query > > resolvable with an index-only scan? Maybe a different table > structure would > > help? > > > It would have to scan the entire index to find the cases where > user2_id=42 but user1_id is not constrained. Technically User1_id > would be constrained to be less than 42, but I don't think the planner > will take that into account. > > > The v11 release notes have this relevant item: > > https://www.postgresql.org/docs/11/release-11.html > |Allow bitmap scans to perform index-only scans when possible > (Alexander Kuzmenkov) > > > But this is not one of those cases.  It is only possible when the only > data needed is whether the row exists or not. > > Cheers, > > Jeff --------------021A6EE0F6D7158984E586D3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Another thing to consider is the visibility map.  From what I understand, index only scans are preferred for heavily updated tables, not infrequently updated ones.  Even though index only scans imply ONLY they really aren't in the sense that they may need to visit the Visibility Map for the heap. This can be costly and the planner may remove index only scan consideration if the VM has tuples that are not visible.

BTW, to Andrew, the UNION ALL alternative still results in bitmap index scans from my testing.

Regards,
Michael Vitale



Jeff Janes wrote on 10/12/2019 11:17 AM:
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> With seqscan disabled, I get this plan on 9.6:
>  Bitmap Heap Scan on friend  (cost=8.42..19.01 rows=14 width=8)
...
> I expected to get an index-only scan in this situation, as that would be a
> very common query. Is there a way to actually make this sort of query
> resolvable with an index-only scan? Maybe a different table structure would
> help?

It would have to scan the entire index to find the cases where   user2_id=42 but user1_id is not constrained.  Technically User1_id would be constrained to be less than 42, but I don't think the planner will take that into account.


The v11 release notes have this relevant item:

https://www.postgresql.org/docs/11/release-11.html
|Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)


But this is not one of those cases.  It is only possible when the only data needed is whether the row exists or not.

Cheers,

Jeff

--------------021A6EE0F6D7158984E586D3--