Re: [HACKERS] Small improvement to compactify_tuples

On Wed, Nov 8, 2017 at 12:59 PM, Andres Freund wrote: > I complained about multiple related things, I'm not exactly sure what > exactly you're referring to here: > - The fact that HeapTupleHeaderData's are commonly iterated over in > reverse order is bad for performance. For

Re: [HACKERS] Small improvement to compactify_tuples

On 2017-11-08 12:02:40 -0500, Tom Lane wrote: > BTW, it strikes me that in considering the rebuild-the-page approach, > we should not have blinders on and just measure the speed of > PageRepairFragmentation. Rather, we should take a look at what happens > subsequently given a physically-ordered

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-08 20:02 GMT+03:00 Tom Lane : > > Claudio Freire writes: > > What's perhaps not clear is whether there are better ideas. Like > > rebuilding the page as Tom proposes, which doesn't seem like a bad > > idea. Bucket sort already is O(bytes), just

Re: [HACKERS] Small improvement to compactify_tuples

Claudio Freire writes: > What's perhaps not clear is whether there are better ideas. Like > rebuilding the page as Tom proposes, which doesn't seem like a bad > idea. Bucket sort already is O(bytes), just as memcopy, only it has a > lower constant factor (it's bytes/256 in

Re: [HACKERS] Small improvement to compactify_tuples

On Wed, Nov 8, 2017 at 12:33 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Nov 7, 2017 at 4:39 PM, Tom Lane wrote: >>> What I'm getting from the standard pgbench measurements, on both machines, >>> is that this patch might be

Re: [HACKERS] Small improvement to compactify_tuples

On Wed, Nov 8, 2017 at 8:19 AM, Robert Haas wrote: > I don't remember any more just how much faster qsort_tuple() and > qsort_ssup() are than plain qsort(), but it was significant enough to > convince me to commit 337b6f5ecf05b21b5e997986884d097d60e4e3d0... IIRC,

Re: [HACKERS] Small improvement to compactify_tuples

Robert Haas writes: > Just to throw a random idea out here, we currently have > gen_qsort_tuple.pl producing qsort_tuple() and qsort_ssup(). Maybe it > could be modified to also produce a specialized qsort_itemids(). That > might be noticeably faster that our

Re: [HACKERS] Small improvement to compactify_tuples

On Wed, Nov 8, 2017 at 10:33 AM, Tom Lane wrote: > I do not think there is any change here that can be proven to always be a > win. Certainly the original patch, which proposes to replace an O(n log n) > sort algorithm with an O(n^2) one, should not be thought to be that. >

Re: [HACKERS] Small improvement to compactify_tuples

Robert Haas writes: > On Tue, Nov 7, 2017 at 4:39 PM, Tom Lane wrote: >> What I'm getting from the standard pgbench measurements, on both machines, >> is that this patch might be a couple percent slower than HEAD, but that is >> barely above the noise

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Nov 7, 2017 at 4:39 PM, Tom Lane wrote: > What I'm getting from the standard pgbench measurements, on both machines, > is that this patch might be a couple percent slower than HEAD, but that is > barely above the noise floor so I'm not too sure about it. Hmm. It

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Nov 7, 2017 at 2:40 PM, Юрий Соколов wrote: >> The same is true of unique indexes vs. non-unique. > > offtopic: recently I'd a look at setting LP_DEAD in indexes. > I didn't found huge difference between unique and non-unique indices. > There is codepath that works

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Nov 7, 2017 at 2:36 PM, Tom Lane wrote: > Peter Geoghegan writes: >> My point is only that it's worth considering that this factor affects >> how representative your sympathetic case is. It's not clear how many >> PageIndexMultiDelete() calls are from

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-08 1:11 GMT+03:00 Peter Geoghegan : > > The same is true of unique indexes vs. non-unique. offtopic: recently I'd a look at setting LP_DEAD in indexes. I didn't found huge difference between unique and non-unique indices. There is codepath that works only for unique, but it

Re: [HACKERS] Small improvement to compactify_tuples

Peter Geoghegan writes: > My point is only that it's worth considering that this factor affects > how representative your sympathetic case is. It's not clear how many > PageIndexMultiDelete() calls are from opportunistic calls to > _bt_vacuum_one_page(), how important that subset of

Re: [HACKERS] Small improvement to compactify_tuples

) On Tue, Nov 7, 2017 at 1:39 PM, Tom Lane wrote: > So I think we should seriously consider the attached, but it'd be a > good idea to benchmark it on a wider variety of platforms and test > cases. > create unlogged table test3 ( > id integer PRIMARY KEY with

Re: [HACKERS] Small improvement to compactify_tuples

I've been getting less and less excited about this patch, because I still couldn't measure any above-the-noise performance improvement without artificial exaggerations, and some cases seemed actually slower. However, this morning I had an epiphany: why are we sorting at all? There is no

Re: [HACKERS] Small improvement to compactify_tuples

On 2017-11-07 12:12:02 -0300, Claudio Freire wrote: > If you need it. I'm not particularly fond of writing code before it's needed. +1 > Otherwise, if it's a rarely-encountered corner case, I'd recommend > simply calling the stdlib's qsort. FWIW, we always map qsort onto our own implementation:

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Nov 7, 2017 at 11:42 AM, Юрий Соколов wrote: > > > 2017-11-07 17:15 GMT+03:00 Claudio Freire : >> Aside from requiring all that include magic, if you place specialized >> sort functions in a reusable header, using it is as simple as >>

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-07 17:15 GMT+03:00 Claudio Freire : > > On Mon, Nov 6, 2017 at 9:08 PM, Юрий Соколов wrote: > > 2017-11-07 1:14 GMT+03:00 Claudio Freire : > >> > >> I haven't seen this trick used in postgres, nor do I know whether it

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 9:08 PM, Юрий Соколов wrote: > 2017-11-07 1:14 GMT+03:00 Claudio Freire : >> >> I haven't seen this trick used in postgres, nor do I know whether it >> would be well received, so this is more like throwing an idea to see >> if

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-07 1:14 GMT+03:00 Claudio Freire : > > On Mon, Nov 6, 2017 at 6:58 PM, Юрий Соколов wrote: > > > > 2017-11-06 17:55 GMT+03:00 Claudio Freire : > >> > >> On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 6:58 PM, Юрий Соколов wrote: > > 2017-11-06 17:55 GMT+03:00 Claudio Freire : >> >> On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов >> wrote: >> >> Maybe leave a fallback to qsort if some corner case

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-06 17:55 GMT+03:00 Claudio Freire : > > On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов wrote: > >> Maybe leave a fallback to qsort if some corner case produces big buckets? > > > > For 8kb pages, each bucket is per 32 bytes. So, for heap

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов wrote: >> Maybe leave a fallback to qsort if some corner case produces big buckets? > > For 8kb pages, each bucket is per 32 bytes. So, for heap pages it is at > most 1 heap-tuple per bucket, and for index pages it is at most 2

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-05 20:44 GMT+03:00 Claudio Freire : > > On Sat, Nov 4, 2017 at 8:07 PM, Юрий Соколов wrote: > > 2017-11-03 5:46 GMT+03:00 Tom Lane : > >> > >> Sokolov Yura writes: > >> > [

Re: [HACKERS] Small improvement to compactify_tuples

On Sat, Nov 4, 2017 at 8:07 PM, Юрий Соколов wrote: > 2017-11-03 5:46 GMT+03:00 Tom Lane : >> >> Sokolov Yura writes: >> > [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ] >> >> I went to check the shellsort

Re: [HACKERS] Small improvement to compactify_tuples

Юрий Соколов wrote: tps is also reflects changes: ~17ktps with qsort ~19ktps with bucket sort Also vacuum of benchmark's table is also improved: ~3s with qsort, ~2.4s with bucket sort One thing that you have to be careful with when it comes to our qsort with partially

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-03 5:46 GMT+03:00 Tom Lane : > > Sokolov Yura writes: > > [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ] > > I went to check the shellsort algorithm against Wikipedia's entry, > and found that this appears to be an incorrect

Re: [HACKERS] Small improvement to compactify_tuples

On Fri, Nov 3, 2017 at 4:30 PM, Tom Lane wrote: > Claudio Freire writes: >> On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane wrote: >>> BTW, the originally given test case shows no measurable improvement >>> on my box. > >> I did manage

Re: [HACKERS] Small improvement to compactify_tuples

I wrote: > Have not looked at the 0002 patch yet. I looked at that one, and it seems to be a potential win with no downside, so pushed. (I tweaked it slightly to avoid an unnecessary conflict with the test patch I posted earlier.) regards, tom lane -- Sent via

Re: [HACKERS] Small improvement to compactify_tuples

Claudio Freire writes: > On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane wrote: >> BTW, the originally given test case shows no measurable improvement >> on my box. > I did manage to reproduce the original test and got a consistent improvement. It occurred

Re: [HACKERS] Small improvement to compactify_tuples

On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane wrote: > Sokolov Yura writes: >> [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ] > > I started to review this patch. I spent a fair amount of time on > beautifying the code, because I found it

Re: [HACKERS] Small improvement to compactify_tuples

Sokolov Yura writes: > [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ] I started to review this patch. I spent a fair amount of time on beautifying the code, because I found it rather ugly and drastically undercommented. Once I had it to the point where

Re: [HACKERS] Small improvement to compactify_tuples

On Sat, Sep 23, 2017 at 5:56 AM, Sokolov Yura wrote: > Hello, Claudio. > > Thank you for review and confirm of improvement. > > > On 2017-09-23 01:12, Claudio Freire wrote: >> >> >> Patch 1 applies cleanly, builds, and make check runs fine. >> >> The code looks

Re: [HACKERS] Small improvement to compactify_tuples

Hello, Claudio. Thank you for review and confirm of improvement. On 2017-09-23 01:12, Claudio Freire wrote: On Tue, Sep 12, 2017 at 12:49 PM, Sokolov Yura wrote: On 2017-07-21 13:49, Sokolov Yura wrote: On 2017-05-17 17:46, Sokolov Yura wrote: Alvaro Herrera

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Sep 12, 2017 at 12:49 PM, Sokolov Yura wrote: > On 2017-07-21 13:49, Sokolov Yura wrote: >> >> On 2017-05-17 17:46, Sokolov Yura wrote: >>> >>> Alvaro Herrera писал 2017-05-15 20:13: As I understand, these patches are logically separate, so putting

Re: [HACKERS] Small improvement to compactify_tuples

On 2017-07-21 13:49, Sokolov Yura wrote: On 2017-05-17 17:46, Sokolov Yura wrote: Alvaro Herrera писал 2017-05-15 20:13: As I understand, these patches are logically separate, so putting them together in a single file isn't such a great idea. If you don't edit the patches further, then

Re: [HACKERS] Small improvement to compactify_tuples

On 2017-05-17 17:46, Sokolov Yura wrote: Alvaro Herrera писал 2017-05-15 20:13: As I understand, these patches are logically separate, so putting them together in a single file isn't such a great idea. If you don't edit the patches further, then you're all set because we already have the

Re: [HACKERS] Small improvement to compactify_tuples

Alvaro Herrera писал 2017-05-15 20:13: As I understand, these patches are logically separate, so putting them together in a single file isn't such a great idea. If you don't edit the patches further, then you're all set because we already have the previously archived patches. Next commitfest

Re: [HACKERS] Small improvement to compactify_tuples

Sokolov Yura wrote: > Sokolov Yura писал 2017-05-15 18:23: > > Alvaro Herrera писал 2017-05-15 18:04: > > > Please add these two patches to the upcoming commitfest, > > > https://commitfest.postgresql.org/ > > > > Thank you for suggestion. > > > > I've created

Re: [HACKERS] Small improvement to compactify_tuples

Sokolov Yura писал 2017-05-15 18:23: Alvaro Herrera писал 2017-05-15 18:04: Please add these two patches to the upcoming commitfest, https://commitfest.postgresql.org/ Thank you for suggestion. I've created https://commitfest.postgresql.org/14/1138/ As I could understand, I should attach

Re: [HACKERS] Small improvement to compactify_tuples

Alvaro Herrera писал 2017-05-15 18:04: Please add these two patches to the upcoming commitfest, https://commitfest.postgresql.org/ Thank you for suggestion. I've created https://commitfest.postgresql.org/14/1138/ As I could understand, I should attach both patches to single email to be show

Re: [HACKERS] Small improvement to compactify_tuples

Please add these two patches to the upcoming commitfest, https://commitfest.postgresql.org/ -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)

Re: [HACKERS] Small improvement to compactify_tuples

Sokolov Yura писал 2017-05-15 15:08: Heikki Linnakangas писал 2017-05-15 12:06: On 05/14/2017 09:47 PM, Sokolov Yura wrote: Good day, everyone. I've been playing a bit with unlogged tables - just random updates on simple key-value table. I've noticed amount of cpu spent in a

Re: [HACKERS] Small improvement to compactify_tuples

Heikki Linnakangas писал 2017-05-15 12:06: On 05/14/2017 09:47 PM, Sokolov Yura wrote: Good day, everyone. I've been playing a bit with unlogged tables - just random updates on simple key-value table. I've noticed amount of cpu spent in a compactify_tuples (called by PageRepareFragmentaion).

Re: [HACKERS] Small improvement to compactify_tuples

On 05/14/2017 09:47 PM, Sokolov Yura wrote: Good day, everyone. I've been playing a bit with unlogged tables - just random updates on simple key-value table. I've noticed amount of cpu spent in a compactify_tuples (called by PageRepareFragmentaion). Most of time were spent in qsort of