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 1iJJQ9-00068y-Gv for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:35:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iJJPA-0000Ho-97 for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:34:04 +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 1iJJP9-0000HV-V5 for pgsql-performance@lists.postgresql.org; Sat, 12 Oct 2019 15:34:03 +0000 Received: from mout.perfora.net ([74.208.4.196]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJP7-0002lI-1L for pgsql-performance@postgresql.org; Sat, 12 Oct 2019 15:34:03 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LdnYT-1hbPvj0AAF-00iybA; Sat, 12 Oct 2019 17:33:56 +0200 Subject: Re: Optimising a two column OR check From: MichaelDBA To: Jeff Janes Cc: Justin Pryzby , Ivan Voras , postgres performance list , Andrew Gierth References: <20191012144336.GA4475@telsasoft.com> Message-ID: Date: Sat, 12 Oct 2019 11:33:53 -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="------------A23DB431BF2D1666672E1C9F" Content-Language: en-US X-Provags-ID: V03:K1:hxRHlz5u1dWxgT+TZaAlyRfROmmOi6YaJIUwgzmxmShv0+X+Khw UFl/th0WERJ3l0UksZVlm1tLZaf02M2iQY4kB9aOokYAxSW1+NzVo+grtHi0cFnQAfGeylw NiCv3O3SrsOhMrOkBCQ67oxuPtUSYvTCXMilZ3owoR7dMxT4vJG6Wsi00SjQpFQjkLQiDEv kz5XxYQJ/omDSBcxliuhA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:Hsf1k/dyCL0=:dNsZQXQu0z9n2tKXFYJigV efu9FHPEqEeQD9XRBeGftenG/6d3PSX+dZOklpqDAYt1JXEVbhBeZyVhUjntnSssu8jgVJRCQ veV8mIpGdSuNzTUTwBG4Hd4UUkyX2wtEG7194sMC+DtTASBPpOg997+/11YN3imoxYBDkGP6y 2tMpy49OKEVGqkAIcm86petfuLr/Pk9CFMb/5DdPtrW24+hoZTQq5DdqoKGopFkEVvui3veAb zwoWFmNPMMPwXMzp789dumsp2/O7ll6n9B5jw00K2EiUSrOYK4GqaD/2jpL23M8C61srpM1az TaIBh+bvLwa7v4+51mEbA9807QW9M1C4WK5LKkasXEpoHpg1UpTJELwuVTy+PO7iGbqAmGMiJ HgBlqn7ucltkGJqxHkVrrd+NGPaNJquqdPVrbsSoPvJmDY9AU9+G9ltcRkMnJp4kpG7KM4jhA fjJbXxZ0kJj4BfglkYo2Hsoe3bqgBX4PbXtEryRBNIZxCIw6259IhEc2CeGQnX4Pwytop0sCZ OrQ55JdZxKaJEeZgVmGCRuMe3gKkhU8F5Ymm3IWQdcByBfzivc1ua5LUyM6/w7d+hxxkiAC53 D3He+dWPiBqO0D4yHHUV1JW3HJ6D04DAp0QNT7VqwcSILZT0uG0WxDzSS7/7l53ti8m8MSNrf /l0cxJKC4txXdLSRc5GN3wM8eQCV3TCHW0MDBVU+q06H2K11Q4/MykuhFIwaCnhhiGuUb4jKb G3ndxE8ZBQ7NJfSgR2A6e8NKzimH9EwtLyOc/AcktYk+zKAOcF73Y3NcbF1/0efe0H8T32hOp UX/zx5Gio0xaMN725ZFthRt722svzm9U0DbTEaVgSBbskZKM7XhpSgaTde8SH1na96LyhqCnj 5FgVQUPRRaWKnVF3WyEMZr9igmzKXc38vb6o2yAiU= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A23DB431BF2D1666672E1C9F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Yikes, apologies to all, my wording is the opposite of what I meant! Index only scans are preferred for infrequently updated ones, not heavily updated ones where the visibility map is updated often. Regards, Michael Vitale MichaelDBA wrote on 10/12/2019 11:27 AM: > 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 > --------------A23DB431BF2D1666672E1C9F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Yikes, apologies to all, my wording is the opposite of what I meant!

Index only scans are preferred for infrequently updated ones, not heavily updated ones where the visibility map is updated often.

Regards,
Michael Vitale


MichaelDBA wrote on 10/12/2019 11:27 AM:
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


--------------A23DB431BF2D1666672E1C9F--