From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Highly Efficient Custom Sorting
Date: Fri, 02 Jul 2010 09:36:23 -0700
Message-ID: <4C2E1587.70001@emolecules.com> (raw)
In-Reply-To: <AANLkTimZISmcXkPN059pz5bx-TXKVOnm6iuJ4y4qQedu@mail.gmail.com>
References: <AANLkTikrA8M3cncIHkFqsJ78TzCnQdriE0GjvK8XjB9E@mail.gmail.com>
<4C2D574E.5030308@postnewspapers.com.au>
<4767.1278043730@sss.pgh.pa.us>
<AANLkTimZISmcXkPN059pz5bx-TXKVOnm6iuJ4y4qQedu@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Highly Efficient Custom Sorting
In-Reply-To: <4C2E1587.70001@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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