On Tue, Jun 9, 2020 at 6:20 PM Dmitry Dolgov <9erthali...@gmail.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
