From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>, Andy Fan <zhihui.fan1213@gmail.com>
CC: PostgreSQL-development <pgsql-hackers@postgresql.org>,
        Jesper Pedersen
	<jesper.pedersen@redhat.com>,
        David Rowley <dgrowleyml@gmail.com>,
        "Kyotaro
 Horiguchi" <horikyota.ntt@gmail.com>,
        Peter Geoghegan <pg@bowt.ie>, "Thomas
 Munro" <thomas.munro@gmail.com>,
        Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        Dilip Kumar <dilipbalaut@gmail.com>
Thread-Topic: Index Skip Scan (new UniqueKeys) [External]
Thread-Index: AQHWPkejvFmf8VE85UaktavhwXWuBqjS8neAgByLE4CAEbyf4A==
Date: Fri, 10 Jul 2020 17:03:37 +0000
Message-ID: <fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.optiver.com>
References: <20200609102247.jdlatmfyeecg52fi@localhost>
 <CAKU4AWrqYz7-+rr=mGe8w6i=NsAZap6cKe=QoQsYGeb4AuiSaA@mail.gmail.com>
 <20200629120709.52w2zi36mtzyliv2@localhost>
In-Reply-To: <20200629120709.52w2zi36mtzyliv2@localhost>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: RE: Index Skip Scan (new UniqueKeys)
Precedence: bulk

Hi Dmitry,

Also took another look at the patch now, and found a case of incorrect data=
. It looks related to the new way of creating the paths, as I can't recall =
seeing this in earlier versions.

create table t1 as select a,b,b%5 as c, random() as d from generate_series(=
1, 10) a, generate_series(1,100) b;
create index on t1 (a,b,c);

postgres=3D# explain select distinct on (a) * from t1 order by a,b desc,c;
                                  QUERY PLAN                               =
   =20
---------------------------------------------------------------------------=
----
 Sort  (cost=3D2.92..2.94 rows=3D10 width=3D20)
   Sort Key: a, b DESC, c
   ->  Index Scan using t1_a_b_c_idx on t1  (cost=3D0.28..2.75 rows=3D10 wi=
dth=3D20)
         Skip scan: true
(4 rows)


With the 'order by a, b desc, c' we expect the value of column 'b' to alway=
s be 100. With index_skipscan enabled, it always gives 1 though. It's not c=
orrect that the planner chooses a skip scan followed by sort in this case.

-Floris