JB <jimbag(at)kw(dot)igs(dot)net> writes:
> SELECT * FROM info WHERE street_name LIKE 'MAIN%';
> ...this would take about 20 secs to complete. Because the wildness only
> happens at the end of the search string, I changed the query to...
> SELECT * FROM info WHERE substring( street_name from 1 to 4 ) = 'MAIN';
> ...this takes under 2 secs.
This makes no sense to me at all. The latter query should be far
slower, because AFAIK there is no optimization for it, whereas there is
an optimization for "foo LIKE 'bar%'".
What version are you running, and what plan does EXPLAIN show for
each of these queries?
regards, tom lane