Performance Enhancement/Fix for Array Utility Functions
Mike Lewis <mikelikespie@gmail.com>
From: Mike Lewis <mikelikespie@gmail.com>
To: pgsql-hackers@postgresql.org
Date: 2010-03-31T07:39:57Z
Lists: pgsql-hackers
Attachments
- detoast-headers-for-array-functions-001.patch (application/octet-stream) patch
I noticed while doing work with very large arrays that several functions such as array_length detoast the entire array instead of only what is required. I found the solution to be just unpacking the header portion of the array and ignoring the rest. Since the header (including the dimensions) is variable length, I just unpack the size of what the header would be if it had MAXDIM dimensions. (Patch is attached) I made a test case to demonstrate performance gains (watch out, it creates a big table): create temporary table foo as select array_agg(i) as a from ( select generate_series(1,10000000) as i) as bar; \timing select array_length(a, 1) from foo; -- Run a few times. First time will be cold Results (after warming up) Before patch: Time: 6.251 ms Time: 6.078 ms Time: 5.983 ms After patch: Time: 0.401 ms Time: 0.397 ms Time: 0.441 ms ... -- Michael Lewis lolrus.org mikelikespie@gmail.com