Re: Forced external sort?

On Fri, Sep 11, 2015 at 11:45 AM, <rolf(at)winmutt(dot)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.

Cheers,

Jeff