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