Skip site navigation (1) Skip section navigation (2)

Big field, limiting and ordering

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Big field, limiting and ordering
Date: 2010-07-19 15:09:32
Message-ID: i21pqn$146$1@dough.gmane.org (view raw or flat)
Thread:
Lists: 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()).


Responses

pgsql-performance by date

Next:From: Vitalii TymchyshynDate: 2010-07-19 15:35:42
Subject: Re: Big field, limiting and ordering
Previous:From: Greg SmithDate: 2010-07-19 14:38:21
Subject: Re: IDE x SAS RAID 0 on HP DL 380 G5 P400i controller performance problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group