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
signature.asc
Description: This is a digitally signed message part