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: (view raw, whole thread or download thread mbox)
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  
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...



In response to


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-2017 The PostgreSQL Global Development Group