From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp21.mail.ru (smtp21.mail.ru [94.100.179.250]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 7532B469719 for ; Tue, 6 Oct 2020 14:31:24 +0300 (MSK) References: <38490e796033c2eb51ae9fec9e330f6bc7ec51a7.1601967922.git.imeevma@gmail.com> <20201006104440.GB12999@tarantool.org> From: Mergen Imeev Message-ID: Date: Tue, 6 Oct 2020 14:31:22 +0300 MIME-Version: 1.0 In-Reply-To: <20201006104440.GB12999@tarantool.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US Subject: Re: [Tarantool-patches] [PATCH v2 1/1] sql: enable autoindex optimization List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Nikita Pettik Cc: Mergen Imeev , tarantool-patches@dev.tarantool.org On 06.10.2020 13:44, Nikita Pettik wrote: > On 06 Oct 10:08, imeevma@tarantool.org wrote: >> This patch enables "autoindex" optimization. This optimization uses an >> ephemeral space that contains all the fields used in the query. This >> ephemeral space is called "autoindex". Autoindex may have fewer fields >> than the original space, and the order of these fields in autoindex may >> be different from their order in the original space. In addition to the >> mentioned fields, autoindex contains a rowid. >> >> The primary key of autoindex is a covering index, i.e. it contains all >> of its fields. >> >> Currently this optimization mainly used in cases when values SELECTED >> from more than one space and WHERE specified. >> >> At the moment, this optimization does not take into account the number >> of tuples in spaces. This should be fixed. >> >> Closes #4933 >> >> Co-authored-by: Mergen Imeev >> Co-authored-by: Timur Safin >> --- >> https://github.com/tarantool/tarantool/issues/4933 >> https://github.com/tarantool/tarantool/tree/imeevma/gh-4933-autoindex >> >> @ChangeLog >> - "Auto-index" optimization is now enabled (gh-4933). >> diff --git a/test/sql/misc.result b/test/sql/misc.result >> index 6af11bfba..d86d55081 100644 >> --- a/test/sql/misc.result >> +++ b/test/sql/misc.result >> @@ -204,3 +204,32 @@ box.execute('SELECT field66, field68, field70 FROM test') >> box.space.TEST:drop() >> --- >> ... >> +-- gh-4933: Make sure that autoindex optimization is used. >> +box.execute('CREATE TABLE t1(i int primary key, a int);') >> +--- >> +- row_count: 1 >> +... >> +box.execute('CREATE TABLE t2(i int primary key, b int);') >> +--- >> +- row_count: 1 >> +... >> +-- >> +-- There is no need to insert values in the tables since planner assumes a >> +-- default number of tuples for each space, regardless of how many tuples there >> +-- actually are in those spaces. The default value is 1048576 (== 2^20). >> +-- >> +box.execute('EXPLAIN QUERY PLAN SELECT a, b FROM t1, t2 WHERE a = b;') >> +--- >> +- metadata: >> + - name: selectid >> + type: integer >> + - name: order >> + type: integer >> + - name: from >> + type: integer >> + - name: detail >> + type: text >> + rows: >> + - [0, 0, 0, 'SCAN TABLE T1 (~1048576 rows)'] >> + - [0, 1, 1, 'SEARCH TABLE T2 USING AUTOMATIC COVERING INDEX (B=?) (~20 rows)'] >> +... > I haven't reviewed the whole patch yet, but do you really think autoindex > qp optimization deserves this single insignificant test..? So, there won't be review before I create new tests? >