Everlasting SQL query

[Joost Kraaijeveld]

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).

Explain gives me this (why 7 rows?):

QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=722506879.16..722506879.22 rows=25 width=44)
-> Sort (cost=722506879.16..726378050.59 rows=1548468574 width=44)
Sort Key: klt_alg.klantnummer, orders.ordernummer
-> Nested Loop (cost=9408.93..36288661.59 rows=1548468574 width=44)
-> Seq Scan on klt_alg (cost=0.00..1927.18 rows=17518 width=40)
-> Materialize (cost=9408.93..10595.86 rows=88393 width=4)
-> Seq Scan on orders (cost=0.00..9105.93 rows=88393 width=4)
(7 rows)

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?

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: [phone]
fax: [phone]
e-mail: J.***********@Askesis.nl
web: www.askesis.nl

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Csaba Nagy]

Joost,

The spelling mistake from below will cause you an unwanted join with the
"customers" table if you have one, which is unconstrained by any where
clause, so it will take a carthesian product of the rest and the
customers table.
If this spelling mistake is just in the mail, please disregard.

Cheers,
Csaba.

select customer.id, customer.name, orders.id
from customers, orders ^^^^^^^^^ order by customer.id, orders.id
limit 25

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michal Taborsky]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Glaesemann]

On Jul 28, 2004, at 7:08 PM, Joost Kraaijeveld wrote:

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).

You have no join condition, so it's doing a full cartesian join (17518
x 88393 = 1,548,468,574 rows before the limit). Try this:

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

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Chris]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly