pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Big field, limiting and ordering
2+ messages / 2 participants
[nested] [flat]

* Big field, limiting and ordering
@ 2010-07-19 15:09  Ivan Voras <ivoras@freebsd.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Ivan Voras @ 2010-07-19 15:09 UTC (permalink / raw)
  To: pgsql-performance

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?

(I cannot limit big_field with substring() to reduce memory usage
because it messes up complex_function()).




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Big field, limiting and ordering
@ 2010-07-19 15:35  Vitalii Tymchyshyn <tivv00@gmail.com>
  parent: Ivan Voras <ivoras@freebsd.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Vitalii Tymchyshyn @ 2010-07-19 15:35 UTC (permalink / raw)
  To: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2010-07-19 15:35 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-19 15:09 Big field, limiting and ordering Ivan Voras <ivoras@freebsd.org>
2010-07-19 15:35 ` Vitalii Tymchyshyn <tivv00@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox