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

Re: Everlasting SQL query

From: Michal Taborsky <michal(at)taborsky(dot)cz>
To: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Everlasting SQL query
Date: 2004-07-28 10:48:26
Message-ID: 4107847A.10609@taborsky.cz (view raw or flat)
Thread:
Lists: pgsql-general
Hi Joost.

Joost Kraaijeveld wrote:
> I have a customer table (17518 records) and an orders table (88393
> records). One of the columns of orders is customerid, containing the
> customerid (what else, but it is not a foreign key as this table is
> imported from a database that did not support foreign keys).
> 
> If I do this query (with pgadmin III):
> 
> select customer.id, customer.name, orders.id from customers, orders 
> order by customer.id, orders.id limit 25
> 
> The query runs forever (the longest I let it run is 500 seconds).

No wonder. You are retrieving 1548468574 rows. You are trying to perform
a JOIN, but without specifying which fields to join on. So the query
works with cartesian product of these two table (all possible
combinantions), which is 17518 * 88393 = 1548468574 rows.

You want:

select customer.id, customer.name, orders.id
from customers JOIN orders ON customers.id=orders.customerid
order by customer.id, orders.id
limit 25

or alternatively:

select customer.id, customer.name, orders.id
from customers, orders
where customers.id=orders.customerid
order by customer.id, orders.id
limit 25

I prefer the first notation, though.

-- 
Michal Taborsky
http://www.taborsky.cz


In response to

pgsql-general by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-07-28 10:57:57
Subject: Re: Trigger on Postgres for tables syncronization
Previous:From: Csaba NagyDate: 2004-07-28 10:48:00
Subject: Re: Everlasting SQL query

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