Hacker News Reader

Comment by nhoughto | Hacker News Reader

Interesting to see there is still some more to squeeze out of the old girl yet. Most seem like things that could have attempted before now but I guess weren’t the top pain points to fix, I wonder how many more 4-6% savings there are in there, good to see people going deep and keeping the improvements coming.

View on HN

I'm currently working (on and off) on improving the performance of specific "kinds"/"shapes" of btree indexes by several %s (depending on shape it can be tens of percents). I hope and expect that it'll be ready for Pg16, but that of course assumes that the concept behind the implementation is acceptable.

See https://www.postgresql.org/message-id/flat/CAEze2Wg52tsSWA9F... for specifics

Thanks so much for your contributions! The community for PG is so great.

Anyone who is using a distro package of pg has the largest opportunity because they are all built wrong. A peak-optimized binary will get you much more than 4% speed up over generic packages.

Can you please elaborate, what’s wrong and what do I need to do when building from source?

compile with --march=native can often be significant.

Compiling for your target is good, not using the PLT for mem* functions helps a ton, feedback-directed optimization and LTO and/or post-link optimization helps the most. For CPU-bound workloads of course.

Will native march option work in case of cloud where you're not in control of cpu?

I haven't used a cloud where you don't dictate the exact CPU you want. EC2, GCE, and Azure all provide specific guarantees. Even if you wanted a generic any-cloud binary you would probably compile with -march=skylake or, if truly paranoid, sandybridge.

Many distro packages are, by contrast, compiled for k8-generic. Freaking Opteron!

BTW "native" is not really recommendable. One really ought to target a particular platform.

Do google and AWS hosted postgres do this? I assume, but I am not sure how to check.

I don't know but it would be out-of-character for Google to have a C program that wasn't optimized to the hilt.

Using PG for years and this is the first time somebody made me aware of this, so thank you.

In the long tail it’s more a matter of choosing a module or concern and squeezing a percent here and a percent there until it adds up to 5%, rather than looking for tall tent poles. Also counting calls for a fixed workload, and making sure this call stack really should call functionB nine times for three results or if that should be six or three. Function is still exactly the same cost but is now 1% of the overall cost instead of 3%.

Reply on news.ycombinator.com