On Fri, Sep 11, 2015 at 11:45 AM, <rolf@xxxxxxxxxxx> wrote:
Only the rounding issue was removed in 9.3 so that it could use the full 1GB, (rather than getting to 512MB plus a few bytes, and then deciding it didn't have room to double). The 1G limit itself was not removed until 9.4.
Note that these limits were not on the total amount of data being sorted, but on the size of the array of row headers, and so limits the number of rows, regardless of the size of the rows.
Cheers,
I've got a poorly indexed query and was attempting a quick work around in production by increasing work_mem when it was called. EXPLAIN ANALYZE is telling me this:
Sort Method: external sort Disk: 1253824kB
So I set the work_mem to 2gb, still going to disk. I read Tom's suggestion here (http://www.postgresql.org/message-id/1936.1298394374@xxxxxxxxxxxxx) and went all the way up to 92G or work_mem.
Did more googling and found this reference (http://www.postgresql.org/message-id/CAMkU=1w2y87NJueqwN8-HK2KDb4UOihFAJXpO1NZ3EkHZvBmmQ@xxxxxxxxxxxxxx) to a 1G sort limit that is going to be removed in 9.3.
Am I possibly experiencing the same issue even though I am on 9.3.1? Was it really not fixed until 9.4? (http://www.postgresql.org/message-id/557C7213.8000704@xxxxxxxxxxxxx)
Only the rounding issue was removed in 9.3 so that it could use the full 1GB, (rather than getting to 512MB plus a few bytes, and then deciding it didn't have room to double). The 1G limit itself was not removed until 9.4.
Note that these limits were not on the total amount of data being sorted, but on the size of the array of row headers, and so limits the number of rows, regardless of the size of the rows.
Jeff
