The thing here is that you are effectively causing Postgres to run a
sub-select for each row of the "result" table, each time generating
either an empty list or a list with one or more identical URLs. This
is effectively forcing a nested loop. In a way, you have two
constraints where you
On Mon, 30 Jun 2008, Moritz Onken wrote:
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
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?
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);
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 inde
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,
li
Am 30.06.2008 um 12:19 schrieb Matthew Wakeling:
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
What on earth is wrong with writing it like this?
SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item
WHERE
item.shorturl =
Hi,
Le samedi 28 juin 2008, Moritz Onken a écrit :
> select count(*)
> from result
> where exists
> (select * from item where item.url LIKE result.url || '%' limit 1);
>
> which basically returns the number of items which exist in table
> result and match a URL in table item by its prefix.
On Mon, 30 Jun 2008, Moritz Onken wrote:
I created a new column in "item" where I store the shortened url which makes
"=" comparisons possible.
Good idea. Now create an index on that column.
select count(1) from result where url in (select shorturl from item where
shorturl = result.url);
Wh
Am 28.06.2008 um 21:19 schrieb Steinar H. Gunderson:
On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
SELECT distinct url from item where url like 'http://www.micro%'
limit
10;
Here, the planner knows the pattern beforehand, and can see that
it's a
simple prefix.
select *
Anfang der weitergeleiteten E-Mail:
Von: Moritz Onken <[EMAIL PROTECTED]>
Datum: 30. Juni 2008 09:16:06 MESZ
An: Steinar H. Gunderson <[EMAIL PROTECTED]>
Betreff: Re: [PERFORM] Planner should use index on a LIKE 'foo%' query
Am 28.06.2008 um 21:19 schrieb Steinar H. Gu
On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
> SELECT distinct url from item where url like 'http://www.micro%' limit
> 10;
Here, the planner knows the pattern beforehand, and can see that it's a
simple prefix.
> select *
> from result
> where exists
>(select * from item w
11 matches
Mail list logo
