Re: like & optimization

On Oct 12, 2013, at 4:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The reason you're losing on this is that the "select *" command eliminates
> the possibility of an index-only scan (I'm assuming that that selects some
> columns that aren't in the index). Given that a plain indexscan will
> always involve fetching each heap row that satisfies the indexable
> condition (the one on tz), the planner figures it might as well use the
> physically-smaller index.

OK, that logic makes sense. In the particular case I'm looking at, the comparison to colb will match such a tiny fraction that I think it should be faster to use the index first before fetching heap rows. (It most certainly would be faster if the rows to be evaluated for the colb match were randomly dispersed, but because they tend to be naturally clustered on tz anyway, and the rows are pretty small, there's some chance an index scan might not save enough heap row I/O to offset it's own I/O.)

> It's true that in principle we could use the index-only-scan index AM
> machinery to retrieve colb from the index, and then check the LIKE
> predicate on that value before we go to the heap to get the other values;
> but the code isn't factored that way at the moment. I'm not entirely sure
> that such cases arise often enough to be worth making it happen. I think
> there was discussion of this point back when the index-only-scan patch was
> being written, and we decided it didn't seem worth pursuing at the time.

It's not a common-enough case for me to worry about. This is a very rare query in this application--I just wanted to know if I was missing something wrt indexes or whatever. It took me a long time to even find varchar_pattern_ops. (This is one particular question where the top results from google searches are dominated by incorrect assertions. Yes, Virginia, it *IS* possible to use an index in evaluating a like '%whatever' condition--whether or not it helps in a particular query is an open question, but it most certainly is possible.)

Besides, you've given me the hint, if I really care about this I can try a covering index ;-)

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice