X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F34B214B2806 for ; Tue, 10 Aug 2004 10:39:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79926-09 for ; Tue, 10 Aug 2004 13:39:24 +0000 (GMT) Received: from snowwhite.lulu.com (mail.lulu.com [66.193.5.50]) by svr1.postgresql.org (Postfix) with ESMTP id B043814B27FF for ; Tue, 10 Aug 2004 10:39:20 -0300 (ADT) Received: from [10.0.0.32] (meatwad.rdu.lulu.com [10.0.0.32]) (authenticated bits=0) by snowwhite.lulu.com (8.12.8/8.12.8) with ESMTP id i7ADdBQR003010 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Tue, 10 Aug 2004 09:39:11 -0400 Message-ID: <4118D008.1070606@lulu.com> Date: Tue, 10 Aug 2004 09:39:20 -0400 From: Bill Montgomery User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Column order performance Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/120 X-Sequence-Number: 7756 Does the order of columns of varying size have any effect on SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where an integer primary key is listed first in the table and alternatively listed after some large varchar or text columns? For example, is this different performance-wise: CREATE TABLE foo ( foo_id serial, foo_data varchar(8000), primary key (foo_id) ); from this? CREATE TABLE bar ( bar_data varchar(8000), bar_id serial, primary key (bar_id) ); My suspicion is it would never make a difference since the index will be searched when querying "WHERE [foo|bar]_id=?" (as long as the planner decides to use the index). What about a case where a sequential scan _must_ be performed? Could the order of columns make a difference in the number of pages read/written if there is a mix of small and large columns? Thanks for your help. Best Regards, Bill Montgomery