Re: Index Onlys Scan for expressions

On Tue, Aug 16, 2016 at 1:03 AM, Ildar Musin <i(dot)musin(at)postgrespro(dot)ru> wrote:
> Hi, hackers!
>
> There is a known issue that index only scan (IOS) can only work with simple
> index keys based on single attributes and doesn't work with index
> expressions. In this patch I propose a solution that adds support of IOS for
> index expressions. Here's an example:
>
> create table abc(a int, b int, c int);
> create index on abc ((a * 1000 + b), c);
>
> with t1 as (select generate_series(1, 1000) as x),
> t2 as (select generate_series(0, 999) as x)
> insert into abc(a, b, c)
> select t1.x, t2.x, t2.x from t1, t2;
> vacuum analyze;
>
> Explain results with the patch:
>
> explain (analyze, buffers) select a * 1000 + b + c from abc where a * 1000 +
> b = 1001;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
> Index Only Scan using abc_expr_c_idx on abc (cost=0.42..4.45 rows=1
> width=4) (actual time=0.032..0.033 rows=1 loops=1)
> Index Cond: ((((a * 1000) + b)) = 1001)
> Heap Fetches: 0
> Buffers: shared hit=4
> Planning time: 0.184 ms
> Execution time: 0.077 ms
> (6 rows)
>
> Before the patch it was:
>
> explain (analyze, buffers) select a * 1000 + b + c from abc where a * 1000 +
> b = 1001;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------
> Index Scan using abc_expr_c_idx on abc (cost=0.42..8.45 rows=1 width=4)
> (actual time=0.039..0.041 rows=1 loops=1)
> Index Cond: (((a * 1000) + b) = 1001)
> Buffers: shared hit=4
> Planning time: 0.177 ms
> Execution time: 0.088 ms
> (5 rows)
>
> This solution has limitations though: the restriction or the target
> expression tree (or its part) must match exactly the index. E.g. this
> expression will pass the check:
>
> select a * 1000 + b + 100 from ...
>
> but this will fail:
>
> select 100 + a * 1000 + b from ...
>
> because the parser groups it as:
>
> (100 + a * 1000) + b
>
> In this form it won't match any index key. Another case is when we create
> index on (a+b) and then make query like 'select b+a ...' or '... where b+a =
> smth' -- it won't match. This applies to regular index scan too. Probably it
> worth to discuss the way to normalize index expressions and clauses and work
> out more convenient way to match them.

pg_operator.oprcommutative ?

> Anyway, I will be grateful if you take a look at the patch in attachment.
> Any comments and tips are welcome.
>
> Thanks!
>
> --
> Ildar Musin
> i(dot)musin(at)postgrespro(dot)ru
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>