-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 04/14/2014 03:17 PM, Jim Nasby wrote: > On 4/14/14, 4:50 PM, Andres Freund wrote: >> On 2014-04-14 14:33:03 -0700, Joe Conway wrote: >>> I realize there are many things that can be done to improve my >>> specific scenario, e.g. drop indexes before loading, change >>> various configs, etc. My purpose for this post is to ask if it >>> is really expected to get over 20 times as much WAL as heap >>> data? >> >> I'd bet a large percentage of this will be full page images of >> the index. The values you index are essentially distributed over >> the whole index, so you'll modifiy the same indx values >> repeatedly. But often enough it won't be in the same checkpoint >> and thus will create full page images. > > My thought exactly... > > ISTM that we should be able to push all the index inserts to the > end of the transaction. That should greatly reduce the amount of > full page writes. That would also open the door for doing all the > index inserts in parallel.
That's the thing. I'm sure there is tuning and other things to improve this particular case, but creating over 20 times as much WAL as real data seems like pathological behavior to me. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJTTGZ8AAoJEDfy90M199hlhXQQAJfs/FOk6W83bVdU4pRN5bVI HW0jeMwX4NtUigW2vk5tKWcCgWKDTZvvV2TE3C7XPQnoa4TC51bjFJDHErKxNV8i vFk47OFvg4AEoILeRgsemLJFCc0jDlc5VClnNiH8esUjmOAv9vFktJ3JymVdaIYL 3ytxMyF/KYiCeWQlu6WZTfFD9qqdZh6dWIkm6m8gVXJstr+jVVkxHe2lNQe77nEi DycHy/4dmMd4QThxw3sRzEGW1GNGGk/6X1zmZECXYu7v95E5dFLl1oD2CFUMpoGh D5LWZqfuyhN0lHLe5nwTvvYeTGMg5+r/fVm1Y4oWbAQPjWycZcrMCFPho7U+5CHC XPt6FuaIZlZ4GBPCNj398xyPZdwWkOBEhfvhu601ibOVbQwBECnWQxGpMTukCvxT giaZD8C1Ty/MAq0lleAPdkNN91GPqMkhd46sG/aVMDOGtjfJkfYFeqj6b7rbFknw +wdioB0+vTFQ+hJ3yzVIAR09RoL0o3UW/8C1kOE5jIjJZPxdta5or7ZD77y1RLJI /UVU2LVcyS82ddmWcWM6/q5LaqlPgityZZmIoi8Hxp1ywNzIZcyY0t1RJkMrrb0I LIOTSizFA1zFM3lDNV7sF261DQS9IjOSgeSMIfB9zJQArWWwJ7c/DiTEbwpZu7iz 0VKmaJk15zqf1FWEdX+I =l6F9 -----END PGP SIGNATURE----- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
