Re: Index Onlys Scan for expressions
| От | Vladimir Sitnikov |
|---|---|
| Тема | Re: Index Onlys Scan for expressions |
| Дата | |
| Msg-id | CAB=Je-GN2teyejnZQOKDGJuhYdbgMtMm03ZVanQpp7aGcEi7Jg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Index Onlys Scan for expressions (Ildar Musin <i.musin@postgrespro.ru>) |
| Ответы |
Re: Index Onlys Scan for expressions
|
| Список | pgsql-hackers |
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>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>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.
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
В списке pgsql-hackers по дате отправления: