From: Heikki Linnakangas <hlinnakangas@vmware.com>
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
Date: Thu, 03 Jan 2013 19:02:08 +0200
Message-ID: <50E5B990.5010101@vmware.com> (raw)
In-Reply-To: <05F9B935C9F93D4DA5ED64B6D321477C215B25@bsw00i-1402.lcsys.ch>
References: <05F9B935C9F93D4DA5ED64B6D321477C215B00@bsw00i-1402.lcsys.ch>
<05F9B935C9F93D4DA5ED64B6D321477C215B25@bsw00i-1402.lcsys.ch>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, Daniel.Westermann@lcsystems.ch
Subject: Re: FW: performance issue with a 2.5gb joinded table
In-Reply-To: <50E5B990.5010101@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox