Home > mailing lists

Re: Using quicksort for every external sort run - Mailing list pgsql-hackers

From	Mithun Cy
Subject	Re: Using quicksort for every external sort run
Date	January 29, 2016 14:41:38
Msg-id	CAD__OuhgUbZgszr-Ftf=bqgz-YwD60T5xMOikvXYiBQUCNpPqQ@mail.gmail.com Whole thread Raw
In response to	Re: Using quicksort for every external sort run (Peter Geoghegan <pg@heroku.com>)
Responses	Re: Using quicksort for every external sort run (Mithun Cy <mithun.cy@enterprisedb.com>) Re: Using quicksort for every external sort run (Peter Geoghegan <pg@heroku.com>)
List	pgsql-hackers

On Tue, Dec 29, 2015 at 4:33 AM, Peter Geoghegan <pg@heroku.com> wrote:

>Attached is a revision that significantly overhauls the memory patch,
>with several smaller changes.

I just ran some tests on above patch. Mainly to compare

how "longer sort keys" would behave with new(Qsort) and old Algo(RS) for sorting.

I have 8GB of ram and ssd storage.

Settings and Results.

----------------------------

Work_mem= DEFAULT (4mb).

key width = 520.

CASE 1. Data is pre-sorted as per sort key order.

CASE 2. Data is sorted in opposite order of sort key.

CASE 3. Data is randomly distributed.

If data is sorted as same as sort key order then current code performs better than proposed patch

as sort size increases.

It appears new algo do not seem have any major impact if rows are presorted in opposite order.

For randomly distributed order quick sort performs well when compared to current sort method (RS).

======================================================

Now Increase the work_mem to 64MB and for 14 GB of data to sort.

CASE 1: We can see Qsort is able to catchup with current sort method(RS).

CASE 2: No impact.

CASE 3: RS is able to catchup with Qsort.

CASE 1	RS	128822.735
	Qsort	90857.496

CSAE 2	RS	105631.775
	Qsort	105938.334

CASE 3	RS	152301.054
	Qsort	149649.347

I think for long keys both old (RS) and new (Qsort) sort method has its own characteristics

based on data distribution. I think work_mem is the key If properly set new method(Qsort) will

be able to fit most of the cases. If work_mem is not tuned right it, there are cases it can regress.

Thanks and Regards

Mithun C Y

From: Oleg Bartunov
Date: 29 January 2016, 14:15:47
Subject: Re: Fuzzy substring searching with the pg_trgm extension

From: Alexander Korotkov
Date: 29 January 2016, 15:00:19
Subject: Re: [PATCH] Refactoring of LWLock tranches