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 1heoIR-00006L-PQ for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Jun 2019 22:15:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1heoIO-00086j-GU for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Jun 2019 22:15:40 +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 1heoIO-00086B-7Q for pgsql-hackers@lists.postgresql.org; Sat, 22 Jun 2019 22:15:40 +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 1heoIG-00056v-QG for pgsql-hackers@postgresql.org; Sat, 22 Jun 2019 22:15:39 +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 x5MMFIi4032326; Sun, 23 Jun 2019 00:15:18 +0200 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 2t99x58939-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Sun, 23 Jun 2019 00:15:18 +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; Sun, 23 Jun 2019 00:15:17 +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; Sun, 23 Jun 2019 00:15:17 +0200 From: Floris Van Nee To: Dmitry Dolgov <9erthalion6@gmail.com> CC: David Rowley , James Coleman , "jesper.pedersen@redhat.com" , Rafia Sabih , "Jeff Janes" , Thomas Munro , "Peter Geoghegan" , Tomas Vondra , "Thomas Munro" , PostgreSQL-development , Alexander Korotkov Subject: Re: Index Skip Scan Thread-Topic: Index Skip Scan Thread-Index: AQHVKUbLnc9LMxpfxE2Ouc8qsa3cxQ== Date: Sat, 22 Jun 2019 22:15:17 +0000 Message-ID: <1561241730766.23014@Optiver.com> References: <2685f020-f302-9bf6-1b5c-ebc2bcebc635@2ndquadrant.com> <1559361698401.22864@Optiver.com> <1559362223126.18661@Optiver.com> <988b7763-5c55-1be1-b7b6-f3a68eff0d40@redhat.com> <1559763453006.85615@Optiver.com> <1559763570329.85227@Optiver.com> <4d469bf2-49ff-bc3d-a428-6c7b5d87d526@redhat.com> <1561198638439.56867@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-24706.002 x-tm-as-result: No--7.705500-0.000000-31 x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_156124173076623014Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-06-22_14:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_156124173076623014Optivercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > Thanks for testing! You're right, looks like in the current implementatio= n in > case of backwards scan there is one unnecessary extra step forward. It se= ems > this mistake was made, since I was concentrating only on the backward sca= ns > with a cursor, and used not exactly correct approach to wrap up after a s= can > was finished. Give me a moment, I'll tighten it up. Thanks. Looking forward to it. I think I found some other strange behavior.= Given the same table as in my previous e-mail, the following queries also = return inconsistent results. I spent some time trying to debug it, but can'= t easily pinpoint the cause. It looks like it also skips over one value too= much, my guess is during _bt_skippage call in _bt_skip? Perhaps a question: when stepping through code in GDB, is there an easy way= to pretty print for example the contents on an IndexTuple? I saw there's s= ome tools out there that can pretty print plans, but viewing tuples is more= complicated I guess. -- this one is OK postgres=3D# select distinct on (a) a,b from a where b>1; a | b ---+--- 1 | 2 2 | 2 3 | 2 4 | 2 5 | 2 (5 rows) -- this one is not OK, it seems to skip too much postgres=3D# select distinct on (a) a,b from a where b=3D2; a | b ---+--- 1 | 2 3 | 2 5 | 2 (3 rows) --_000_156124173076623014Optivercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable


> Thanks for testing! You're right, looks like in = the current implementation in
>= ; case of backwards scan there is one unnecessary extra step fo= rward. It seems
>= ; this mistake was made, since I was concentrating only on the = backward scans
>= ; with a cursor, and used not exactly correct approach to wrap = up after a scan
>= ; was finished. Give me a moment, I'll tighten it up.

Thanks. Looking forward to it. I think I found some other = strange behavior. Given the same table as in my previous e-mail, the follow= ing queries also return inconsistent results. I spent some time trying to d= ebug it, but can't easily pinpoint the cause. It looks like it also skips over one value too much, my guess i= s during _bt_skippage call in _bt_skip?
Perhaps a question: when stepping through code in GDB, is = there an easy way to pretty print for example the contents on an Index= Tuple? I saw there's some tools out there that can pretty print plans,= but viewing tuples is more complicated I guess.

-- this one is OK
postgres=3D# select distinct on (a) a,b from a where b>1;
 a | b 
---+---
 1 | 2
 2 | 2
 3 | 2
 4 | 2
 5 | 2
(5 rows)

-- this one is not OK, it seems to skip too much
postgres=3D# select distinct on (a) a,b from a where b=3D2;
 a | b 
---+---
 1 | 2
 3 | 2
 5 | 2
(3 rows)


--_000_156124173076623014Optivercom_--