Re: Everlasting SQL query
От | Chris |
---|---|
Тема | Re: Everlasting SQL query |
Дата | |
Msg-id | 1091012337.1958.50.camel@dell обсуждение исходный текст |
Ответ на | Everlasting SQL query ("Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl>) |
Список | pgsql-general |
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 thatdid 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.
В списке pgsql-general по дате отправления: