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

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