Re: Index Skip Scan

Hello Jesper,

I was reviewing index-skip patch example and have a comment on it. Example query “select distinct b from t1” is equivalent to “select b from t1 group by b”. 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?

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(dot)korotkov(at)postgrespro(dot)ru> wrote:
>
> Hi!
>
> On Mon, Jun 18, 2018 at 6:26 PM Jesper Pedersen
> <jesper(dot)pedersen(at)redhat(dot)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
>
>