On Wed, 2004-08-04 at 08:44, Valerie Schneider DSI/DEV wrote:
> Hi,
>
> I have some problem of performance on a PG database, and I don't
> know how to improve. I Have two questions : one about the storage
> of data, one about tuning queries. If possible !
>
> My job is to compare Oracle and Postgres. All our operational databases
> have been running under Oracle for about fifteen years. Now I try to replace
> Oracle by Postgres.
You may assume some additional hardware may be required -- this would be
purchased out of the Oracle License budget :)
> My first remark is that the table takes a lot of place on disk, about
> 70 Gb, instead of 35 Gb with oracle.
> 125 000 000 rows x 256 b = about 32 Gb. This calculation gives an idea
> not so bad for oracle. What about for PG ? How data is stored ?
This is due to the datatype you've selected. PostgreSQL does not convert
NUMERIC into a more appropriate integer format behind the scenes, nor
will it use the faster routines for the math when it is an integer.
Currently it makes the assumption that if you've asked for numeric
rather than integer or float that you are dealing with either large
numbers or require high precision math.
Changing most of your columns to integer + Check constraint (where
necessary) will give you a large speed boost and reduce disk
requirements a little.
> The different queries of the bench are "simple" queries (no join,
> sub-query, ...) and are using indexes (I "explained" each one to
> be sure) :
Care to send us the EXPLAIN ANALYZE output for each of the 4 queries
after you've improved the datatype selection?
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
