Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TrE4Z-0004IZ-2w for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jan 2013 20:40:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TrE4X-0000ql-Ty for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jan 2013 20:40:57 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TrE4W-0000qf-On for pgsql-performance@postgresql.org; Fri, 04 Jan 2013 20:40:56 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TrE4V-0003AV-0k for pgsql-performance@postgresql.org; Fri, 04 Jan 2013 20:40:56 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r04KepJI024976; Fri, 4 Jan 2013 15:40:52 -0500 (EST) From: Tom Lane To: Heikki Linnakangas cc: Daniel Westermann , "'pgsql-performance@postgresql.org'" Subject: Re: FW: performance issue with a 2.5gb joinded table In-reply-to: <50E5B990.5010101@vmware.com> References: <05F9B935C9F93D4DA5ED64B6D321477C215B00@bsw00i-1402.lcsys.ch> <05F9B935C9F93D4DA5ED64B6D321477C215B25@bsw00i-1402.lcsys.ch> <50E5B990.5010101@vmware.com> Comments: In-reply-to Heikki Linnakangas message dated "Thu, 03 Jan 2013 19:02:08 +0200" Date: Fri, 04 Jan 2013 15:40:51 -0500 Message-ID: <24975.1357332051@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Heikki Linnakangas writes: > One difference is that numerics are stored more tightly packed on > Oracle. Which is particularly good for Oracle as they don't have other > numeric data types than number. On PostgreSQL, you'll want to use int4 > for ID-fields, where possible. An int4 always takes up 4 bytes, while a > numeric holding an integer value in the same range is typically 5-9 bytes. Replacing those numeric(8) and numeric(16) fields with int4 and int8 would be greatly beneficial to comparison and hashing performance, not just table size. I'm a bit surprised that EDB's porting tools evidently don't do this automatically (I infer from the reference to PPAS that the OP is using EDB ...) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance