Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 551746502B9 for ; Fri, 11 Jul 2008 10:44:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79234-03 for ; Fri, 11 Jul 2008 10:44:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 714A26500EE for ; Fri, 11 Jul 2008 10:44:31 -0300 (ADT) Received: from perhan.alvh.no-ip.org (190-95-31-93.bk17-dsl.surnet.cl [190.95.31.93]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m6BDkcuK031442 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Fri, 11 Jul 2008 06:46:42 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 6E4D147C7E; Fri, 11 Jul 2008 09:44:23 -0400 (CLT) Date: Fri, 11 Jul 2008 09:44:23 -0400 From: Alvaro Herrera To: Ow Mun Heng Cc: Mario Weilguni , Pgsql performance Subject: Re: Altering a column type - Most efficient way Message-ID: <20080711134423.GA4110@alvh.no-ip.org> References: <1215676380.15319.2.camel@neuromancer.home.net> <4875C9FA.7020101@sime.com> <1215682481.15319.14.camel@neuromancer.home.net> <20080710135756.GD3757@alvh.no-ip.org> <1215742620.853.10.camel@neuromancer.home.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1215742620.853.10.camel@neuromancer.home.net> User-Agent: Mutt/1.5.18 (2008-05-17) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Fri, 11 Jul 2008 06:46:43 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/98 X-Sequence-Number: 30698 Ow Mun Heng wrote: > If it were this simple a change, I'm not certain why (I believe) PG is > checking each and every row to see if it will fit into the new column > definition/type. Because the code that does the ALTER TYPE is very generic, and it doesn't (yet) have an optimization that tells it to skip the check and the possible table rewrite in the cases where it's obviously not needed (like this one). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.