Thread: Follow up to irc on CREATE INDEX vs. maintenance_work_mem on 9.3
Tom, First of all, thanks for your help on IRC last time with that CREATE INDEX memory consumption problem. As has been pointed out in a stackexchange answer to my question[1], it is indeed the limitation of pre-9.4 versions, but the limit is imposed on memtuples array, rather than total memory the sort in CREATE INDEX may allocate. The memtuples won't grow further than MaxAllocSize and I've got 24x50x10^6 = 1200MB, which just doesn't fit. We've got a customer who is testing a migration to PostgreSQL-9.3 (from $some_other_db), thus they load the tables first (some of their tables have 10-100 million rows), then create the indexes and they constantly see disk sort being used despite lots of available RAM and maintenance_work_mem set to increasingly higher values. Now my question, is it feasible to back-patch this to 9.3? Or should we tell the customer to wait before 9.4 is released? Thanks. -- Alex [1] http://dba.stackexchange.com/questions/83600/postgresql-create-index-memory-requirement
Alex,
* Alex Shulgin (ash@commandprompt.com) wrote:
> Tom,
>
> First of all, thanks for your help on IRC last time with that CREATE
> INDEX memory consumption problem.
Doubt it was Tom, but if it was, wanna share what channel on IRC it was?
:D
> Now my question, is it feasible to back-patch this to 9.3? Or should we
> tell the customer to wait before 9.4 is released?
I'm aware of a few folks who have back-patched this change and use
custom-built binaries, but it won't be done by the community/PGDG as
it's a new feature and not a bug fix.
Thanks!
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> * Alex Shulgin (ash@commandprompt.com) wrote:
>> Tom,
>>
>> First of all, thanks for your help on IRC last time with that CREATE
>> INDEX memory consumption problem.
> Doubt it was Tom, but if it was, wanna share what channel on IRC it was?
> :D
Must've been my evil twin.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Stephen Frost <sfrost@snowman.net> writes: >> * Alex Shulgin (ash@commandprompt.com) wrote: >>> Tom, >>> >>> First of all, thanks for your help on IRC last time with that CREATE >>> INDEX memory consumption problem. > >> Doubt it was Tom, but if it was, wanna share what channel on IRC it was? >> :D > > Must've been my evil twin. Sorry, I must be under false impression that RhodiumToad is *your* nick on #postgresql at freenode. I don't recall who told me that, but I was pretty sure it's you. :-p -- Alex
Alex Shulgin <ash@commandprompt.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Must've been my evil twin.
> Sorry, I must be under false impression that RhodiumToad is *your* nick
> on #postgresql at freenode. I don't recall who told me that, but I was
> pretty sure it's you. :-p
That's Andrew Gierth, I believe. I'm not much for nicks; when I do use
IRC, I'm tgl.
regards, tom lane
>>>>> "Alex" == Alex Shulgin <ash@commandprompt.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> writes:>> Must've been my evil twin. Alex> Sorry, I must be under false impression that RhodiumToad isAlex> *your* nick on #postgresql at freenode. I don't recallwhoAlex> told me that, but I was pretty sure it's you. :-p ... what People do occasionally make jokes on IRC about me being Tom's clone; I know they mean it in a positive way but I still find it *extremely* annoying, so I do try and discourage it. (If they're making those same jokes elsewhere, I haven't been aware of it, but please consider this a polite public request to stop.) My first name is easily visible in the irc gecos field: *** RhodiumToad is ~andrew@[my hostname] (Andrew) and there is also the IRC users list on the wiki: http://wiki.postgresql.org/wiki/IRC2RWNames -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes: >>>>>> "Alex" == Alex Shulgin <ash@commandprompt.com> writes: > > > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> Must've been my evil twin. > > Alex> Sorry, I must be under false impression that RhodiumToad is > Alex> *your* nick on #postgresql at freenode. I don't recall who > Alex> told me that, but I was pretty sure it's you. :-p > > ... what > > People do occasionally make jokes on IRC about me being Tom's clone; I > know they mean it in a positive way but I still find it *extremely* > annoying, so I do try and discourage it. (If they're making those same > jokes elsewhere, I haven't been aware of it, but please consider this > a polite public request to stop.) > > My first name is easily visible in the irc gecos field: > > *** RhodiumToad is ~andrew@[my hostname] (Andrew) > > and there is also the IRC users list on the wiki: > http://wiki.postgresql.org/wiki/IRC2RWNames Andrew, Tom, Sorry for the confusion. And, Andrew, thanks again for the help! :-) -- Alex