Message-ID: <50E5B990.5010101@vmware.com>
Date: Thu, 03 Jan 2013 19:02:08 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.11) Gecko/20121122 Icedove/10.0.11
MIME-Version: 1.0
To: Daniel Westermann <Daniel.Westermann@lcsystems.ch>
CC: "'pgsql-performance@postgresql.org'" <pgsql-performance@postgresql.org>
Subject: Re: FW: performance issue with a 2.5gb joinded table
References: <05F9B935C9F93D4DA5ED64B6D321477C215B00@bsw00i-1402.lcsys.ch>
	<05F9B935C9F93D4DA5ED64B6D321477C215B25@bsw00i-1402.lcsys.ch>
In-Reply-To: <05F9B935C9F93D4DA5ED64B6D321477C215B25@bsw00i-1402.lcsys.ch>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 03.01.2013 15:30, Daniel Westermann wrote:
> What additionally makes me wonder is, that the same table in oracle is taking much less space than in postgresql:
>
> SQL>  select  sum(bytes) from dba_extents where segment_name = 'TEST1';
> SUM(BYTES)
> ----------
> 1610612736
>
> select pg_relation_size('mgmtt_own.test1');
> pg_relation_size
> ------------------
>         2502082560
> (1 row)
>
> (sysdba@[local]:7777) [bi_dwht]>  \d+ mgmtt_own.test1
>                               Table "mgmtt_own.test1"
>              Column            |     Type      | Modifiers | Storage | Description
> ------------------------------+---------------+-----------+---------+-------------
> slsales_batch                | numeric(8,0)  |           | main    |
> slsales_checksum             | numeric(8,0)  |           | main    |
> slsales_reg_id               | numeric(8,0)  |           | main    |
> slsales_prod_id              | numeric(8,0)  |           | main    |
> slsales_date_id              | numeric(8,0)  |           | main    |
> slsales_pos_id               | numeric(8,0)  |           | main    |
> slsales_amt_sales_gross      | numeric(16,6) |           | main    |
> slsales_amt_sales_discount   | numeric(16,6) |           | main    |
> slsales_units_sales_gross    | numeric(8,0)  |           | main    |
> slsales_amt_returns          | numeric(16,6) |           | main    |
> slsales_amt_returns_discount | numeric(16,6) |           | main    |
> slsales_units_returns        | numeric(8,0)  |           | main    |
> slsales_amt_est_winnings     | numeric(16,6) |           | main    |
> Indexes:
>      "itest1" btree (slsales_date_id) CLUSTER, tablespace "mgmtt_idx"
>      "itest2" btree (slsales_prod_id), tablespace "mgmtt_idx"
> Has OIDs: no
> Tablespace: "mgmtt_dat"

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.

- Heikki


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance