From: | Ankit Kumar Pandey <itsankitkp(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pghackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Vik Fearing <vik(at)postgresfriends(dot)org> |
Subject: | Re: Todo: Teach planner to evaluate multiple windows in the optimal order |
Date: | 2023-01-29 12:05:15 |
Message-ID: | 47f5b0ff-f947-f4ea-3f22-2ad0317a5214@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> On 26/01/23 07:40, David Rowley wrote:
> I just put together a benchmark script to try to help paint a picture
> of under what circumstances reducing the number of sorts slows down
> performance. work_mem was 4GB for each, so the sorts never went to disk.
I tried same test suit albeit work_mem = 1 MB to check how sort behaves with frequent
disk IO.
For 1 million row test, patch version shows some promise but performance degrades in 10 million row test.
There is also an anomalies in middle range for 100/1000 random value.
I have also added results of benchmark with sorting fix enabled and table with 3 columns
and sorting done on > 2 columns. I don't see much improvement vs master here.
Again, these results are for work_mem = 1 MB.
> Sorting in smaller batches that better fit into CPU cache.
More reading yielded that we are looking for cache-oblivious
sorting algorithm.
One the papers[1] mentions that in quick sort, whenever we reach size which can fit in cache,
instead of partitioning it further, we can do insertion sort there itself.
> Memory-tuned quicksort uses insertion sort to sort small subsets while they are in
> the cache, instead of partitioning further. This increases the instruction count,
> but reduces the cache misses
If this looks step in right direction, I can give it a try and do more reading and experimentation.
[1] http://www.ittc.ku.edu/~jsv/Papers/WAC01.sorting_using_registers.pdf
Thanks,
Ankit
Attachment | Content-Type | Size |
---|---|---|
image/png | 44.9 KB | |
image/png | 43.4 KB | |
image/png | 38.9 KB | |
bench_windowsort_sort_opt.sh.txt | text/plain | 1.6 KB |
image/png | 40.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Dolgov | 2023-01-29 12:22:42 | Re: pg_stat_statements and "IN" conditions |
Previous Message | Nitin Jadhav | 2023-01-29 11:52:13 | Re: Fix GUC_NO_SHOW_ALL test scenario in 003_check_guc.pl |