|From:||Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>|
|To:||Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>|
|Cc:||Oleg Bartunov <obartunov(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>|
|Subject:||Re: Index Onlys Scan for expressions|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On 05.09.2016 16:38, Ildar Musin wrote:
> Hi Vladimir,
> On 03.09.2016 19:31, Vladimir Sitnikov wrote:
>> Ildar>The reason why this doesn't work is that '~~' operator (which is a
>> Ildar>synonym for 'like') isn't supported by operator class for btree.
>> Ildar>the only operators supported by btree are <, <=, =, >=, >, you
>> can use
>> Ildar>it with queries like:
>> Ildar>And in 3rd query 'OFFSET' statement prevents rewriter from
>> Ildar>transforming the query, so it is possible to use index only scan on
>> Ildar>subquery and then filter the result of subquery with '~~' operator.
>> I'm afraid I do not follow you.
>> Note: query 3 is 100% equivalent of query 2, however query 3 takes 55
>> times less reads.
>> It looks like either an optimizer bug, or some missing feature in the
>> "index only scan" logic.
>> Here's quote from "query 2" (note % are at both ends): ... where
>> type=42) as x where upper_vc like '%ABC%';
>> Note: I do NOT use "indexed scan" for the like operator. I'm very well
>> that LIKE patterns with leading % cannot be optimized to a btree range
>> What I want is "use the first indexed column as index scan, then use the
>> second column
>> for filtering".
>> As shown in "query 2" vs "query 3", PostgreSQL cannot come up with such
>> a plan on its own
>> for some reason.
>> This is not a theoretical issue, but it is something that I use a lot
>> with Oracle DB (it just creates a good plan for "query 2").
> Thanks, I get it now. The reason why it acts like this is that I used
> match_clause_to_index() function to determine if IOS can be used with
> the specified clauses. This function among other things checks if
> operator matches the index opfamily. Apparently this isn't correct. I
> wrote another prototype to test your case and it seems to work. But it's
> not ready for public yet, I'll publish it in 1-2 days.
Here is a new patch version. I modified check_index_only_clauses() so
that it doesn't use match_clause_to_indexcol() anymore. Instead it
handles different types of expressions including binary operator
expressions, scalar array expressions, row compare expressions (e.g.
(a,b)<(1,2)) and null tests and tries to match each part of expression
to index regardless an operator. I reproduced your example and was able
to get index only scan on all queries. Could you please try the patch
and tell if it works for you?
|Next Message||Greg Stark||2016-09-07 15:12:16||Re: Vacuum: allow usage of more than 1GB of work mem|
|Previous Message||Stas Kelvich||2016-09-07 14:44:09||Re: Suggestions for first contribution?|