Re: Index Skip Scan - Mailing list pgsql-hackers

Hello Jesper,

I was reviewing index-skip patch example and have a comment on it. Example query “select distinct b from t1” is
equivalentto “select b from t1 group by b”. When I tried the 2nd form of query it came up with different plan, is it
possiblethat index skip scan can address it as well?  


postgres=# explain verbose select  b from t1 group by b;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Group  (cost=97331.29..97332.01 rows=3 width=4)
   Output: b
   Group Key: t1.b
   ->  Gather Merge  (cost=97331.29..97331.99 rows=6 width=4)
         Output: b
         Workers Planned: 2
         ->  Sort  (cost=96331.27..96331.27 rows=3 width=4)
               Output: b
               Sort Key: t1.b
               ->  Partial HashAggregate  (cost=96331.21..96331.24 rows=3 width=4)
                     Output: b
                     Group Key: t1.b
                     ->  Parallel Seq Scan on public.t1  (cost=0.00..85914.57 rows=4166657 width=4)
                           Output: a, b
(14 rows)

Time: 1.167 ms

— And here is the original example
postgres=# explain verbose SELECT DISTINCT b FROM t1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Skip Scan using idx_t1_b on public.t1  (cost=0.43..1.30 rows=3 width=4)
   Output: b
(2 rows)

Time: 0.987 ms



> On Jun 18, 2018, at 10:31 AM, Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:
>
> Hi!
>
> 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].
>
> Great, I glad to see you working in this!
>
>> However, as Robert Haas noted in the thread there are issues with the
>> patch as is, especially in relationship to the amcanbackward functionality.
>>
>> A couple of questions to begin with.
>>
>> 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.
>
> 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.
>
>> 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.
>
> 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.
>
>> 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.
>
> 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.
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>