From mboxrd@z Thu Jan  1 00:00:00 1970
Return-Path: <korablev@tarantool.org>
Received: from smtp47.i.mail.ru (smtp47.i.mail.ru [94.100.177.107])
 (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 C38C5469719
 for <tarantool-patches@dev.tarantool.org>;
 Tue,  6 Oct 2020 13:44:41 +0300 (MSK)
Date: Tue, 6 Oct 2020 10:44:40 +0000
From: Nikita Pettik <korablev@tarantool.org>
Message-ID: <20201006104440.GB12999@tarantool.org>
References: <38490e796033c2eb51ae9fec9e330f6bc7ec51a7.1601967922.git.imeevma@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <38490e796033c2eb51ae9fec9e330f6bc7ec51a7.1601967922.git.imeevma@gmail.com>
Subject: Re: [Tarantool-patches] [PATCH v2 1/1] sql: enable autoindex
	optimization
List-Id: Tarantool development patches <tarantool-patches.dev.tarantool.org>
List-Unsubscribe: <https://lists.tarantool.org/mailman/options/tarantool-patches>, 
 <mailto:tarantool-patches-request@dev.tarantool.org?subject=unsubscribe>
List-Archive: <https://lists.tarantool.org/pipermail/tarantool-patches/>
List-Post: <mailto:tarantool-patches@dev.tarantool.org>
List-Help: <mailto:tarantool-patches-request@dev.tarantool.org?subject=help>
List-Subscribe: <https://lists.tarantool.org/mailman/listinfo/tarantool-patches>, 
 <mailto:tarantool-patches-request@dev.tarantool.org?subject=subscribe>
To: imeevma@tarantool.org
Cc: Mergen Imeev <imeevma@gmail.com>, tarantool-patches@dev.tarantool.org

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 <imeevma@gmail.com>
> Co-authored-by: Timur Safin <tsafin@tarantool.org>
> ---
> 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..?