-----Original Message-----
From: Heikki Linnakangas [mailto:[email protected]]
Sent: Donnerstag, 3. Januar 2013 18:02
To: Daniel Westermann
Cc: '[email protected]'
Subject: Re: [PERFORM] FW: performance issue with a 2.5gb joinded table
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 =
> SQL> '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
Thanks for poiting that out, Heikki.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
