Re: FW: performance issue with a 2.5gb joinded table - Mailing list pgsql-performance

From Daniel Westermann
Subject Re: FW: performance issue with a 2.5gb joinded table
Date
Msg-id 05F9B935C9F93D4DA5ED64B6D321477C21601D@bsw00i-1402.lcsys.ch
Whole thread Raw
In response to Re: FW: performance issue with a 2.5gb joinded table  (Heikki Linnakangas <hlinnakangas@vmware.com>)
List pgsql-performance
-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnakangas@vmware.com]
Sent: Donnerstag, 3. Januar 2013 18:02
To: Daniel Westermann
Cc: 'pgsql-performance@postgresql.org'
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'thave other numeric data types than number. On PostgreSQL, you'll want to use int4 for ID-fields, where possible.
Anint4 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.


pgsql-performance by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: FW: performance issue with a 2.5gb joinded table
Next
From: Alex Vinnik
Date:
Subject: Simple join doesn't use index