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> |
Subject: | Re: Index Onlys Scan for expressions |
Date: | 2016-09-05 13:38:54 |
Message-ID: | 8045bf7a-bd7a-b4c5-8009-d6ca15848e84@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
--
Ildar Musin
i(dot)musin(at)postgrespro(dot)ru
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2016-09-05 13:58:18 | Re: INSERT .. SET syntax |
Previous Message | Alvaro Herrera | 2016-09-05 13:34:40 | Re: Fun fact about autovacuum and orphan temp tables |