X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4D57C9DCAD8 for ; Thu, 16 Mar 2006 14:51:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78457-08 for ; Thu, 16 Mar 2006 14:51:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from prod01.jerrysievers.com (adsl-64-118-249-70.netrox.net [64.118.249.70]) by postgresql.org (Postfix) with ESMTP id 67F559DCA18 for ; Thu, 16 Mar 2006 14:51:12 -0400 (AST) Received: (from gsievers@localhost) by prod01.jerrysievers.com (8.11.0/8.11.0) id k2GIpCl18083; Thu, 16 Mar 2006 13:51:12 -0500 To: pgsql-admin@postgresql.org Subject: Re: Curious run-away index build on upgrade to 8.1.3 References: <16725.1142523155@sss.pgh.pa.us> <18502.1142533042@sss.pgh.pa.us> From: Jerry Sievers Date: 16 Mar 2006 13:51:12 -0500 In-Reply-To: Tom Lane's message of "Thu, 16 Mar 2006 13:17:22 -0500" Message-ID: Lines: 41 X-Mailer: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] X-Spam-Score: 0.096 X-Spam-Level: X-Archive-Number: 200603/139 X-Sequence-Number: 20896 Hi Tom; Tom Lane writes: > Jerry Sievers writes: > > Platform is Solaris 2.9 > > I think this might be the key... > > I can reproduce an unreasonably long runtime if I use src/port/qsort.c > (as we do on Solaris), but not with glibc's version of qsort. I think > you are seeing the poor-choice-of-qsort-pivots problem recently > discussed on -hackers: > http://archives.postgresql.org/pgsql-hackers/2006-02/msg00590.php > The fact that the data contains a very large fraction of nulls > probably contributes to the problem (thanks for sending it BTW). > The reason you only see it with maintenance_work_mem >= 64M is that > below that, the problem doesn't fit into work_mem and so we don't use > qsort at all. What I still don't understand though is why you see it > in 8.1 and not 8.0 ... the src/port/qsort.c code didn't change at all > between those versions. Maybe 8.0 isn't taking the qsort code path, > perhaps because it uses a shade more memory or some such. Could you > try increasing maintenance_work_mem even more, like to 100M, > and see if 8.0 gets slow? I did as you suggest here; taking the MWM setting incrementally up to nearly a Gig in 100M increments, also raised and lowered the work_mem setting too. Tried a dozen or more combos and in every case, the index built quickly on 8.0.3. Very interesting! Thanks for all your help. -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobile http://www.JerrySievers.com/