Re: index problems (again)

Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
> So it seems that it should in fact be usable after all. So I'm still
> stumped as to why the (scdate,sc_id) index isn't used :(

Because the other way is estimated to be cheaper. The estimate is
wrong, because it's based on a statistical assumption that's wrong
(ie that sc_id and scdate are uncorrelated), but it's what we have
to work with at the moment.

As you found upthread, that index could be used in the way you want
if you had an equality condition on scdate. So the workaround
I'd suggest is to whack the query into that shape. Something
along the lines of (untested)

select min((select min(sc_id) from legs where scdate = gs))
from generate_series(20160219, 20160221) gs

This would only work well for relatively small ranges of scdate,
but if you had a large range then I think the original plan
would've been fine.

regards, tom lane