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 68CF69DC840 for ; Thu, 16 Mar 2006 18:03:11 -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 26465-10 for ; Thu, 16 Mar 2006 18:03:12 -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 D41739DC837 for ; Thu, 16 Mar 2006 18:03:08 -0400 (AST) Received: (from gsievers@localhost) by prod01.jerrysievers.com (8.11.0/8.11.0) id k2GM3Aw10901; Thu, 16 Mar 2006 17:03:10 -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> <19992.1142541663@sss.pgh.pa.us> From: Jerry Sievers Date: 16 Mar 2006 17:03:10 -0500 In-Reply-To: Tom Lane's message of "Thu, 16 Mar 2006 15:41:03 -0500" Message-ID: Lines: 24 X-Mailer: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.085 required=5 tests=[AWL=0.085] X-Spam-Score: 0.085 X-Spam-Level: X-Archive-Number: 200603/141 X-Sequence-Number: 20898 Well, I'll be dipped! More below; Tom Lane writes: > On further analysis, it seems the problem is dependent on the exact > ordering of the inputs to the qsort function. So not only do you need > maintenance_work_mem to be large enough that the code will try to use > qsort, but the physical order of the rows in the table matters. > I suspect that you are testing on an 8.0 table with a different physical > row order --- if you drop the table and reload it from the same dump you > loaded into 8.1, does it get slow? Tom, your theory is correct. If we had to build that index from scratch on 8.0, with the data ordered as it was when I made the original dump, it would have stalled all the same. I just reproduced this on an 8.0.3 box. FYI -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobile http://www.JerrySievers.com/