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 1hed5Y-0000WA-1H for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Jun 2019 10:17:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hed5V-0006Ha-OO for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Jun 2019 10:17:37 +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 1hed5V-0006Gy-4V for pgsql-hackers@lists.postgresql.org; Sat, 22 Jun 2019 10:17:37 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hed5O-0001Qv-Aj for pgsql-hackers@postgresql.org; Sat, 22 Jun 2019 10:17:35 +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 x5MAH5Hk022448; Sat, 22 Jun 2019 12:17:05 +0200 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 2t99x584ek-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Sat, 22 Jun 2019 12:17:05 +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.1263.5; Sat, 22 Jun 2019 12:17:05 +0200 Received: from opammb0561.comp.optiver.com ([fe80::4c35:85e9:1255:ee9f]) by opammb0561.comp.optiver.com ([fe80::4c35:85e9:1255:ee9f%17]) with mapi id 15.00.1263.000; Sat, 22 Jun 2019 12:17:05 +0200 From: Floris Van Nee To: Dmitry Dolgov <9erthalion6@gmail.com>, David Rowley , James Coleman , "jesper.pedersen@redhat.com" CC: 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: AQHVKONuvKZ8sYg0dki2OxSpfaGT6w== Date: Sat, 22 Jun 2019 10:17:04 +0000 Message-ID: <1561198638439.56867@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> , 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-24704.003 x-tm-as-result: No--3.772500-0.000000-31 x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-06-22_06:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk The following sql statement seems to have incorrect results - some logic in= the backwards scan is currently not entirely right.=0A= =0A= -Floris=0A= =0A= =0A= drop table if exists a;=0A= create table a (a int, b int, c int);=0A= insert into a (select vs, ks, 10 from generate_series(1,5) vs, generate_ser= ies(1, 10000) ks);=0A= create index on a (a,b);=0A= analyze a;=0A= select distinct on (a) a,b from a order by a desc, b desc;=0A= explain select distinct on (a) a,b from a order by a desc, b desc;=0A= =0A= DROP TABLE=0A= CREATE TABLE=0A= INSERT 0 50000=0A= CREATE INDEX=0A= ANALYZE=0A= a | b =0A= ---+-------=0A= 5 | 10000=0A= 5 | 1=0A= 4 | 1=0A= 3 | 1=0A= 2 | 1=0A= 1 | 1=0A= (6 rows)=0A= =0A= QUERY PLAN = =0A= ---------------------------------------------------------------------------= ------=0A= Index Only Scan Backward using a_a_b_idx on a (cost=3D0.29..1.45 rows=3D5= width=3D8)=0A= Scan mode: Skip scan=0A= (2 rows)=0A= =0A=