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
