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 12:52:08
Message-ID: Pine.LNX.4.64.0806301348220.4085@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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."

In response to

Responses

pgsql-performance by date

Next:From: Moritz OnkenDate: 2008-06-30 12:56:57
Subject: Re: Planner should use index on a LIKE 'foo%' query
Previous:From: Moritz OnkenDate: 2008-06-30 12:46:22
Subject: Re: Planner should use index on a LIKE 'foo%' query

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