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

On Mon, 30 Jun 2008, Moritz Onken wrote:
>> SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item WHERE
>>  item.shorturl = result.url) AS a
>
> I tried the this approach but it's slower than WHERE IN in my case.

However there's a lot more scope for improving a query along these lines,
like adding indexes, or CLUSTERing on an index. It depends what other
queries you are wanting to run.

I don't know how much update/insert activity there will be on your
database. However, if you were to add an index on the URL on both tables,
then CLUSTER both tables on those indexes, and ANALYSE, then this query
should run as a merge join, and be pretty quick.

However, this is always going to be a long-running query, because it
accesses at least one whole table scan of a large table.

Matthew

--
"Finger to spiritual emptiness underlying everything."
        -- How a foreign C manual referred to a "pointer to void."