On Thu, Jul 1, 2010 at 8:46 PM, Eliot Gable <egable+pgsql-performance@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
