Re: Index Onlys Scan for expressions

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
Date: 2016-09-07 15:09:51
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi Vladimir,

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.
>> Since
>> 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
>> aware
>> that LIKE patterns with leading % cannot be optimized to a btree range
>> scan.
>> 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").
>> Vladimir
> 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?

Ildar Musin

Attachment Content-Type Size
indexonlyscan6.patch text/x-patch 9.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
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?