Message-ID: <42065346.7000400@aeccom.com>
Date: Sun, 06 Feb 2005 18:26:30 +0100
From: Dirk.Lutzebaeck@t-online.de (Dirk Lutzebaeck)
Reply-To: lutzeb@aeccom.com
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>, pgsql-performance@postgresql.org,
	john@arbash-meinel.com
Subject: Re: query produces 1 GB temp file
References: <20050205182117.0125074@mail2.aeccom.com>
	<87y8e2kb8j.fsf@stark.xeocode.com>
	<42061B35.6000306@aeccom.com> <4859.1107710214@sss.pgh.pa.us>
In-Reply-To: <4859.1107710214@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom,

the orginal query has more output columns. I reduced it for readability. 
Specifically it returns a persitent object (flatobj column) which needs 
to be processed by the application as the returned result. The problem 
of the huge sort space usage seems to be that the flatobj is part of the 
row, so it used always copied in the sort algorithm I guess. When I drop 
the flatobj from the output columns the size of the temp space file 
drops dramatically. So I'll probably need to read flatobj after the 
sorting from the limited return result in a subselect.

Regards,

Dirk

Tom Lane wrote:

>Dirk.Lutzebaeck@t-online.de (Dirk Lutzebaeck) writes:
>  
>
>>SELECT DISTINCT ON (df.val_9, df.created, df.flatid) df.docindex, 
>>df.flatobj, bi.oid, bi.en
>>FROM bi,df
>>WHERE bi.rc=130170467
>>...
>>ORDER BY df.val_9 ASC, df.created DESC
>>LIMIT 1000 OFFSET 0
>>    
>>
>
>Just out of curiosity, what is this query supposed to *do* exactly?
>It looks to me like it will give indeterminate results.  Practical
>uses of DISTINCT ON generally specify more ORDER BY columns than
>there are DISTINCT ON columns, because the extra columns determine
>which rows have priority to survive the DISTINCT filter.  With the
>above query, you have absolutely no idea which row will be output
>for a given combination of val_9/created/flatid.
>
>			regards, tom lane
>
>  
>