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

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

From: Moritz Onken <onken(at)houseofdesign(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner should use index on a LIKE 'foo%' query
Date: 2008-06-30 16:11:15
Message-ID: 0E158087-4E44-4398-96E0-BFEEAAE099A7@houseofdesign.de (view raw or flat)
Thread:
Lists: pgsql-performance
Am 30.06.2008 um 16:59 schrieb Steinar H. Gunderson:

> On Mon, Jun 30, 2008 at 09:16:06AM +0200, Moritz Onken wrote:
>> the result table has 20.000.000 records and the item table 5.000.000.
>> The query
>>
>> select count(1) from result where url in (select shorturl from item
>> where shorturl = result.url);
>>
>> will take about 8 hours (still running, just guessing). Is this
>> reasonable on a system with 1 GB of RAM and a AMD Athlon 64 3200+
>> processor? (1 SATA HDD)
>
> I really don't see what your query tries to accomplish. Why would  
> you want
> "url IN (... where .. = url)"? Wouldn't you want a different qualifier
> somehow?

well, it counts the number of rows with urls which already exist in  
another
table.
How would you describe the query?
If the "(select shorturl from item where shorturl = result.url)"
clause is empty the row is not counted, that's what I want...

greetings,

moritz

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2008-06-30 16:21:19
Subject: Re: Planner should use index on a LIKE 'foo%' query
Previous:From: Peter SchullerDate: 2008-06-30 15:43:18
Subject: Re: VACUUM ANALYZE blocking both reads and writes to atable

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