Re: Highly Efficient Custom Sorting

On 7/2/10 6:59 AM, Eliot Gable wrote:
> 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.

Try coding it in perl on the server. It is MUCH easier to code, and you don't have to link anything or learn the esoteric details of the Postgres/C API.

Perl itself is written in C, and some of it's operations are extremely fast. Depending on the size and complexity of your data structures, Perl code may be just as fast as code you could write in C.

Even if it turns out to be slower than you like, it will give you a way to package up your sort functionality into a function call, so if you later find you need to replace the Perl function with a C function, the rest of your application won't change.

Craig