On Oct 12, 2013, at 4:21 PM, Tom Lane <tgl@sss.pgh.pa.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
fractionthat I think it should be faster to use the index first before fetching heap rows. (It most certainly would be
fasterif the rows to be evaluated for the colb match were randomly dispersed, but because they tend to be naturally
clusteredon tz anyway, and the rows are pretty small, there's some chance an index scan might not save enough heap row
I/Oto 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
knowif I was missing something wrt indexes or whatever. It took me a long time to even find varchar_pattern_ops. (This
isone 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
particularquery 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@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice