Skip site navigation (1) Skip section navigation (2)

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

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner should use index on a LIKE 'foo%' query
Date: 2008-06-30 10:19:47
Message-ID: Pine.LNX.4.64.0806301114130.4085@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Dimitri FontaineDate: 2008-06-30 10:20:40
Subject: Re: Planner should use index on a LIKE 'foo%' query
Previous:From: Rusty ConoverDate: 2008-06-30 07:44:45
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group