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.
I have an inferiority complex. But it's not a very good one.
In response to
pgsql-performance by date
|Next:||From: Dimitri Fontaine||Date: 2008-06-30 10:20:40|
|Subject: Re: Planner should use index on a LIKE 'foo%' query|
|Previous:||From: Rusty Conover||Date: 2008-06-30 07:44:45|
|Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?|