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 1i33fz-0007Yv-Vc for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Aug 2019 19:32:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i33fy-0000HA-Fn for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Aug 2019 19:32:14 +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 1i33fy-0000Gy-7J for pgsql-hackers@lists.postgresql.org; Wed, 28 Aug 2019 19:32:14 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i33fv-0004S1-Ps for pgsql-hackers@postgresql.org; Wed, 28 Aug 2019 19:32:13 +0000 Received: from pps.filterd (smtp03.optiver.com [127.0.0.1]) by smtp03.optiver.com (8.16.0.27/8.16.0.27) with SMTP id x7SJMkwJ019016; Wed, 28 Aug 2019 21:31:52 +0200 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 2ujtu5j16f-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Wed, 28 Aug 2019 21:31:52 +0200 Received: from opammb0561.comp.optiver.com (10.131.160.1) by opammb0561.comp.optiver.com (10.131.160.1) with Microsoft SMTP Server (TLS) id 15.0.1473.3; Wed, 28 Aug 2019 21:31:52 +0200 Received: from opammb0561.comp.optiver.com ([fe80::9d46:f4ee:846a:2858]) by opammb0561.comp.optiver.com ([fe80::9d46:f4ee:846a:2858%17]) with mapi id 15.00.1473.003; Wed, 28 Aug 2019 21:31:52 +0200 From: Floris Van Nee To: Dmitry Dolgov <9erthalion6@gmail.com> CC: Kyotaro Horiguchi , "jesper.pedersen@redhat.com" , David Rowley , Thomas Munro , James Coleman , Rafia Sabih , Jeff Janes , Peter Geoghegan , Tomas Vondra , Thomas Munro , Bhushan Uparkar , PostgreSQL-development , Alexander Korotkov Subject: Re: Index Skip Scan Thread-Topic: Index Skip Scan Thread-Index: AQHVXdR+7yStoDGNZEuLWnNsdB4+cQ== Date: Wed, 28 Aug 2019 19:31:51 +0000 Message-ID: <1567020711705.41641@Optiver.com> References: <1d917274-73c1-24de-9faf-c1d2c542cb23@redhat.com> <20190725.201737.192223037.horikyota.ntt@gmail.com> <20190725.202950.257050501.horikyota.ntt@gmail.com> <1564999539204.96859@Optiver.com> <1565080675672.86115@Optiver.com>, In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.131.160.182] x-tm-as-product-ver: SMEX-11.0.0.1284-8.100.1062-24876.002 x-tm-as-result: No--7.594600-0.000000-31 x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_156702071170541641Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-08-28_10:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_156702071170541641Optivercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > Sorry for long delay. Here is more or less what I had in mind. After chan= ging > read_closest to read the whole page I couldn't resist to just merge it in= to > readpage itself, since it's basically the same. It could raise questions = about> > performance and how intrusive this patch is, but I hope it's not that muc= h of a > problem (in the worst case we can split it back). I've also added few tes= ts for > the issue you've mentioned. Thanks again, I'm appreciate how much efforts= you > put into reviewing! Putting it into one function makes sense I think. Looking at the patch, I t= hink in general there are some good improvements in there. I'm afraid I did manage to find another incorrect query result though, havi= ng to do with the keepPrev part and skipping to the first tuple on an index= page: postgres=3D# drop table if exists b; create table b as select a,b::int2 b,(= b%2)::int2 c from generate_series(1,5) a, generate_series(1,366) b; create = index on b (a,b,c); analyze b; DROP TABLE SELECT 1830 CREATE INDEX ANALYZE postgres=3D# set enable_indexskipscan=3D1; SET postgres=3D# select distinct on (a) a,b,c from b where b>=3D1 and c=3D0 ord= er by a,b; a | b | c ---+---+--- 1 | 2 | 0 2 | 4 | 0 3 | 4 | 0 4 | 4 | 0 5 | 4 | 0 (5 rows) postgres=3D# set enable_indexskipscan=3D0; SET postgres=3D# select distinct on (a) a,b,c from b where b>=3D1 and c=3D0 ord= er by a,b; a | b | c ---+---+--- 1 | 2 | 0 2 | 2 | 0 3 | 2 | 0 4 | 2 | 0 5 | 2 | 0 (5 rows) -Floris --_000_156702071170541641Optivercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable


> Sorry for long delay. Here is more or le= ss what I had in mind. After changing
> 

Putting it into one function makes sense I think. = Looking at the patch, I think in general there are some good improvements i= n there.

I'm afraid I did manage to find another incorrect = query result though, having to do with the keepPrev part and skipping = to the first tuple on an index page:

postgres=3D# drop table if exists b; create table b as select a,b::int= 2 b,(b%2)::int2 c from generate_series(1,5) a, generate_series(1,366) b; cr= eate index on b (a,b,c); analyze b;
DROP TABLE
SELECT 1830
CREATE INDEX
ANALYZE
postgres=3D# set enable_indexskipscan=3D1;
SET
postgres=3D# select distinct on (a) a,b,c from b where b>=3D1 and c= =3D0 order by a,b;
 a | b | c 
---+---+---
 1 | 2 | 0
 2 | 4 | 0
 3 | 4 | 0
 4 | 4 | 0
 5 | 4 | 0
(5 rows)

postgres=3D# set enable_indexskipscan=3D0;
SET
postgres=3D# select distinct on (a) a,b,c from b where b>=3D1 and c= =3D0 order by a,b;
 a | b | c 
---+---+---
 1 | 2 | 0
 2 | 2 | 0
 3 | 2 | 0
 4 | 2 | 0
 5 | 2 | 0
(5 rows)


-Floris

--_000_156702071170541641Optivercom_--