Re: Forced external sort?

On 09/13/2015 01:53 AM, Jeff Janes wrote:
> On Fri, Sep 11, 2015 at 11:45 AM, <rolf@winmutt.com
> <mailto:rolf@winmutt.com>> 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@sss.pgh.pa.us)
>     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@mail.gmail.com)
>     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@joeconway.com)
>
>
>
> 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