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