Re: Index Scans Oddness

Orion <o2(at)trustcommerce(dot)com> writes:
> select * from foo where text_field like 'foo%'
> will do an index scan with my data in 7.0.3 but not 7.1.3-1PGDG.
> Makes me think there's a bug.

Yes: in 7.0. You're probably running in a non-C locale, wherein
optimizing LIKE with an indexscan is unsafe.

> select * from foo where order_date = current_date
> and
> select * from foo
> where order_date < to_date(xxx) and order_date > to_date(yyy)

> Both do sequence scans where current date is indexed

The problem is probably lack of cachability of current_date and to_date().
You can fix this with a wrapper function that's marked "iscachable".
Look in the mailing list archives for prior discussions.

regards, tom lane