Re: Highly Efficient Custom Sorting

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Eliot Gable <egable+pgsql-performance(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Highly Efficient Custom Sorting
Date: 2010-07-02 13:59:36
Message-ID: AANLkTilsy1QlSURLUIg3GC5NzRsTEJbEuqUYrZKMu32n@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Jul 1, 2010 at 8:46 PM, Eliot Gable
<egable+pgsql-performance(at)gmail(dot)com> wrote:
> I have a long stored procedure (over 3,000 lines). Originally, it would take
> about 44ms to run the whole query. After lots and lots of tweaking, Postgres
> now runs the entire thing and gathers my results in just 15.2ms, which is
> very impressive given the hardware this is running on. Now, I used to return
> the results unsorted to my C++ backend and then sort them there using my
> custom sort order which provides prioritized, weighted random ordering with
> 4 different priority fields and 3 different weighting fields within 3 of
> those 4 priority fields. Needless to say, the sorting is quite complex. I
> wanted to cut down on the amount of data being delivered to my C++ backend,
> so I am using the stored procedure to insert a summary of my results
> directly into the database, which is far more efficient than dumping it all
> to the C++ backend (including stuff that isn't really needed there) and then
> dumping it all back to Postgres via INSERTS later in the execution path. The
> problem is that I want the results sorted in this custom order before they
> are stored in the database. (By sorted, I mean I want to include a field
> that lists a numerical order for the set of results.) Thus, I used to dump
> everything to the C++ program, perform the sorting, then INSERT back to
> Postgres. This was obviously not all that efficient. Now, the sorting in C++
> took <1ms to accomplish. When I re-wrote the sorting in pl/pgsql using a
> couple of additional stored procedures, I discovered it is taking 15.2ms to
> perform the sort of the records within Postgres. This almost cancels out all
> of the prior optimizations I previously performed:
> T:20100702001841+0903010 TID:0x43945940 INFO:NOTICE:  Sorting group ...
> <snip>
> ...
> </snip>

what are you sorting and how are you sorting it?

merlin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Eliot Gable 2010-07-02 13:59:44 Re: Highly Efficient Custom Sorting
Previous Message damien hostin 2010-07-02 08:48:59 Slow query with planner row strange estimation