Re: Planner should use index on a LIKE 'foo%' query

On Mon, 30 Jun 2008, Moritz Onken wrote:
> I created a new column in "item" where I store the shortened url which makes
> "=" comparisons possible.

Good idea. Now create an index on that column.

> select count(1) from result where url in (select shorturl from item where
> shorturl = result.url);

What on earth is wrong with writing it like this?

SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item WHERE
    item.shorturl = result.url) AS a

That should do a fairly sensible join plan. There's no point in using
fancy IN or EXISTS syntax when a normal join will do.

Matthew

--
I have an inferiority complex. But it's not a very good one.