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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
