DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=pNOkUzitFMtzKg4RNnuJYD5ux0IlSPoTOmobuv7ZGCVEzLwGn+StjAAE7JDt7f9t2e
	Ju8Hxlc20EzRv9Qe5MXJE6orducyKc++VT8V6zw638Afk/a+1SoSDhlvD69py3V+sG4z
	6WHgJ1/giTa0Td+qCPly9L0j/BwKPhi+EO+Sg=
Message-ID: <4C4470CE.9060902@gmail.com>
Date: Mon, 19 Jul 2010 18:35:42 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.10) Gecko/20100528 Thunderbird/3.0.5
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Big field, limiting and ordering
References: <i21pqn$146$1@dough.gmane.org>
In-Reply-To: <i21pqn$146$1@dough.gmane.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

19.07.10 18:09, Ivan Voras написав(ла):
> Hello,
>
> I don't think this is generally solvable but maybe it is so here goes.
> The original situation was this:
>
> SELECT something, big_field, complex_function(big_field), rank FROM t1
> UNION ALL SELECT something, big_field, complex_function(big_field), rank
> from t2 ORDER BY rank LIMIT small_number;
>
> This query first fetches all big_field datums and does all
> complex_function() calculations on them, then orders then by rank, even
> though I actually need only small_number of records. There are two
> problems here: first, selecting for all big_field values requires a lot
> of memory, which is unacceptable, and then, running complex_function()
> on all of them takes too long.
>
> I did get rid of unnecessary complex_function() calculations by nesting
> queries like:
>
> SELECT something, big_field, complex_function(big_field), rank FROM
> (SELECT original_query_without_complex_function_but_with_big_field ORDER
> BY rank LIMIT small_number);
>
> but this still leaves gathering all the big_field datum from the
> original query. I cannot pull big_field out from this subquery because
> it comes from UNION of tables.
>
> Any suggestions?
>    
You can do the next:

SELECT something, big_field, complex_function(big_field), rank FROM
(SELECT * from
(
(SELECT something, big_field, complex_function(big_field), rank FROM t1 order by rank limit small_number)
UNION ALL (SELECT something, big_field, complex_function(big_field), rank
from t2 ORDER BY rank LIMIT small_number)
) a

  ORDER
BY rank LIMIT small_number) b;

So, you take small_number records from each table, then select small_number best records from resulting set, then do the calculation.

Best regards, Vitalii Tymchyshyn