On Mon, Mar 12, 2018 at 1:29 AM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 9 March 2018 at 02:11, David Rowley <david.row...@2ndquadrant.com> wrote: >> On 8 March 2018 at 18:40, Andrew Dunstan <andrew.duns...@2ndquadrant.com> >> wrote: >>> select * from t; >>> fastdef tps = 107.145811 >>> master tps = 150.207957 >>> >>> "select * from t" used to be about a wash, but with this patch it's >>> got worse. The last two queries were worse and are now better, so >>> that's a win. >> >> How does it compare to master if you drop a column out the table? >> Physical tlists will be disabled in that case too. I imagine the >> performance of master will drop much lower than the all columns >> missing case. > > I decided to test this for myself, and the missing version is still > slightly slower than the dropped column version, but not by much. I'm > not personally concerned about this. > > The following results are with 1000 column tables with 64 rows each.
I've done some more extensive benchmarking now. Here are some fairly typical results from pgbench runs done on standard scale 100 pgbench data: [andrew@foo tests]$ PATH=$HOME/pg_fast_def/root/HEAD/inst/bin:$PATH pgbench -S -c 10 -j 5 -T 60 test starting vacuum...end. transaction type: <builtin: select only> scaling factor: 100 query mode: simple number of clients: 10 number of threads: 5 duration: 60 s number of transactions actually processed: 2235601 latency average = 0.268 ms tps = 37256.886332 (including connections establishing) tps = 37258.562925 (excluding connections establishing) [andrew@foo tests]$ PATH=$HOME/pg_head/root/HEAD/inst/bin:$PATH pgbench -S -c 10 -j 5 -T 60 test starting vacuum...end. transaction type: <builtin: select only> scaling factor: 100 query mode: simple number of clients: 10 number of threads: 5 duration: 60 s number of transactions actually processed: 2230085 latency average = 0.269 ms tps = 37164.696271 (including connections establishing) tps = 37166.647971 (excluding connections establishing) So generally the patched code and master are pretty much on a par. I have also done some testing on cases meant to stress-test the feature a bit - two 1000 column, all columns having defaults, one with a dropped column. For the fast_default case I then also copied the tables (and again dropped a column) so that the data files and table definitions would match fairly closely what was being tested in the master branch. The scripts in the attached tests.tgz. The test platform is an Amazon r4.2xlarge instance running RHEL7. There are two sets of results attached, one for 64 row tables and one for 50k row tables. The 50k row results are fairly unambiguous, the patched code performs as well as or better (in some cases spectacularly better) than master. In a few cases the patched code performs slightly worse than master in the last (fdnmiss) case with the copied tables. > > Going by the commitfest app, the patch still does appear to be waiting > on Author. Never-the-less, I've made another pass over it and found a > few mistakes and a couple of ways to improve things: > working on these. Should have a new patch tomorrow. > Thanks again for working on this feature. I hope we can get this into PG11. > Thanks for you help. I hope so too. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
results.t100r50k
Description: Binary data
results.t100r64
Description: Binary data
tests.tgz
Description: application/compressed