On 09/13/2015 01:53 AM, Jeff Janes wrote:
On Fri, Sep 11, 2015 at 11:45 AM, <rolf@xxxxxxxxxxx <mailto:rolf@xxxxxxxxxxx>> wrote: 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.
Additional thing to consider is that the two sort methods (in-memory and on-disk) use different representations of the data, and the on-disk is much more compact. It's not uncommon to see 1:3 ratio, i.e. when external (on-disk) sort needs 100MB, the in-memory sort would need 300MB.
So when the on-disk sort needs 1253824kB, you'll probably need ~4GB work_mem to actually do that in memory.
regards -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
