From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
CC: Andy Fan <zhihui.fan1213@gmail.com>,
        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: 
 AQHWPkejvFmf8VE85UaktavhwXWuBqjS8neAgByLE4CAEbyf4IABZkuAgAFvkyCAA0bPAIAAK8DAgA2NqYCAACIEEA==
Date: Thu, 23 Jul 2020 11:43:56 +0000
Message-ID: <8a483a1889b24e888f6cf37bc5baea85@opammb0561.comp.optiver.com>
References: <20200609102247.jdlatmfyeecg52fi@localhost>
 <CAKU4AWrqYz7-+rr=mGe8w6i=NsAZap6cKe=QoQsYGeb4AuiSaA@mail.gmail.com>
 <20200629120709.52w2zi36mtzyliv2@localhost>
 <fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.optiver.com>
 <20200711162103.hiygat3k6wu2d25p@localhost>
 <ef9954d83a9e42fabfac235bdd87d05a@opammb0561.comp.optiver.com>
 <20200714161852.s27ikts2dppvbm3h@localhost>
 <536657c24bcf49eb8215e84969f2b345@opammb0561.comp.optiver.com>
 <20200723095351.dbboo3fymuzwqmic@localhost>
In-Reply-To: <20200723095351.dbboo3fymuzwqmic@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

>=20
> One UniqueKey can have multiple corresponding expressions, which gives us
> also possibility of having one unique key with (t1.a, t2.a) and it looks =
now
> similar to EquivalenceClass.
>=20

I believe the current definition of a unique key with two expressions (t1.a=
, t2.a) means that it's unique on the tuple (t1.a, t2.a) - this gives weake=
r guarantees than uniqueness on (t1.a) and uniqueness on (t2.a).

>=20
> The idea behind this query sounds questionable to me, more transparent
> would be to do this without distinct, skipping will actually do exactly t=
he same
> stuff just under another name. But if allowing skipping on constants do n=
ot
> bring significant changes in the code probably it's fine.
>=20

Yeah indeed, I didn't say it's a query that people should generally write. =
:-) It's better to write as a regular SELECT with LIMIT 1 of course. Howeve=
r, it's more to be consistent and predictable to the user: if a SELECT DIST=
INCT ON (a) * FROM t1 runs fast, then it doesn't make sense to the user if =
a SELECT DISTINCT ON (a) * FROM t1 WHERE a=3D2 runs slow. And to support it=
 also makes the implementation more consistent with little code changes.

> >
> > Yeah, there's definitely some double work there, but the actual impact =
may
> be limited - it doesn't actually allocate a new path key, but it looks it=
 up in
> root->canon_pathkeys and returns that path key.
> > I wrote it like this, because I couldn't find a way to identify from a =
certain
> PathKey the actual location in the index of that column. The constructed =
path
> keys list filters out all redundant path keys. An index on (a,a,b,a,b) be=
comes
> path keys (a,b). Now if we skip on (a,b) we actually need to use prefix=
=3D3. But
> how to get from PathKey=3Db to that number 3, I didn't find a solid way e=
xcept
> doing this. Maybe there is though?
>=20
> I don't think there is a direct way, but why not modify build_index_paths=
 to
> also provide this information, or compare index_pathkeys expressions with
> indextlist without actually create those pathkeys again?
>=20

I agree there could be other ways - I don't currently have a strong prefere=
nce for either. I can have a look at this later.

> And couple of words about this thread [1]. It looks to me like a strange =
way
> of interacting with the community. Are you going to duplicate there
> everything, or what are your plans? At the very least you could try to in=
clude
> everyone involved in the recipients list, not exclude some of the authors=
.
>=20

When I wrote the first mail in the thread, I went to this thread [1] and in=
cluded everyone from there, but I see now that I only included the to: and =
cc: people and forgot the original thread author, you. I'm sorry about that=
 - I should've looked better to make sure I had everyone.
In any case, my plan is to keep the patch at least applicable to master, as=
 I believe it can be helpful for discussions about both patches.

[1] https://www.postgresql.org/message-id/20200609102247.jdlatmfyeecg52fi%4=
0localhost