Re: Everlasting SQL query - Mailing list pgsql-general

From Michal Taborsky
Subject Re: Everlasting SQL query
Date
Msg-id 4107847A.10609@taborsky.cz
Whole thread Raw
In response to Everlasting SQL query  ("Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl>)
List 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


pgsql-general by date:

Previous
From: Csaba Nagy
Date:
Subject: Re: Everlasting SQL query
Next
From: Pierre-Frédéric Caillaud
Date:
Subject: Re: Trigger on Postgres for tables syncronization