Re: Everlasting SQL query

On Wed, 2004-07-28 at 12:08, Joost Kraaijeveld wrote:
> Hi everyone,
>
> 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).
> [...]

> If I only order by customer.id or by orders.is the query return within a second.
>
> Can anyone give me a reason why this all happens?

This is an inner join without a where clause.
It gives the crsoo product of 17518*88393 = 1548468574 results.

If you order by just one, PG can manage to give you the first 25 results
out of the 1.5 billion (!). If you order by both there's no other
way than to (try) computing everything - which PG does.

You most likely don't want this. Add a where clause:
where order.customer_id = customer.id
or something like that (I'm just guessing your scheme).

Bye, Chris.