Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: Index Skip Scan
From: Bhushan Uparkar <bhushan.uparkar@gmail.com>
In-Reply-To: 
 <CAPpHfducUiDw7hVT=Vui8rxTmawCkVc0H--v=jdbztMUGhzuRg@mail.gmail.com>
Date: Wed, 15 Aug 2018 22:44:58 -0700
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>,
 Andrew Dunstan <andrew.dunstan@2ndquadrant.com>,
 Alexander Korotkov <a.korotkov@postgrespro.ru>
Content-Transfer-Encoding: quoted-printable
Message-Id: <89F84E17-D684-4717-B66F-0F05CB00168E@gmail.com>
References: <707b6f68-16fa-7aa7-96e5-eeb4865e6a30@redhat.com>
 <CAPpHfducUiDw7hVT=Vui8rxTmawCkVc0H--v=jdbztMUGhzuRg@mail.gmail.com>
To: Jesper Pedersen <jesper.pedersen@redhat.com>
Precedence: bulk

Hello Jesper,

I was reviewing index-skip patch example and have a comment on it. =
Example query =E2=80=9Cselect distinct b from t1=E2=80=9D is equivalent =
to =E2=80=9Cselect b from t1 group by b=E2=80=9D. When I tried the 2nd =
form of query it came up with different plan, is it possible that index =
skip scan can address it as well?=20


postgres=3D# explain verbose select  b from t1 group by b;
                                             QUERY PLAN
=
--------------------------------------------------------------------------=
--------------------------
 Group  (cost=3D97331.29..97332.01 rows=3D3 width=3D4)
   Output: b
   Group Key: t1.b
   ->  Gather Merge  (cost=3D97331.29..97331.99 rows=3D6 width=3D4)
         Output: b
         Workers Planned: 2
         ->  Sort  (cost=3D96331.27..96331.27 rows=3D3 width=3D4)
               Output: b
               Sort Key: t1.b
               ->  Partial HashAggregate  (cost=3D96331.21..96331.24 =
rows=3D3 width=3D4)
                     Output: b
                     Group Key: t1.b
                     ->  Parallel Seq Scan on public.t1  =
(cost=3D0.00..85914.57 rows=3D4166657 width=3D4)
                           Output: a, b
(14 rows)

Time: 1.167 ms

=E2=80=94 And here is the original example
postgres=3D# explain verbose SELECT DISTINCT b FROM t1;
                                  QUERY PLAN
=
--------------------------------------------------------------------------=
-----
 Index Skip Scan using idx_t1_b on public.t1  (cost=3D0.43..1.30 rows=3D3 =
width=3D4)
   Output: b
(2 rows)

Time: 0.987 ms


> On Jun 18, 2018, at 10:31 AM, Alexander Korotkov =
<a.korotkov@postgrespro.ru> wrote:
>=20
> Hi!
>=20
> On Mon, Jun 18, 2018 at 6:26 PM Jesper Pedersen
> <jesper.pedersen@redhat.com> wrote:
>> I would like to start a discussion on Index Skip Scan referred to as
>> Loose Index Scan in the wiki [1].
>=20
> Great, I glad to see you working in this!
>=20
>> However, as Robert Haas noted in the thread there are issues with the
>> patch as is, especially in relationship to the amcanbackward =
functionality.
>>=20
>> A couple of questions to begin with.
>>=20
>> Should the patch continue to "piggy-back" on T_IndexOnlyScan, or =
should
>> a new node (T_IndexSkipScan) be created ? If latter, then there =
likely
>> will be functionality that needs to be refactored into shared code
>> between the nodes.
>=20
> Is skip scan only possible for index-only scan?  I guess, that no.  We
> could also make plain index scan to behave like a skip scan.  And it
> should be useful for accelerating DISTINCT ON clause.  Thus, we might
> have 4 kinds of index scan: IndexScan, IndexOnlyScan, IndexSkipScan,
> IndexOnlySkipScan.  So, I don't think I like index scan nodes to
> multiply this way, and it would be probably better to keep number
> nodes smaller.  But I don't insist on that, and I would like to hear
> other opinions too.
>=20
>> Which is the best way to deal with the amcanbackward functionality ? =
Do
>> people see another alternative to Robert's idea of adding a flag to =
the
>> scan.
>=20
> Supporting amcanbackward seems to be basically possible, but rather
> complicated and not very efficient.  So, it seems to not worth
> implementing, at least in the initial version.  And then the question
> should how index access method report that it supports both skip scan
> and backward scan, but not both together?  What about turning
> amcanbackward into a function which takes (bool skipscan) argument?
> Therefore, this function will return whether backward scan is
> supported depending of whether skip scan is used.
>=20
>> I wasn't planning on making this a patch submission for the July
>> CommitFest due to the reasons mentioned above, but can do so if =
people
>> thinks it is best. The patch is based on master/4c8156.
>=20
> Please, register it on commitfest.  If even there wouldn't be enough
> of time for this patch on July commitfest, it's no problem to move it.
>=20
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>=20
>=20