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: (view raw, whole thread or download thread mbox)
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,, from customers, orders 
> order by, 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:

from customers JOIN orders ON
order by,
limit 25

or alternatively:

from customers, orders
order by,
limit 25

I prefer the first notation, though.

Michal Taborsky

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-2017 The PostgreSQL Global Development Group