Re: Highly Efficient Custom Sorting

Yes, I have two pl/pgsql functions. They take a prepared set of data (just
the row id of the original results, plus the particular priority and weight
fields) and they return the same set of data with an extra field called
"order" which contains a numerical order to apply when sorting the rows. One
function uses the priority information to break everything into priority
groups, then calls the other function for each priority group. Each time it
gets results back from the inner function, it returns that set of results.
When it has looped through all priority groups, then it returns the full
built-up set of results back to the calling function.

The pl/pgsql functions implementing the sort are as optimized as they are
likely to get. I don't want to waste my time trying to further optimize
pl/pgsql functions that are never going to be as fast and efficient as I
need. I would rather spend that time re-writing it in C and get sorting back
to <1ms.

I guess the real question is, is a generic C sorting function my only real
alternative? Is there anything else that would allow me to sort things
faster than pl/pgsql functions? For example, if I used pl/perl, would I be
able to expect considerably better performance for sorting than using
pl/pgsql? What about other supported languages? If I can get close to 1ms
sorting performance without resorting to C, it would save me much time and
frustration.

On Fri, Jul 2, 2010 at 12:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> > On 02/07/10 08:46, Eliot Gable wrote:
> >> So, the bottom line is, I need a faster way to do this sorting.
>
> > You haven't showed us how you're doing it at the moment, so it's awfully
> > hard to comment usefully on possible approaches.
>
> I'm guessing from tea leaves, but the impression I got from Eliot's
> description is that he's using plpgsql functions as sort comparators.
> It's not surprising that that sucks performance-wise compared to having
> the equivalent logic in C/C++ functions used as comparators on the
> client side. plpgsql is no speed demon. Best fix might be to code the
> comparators as C functions on the server side.
>
> regards, tom lane
>

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero