Re: [7.0.3] optimizing a LIKE query ...

The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> I'm figuring that if I can somehow get the query (using subselects,
> maybe?), to have the LIKE part of the query work only on the 6k records
> returned by the "=" part of it, the overall results should be faster ...

In 7.0.* I think the only way to do that is to select into a temp table
and then apply the LIKE while selecting from the temp table.

In 7.1 you could possibly force the order by using a subselect (although
offhand I think the planner might be smart enough to see through that,
and do what it thinks is right anyway). The real problem is the planner
thinks that LIKE '%http://www.postgresql.org/%%' is really selective; it
has no idea that most of your table mentions pgsql.org URLs :-(. We
need better statistics to fix this properly. (On my list for 7.2.)

regards, tom lane