Re: Index Skip Scan (new UniqueKeys)

On Tue, Jun 9, 2020 at 6:20 PM Dmitry Dolgov <9erthalion6(at)gmail(dot)com> wrote:

> Hi,
>
> Here is a new version of index skip scan patch, based on v8 patch for
> UniqueKeys implementation from [1]. I want to start a new thread to
> simplify navigation, hopefully I didn't forget anyone who actively
> participated in the discussion.
>
> To simplify reviewing I've split it into several parts:
>
> * First two are taken from [1] just for the reference and to make cfbot
> happy.
>
> * Extend-UniqueKeys consists changes that needs to be done for
> UniqueKeys to be used in skip scan. Essentially this is a reduced
> version of previous implementation from Jesper & David, based on the
> new UniqueKeys infrastructure, as it does the very same thing.
>
> * Index-Skip-Scan contains not am specific code and the overall
> infrastructure, including configuration elements and so on.
>
> * Btree-implementation contains btree specific code to implement amskip,
> introduced in the previous patch.
>
> * The last one contains just documentation bits.
>
> Interesting enough with a new UniqueKey implementation skipping is
> applied in some tests unexpectedly. For those I've disabled
> indexskipscan to avoid confusion.
>
> [1]:
> https://www.postgresql.org/message-id/flat/CAKU4AWrwZMAL%3DuaFUDMf4WGOVkEL3ONbatqju9nSXTUucpp_pw%40mail.gmail.com
>

Thanks for the patch.

I just get the rough idea of patch, looks we have to narrow down the user
cases
where we can use this method. Consider the below example:

create table j1(i int, im5 int, im100 int, im1000 int);
insert into j1 select i, i%5, i%100, i%1000 from generate_series(1,
10000000)i;
create index on j1(im5, i);
insert into j1 values (1, 1, 0, 0);
analyze j1;

demo=# select distinct * from j1 where i < 2;
i | im5 | im100 | im1000
---+-----+-------+--------
1 | 1 | 1 | 1
(1 row)

demo=# set enable_indexskipscan to off;
SET
demo=# select distinct * from j1 where i < 2;
i | im5 | im100 | im1000
---+-----+-------+--------
1 | 1 | 0 | 0
1 | 1 | 1 | 1
(2 rows)

drop index j1_im5_i_idx;

create index on j1(im5, i, im100);
demo=# select distinct im5, i, im100 from j1 where i < 2;
im5 | i | im100
-----+---+-------
1 | 1 | 0
1 | 1 | 1
(2 rows)
demo=# set enable_indexskipscan to on;
SET
demo=# select distinct im5, i, im100 from j1 where i < 2;
im5 | i | im100
-----+---+-------
1 | 1 | 0
(1 row)

--
Best Regards
Andy Fan