On Sat, 27 Mar 2004 08:10:53 +0000 (UTC), Alois Maier wrote:

> The following query is very slow and ultimately fails due to lack of
> disk space. The temp file grows to more than 2GB.
> The relevant metadata is below. TABLE1 has ca. 1000 records, TABLE2 ca.
> 150000.
> I am a bit surprised about this because postgres has no problems with
> this query and provides the result in seconds.
>
>
>
> Query:
> SELECT "TABLE1"."FIELD1", "TABLE1"."N2", "TABLE2"."FIELD2",
> "TABLE2"."FIELD3", SUM( "TABLE2"."FIELD4" ), COUNT( * ) FROM "TABLE1"
> "TABLE1", "TABLE2" "TABLE2" GROUP BY "TABLE1"."FIELD1", "TABLE1"."N2",
> "TABLE2"."FIELD2", "TABLE2"."FIELD3"
>
> Statement failed, SQLCODE = -902
>
> I/O error for file "C:\WINDOWS\TEMP\fb_sort_admp3k"
> -Error while trying to write to file
> -Der Drucker hat kein Papier mehr.
>
> -sort error

Your intermediate result set is 150.000.000 records.
If the total record size is approx. 400B you'll have around 60GB.

Do you really mean to join everything with everything (no WHERE clause)?
(And, don't you get fed up with all the quoting?)
(And, an explicit join looks better ...)

> -Der Drucker hat kein Papier mehr.

Interesting... How did the printer get involved in this?


Regards,
Aage J.