pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <rbt@rbt.ca>
To: Valerie Schneider DSI/DEV <Valerie.Schneider@meteo.fr>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Tuning queries on large database
Date: Wed, 04 Aug 2004 09:26:42 -0400
Message-ID: <1091625999.58593.47.camel@jester> (raw)
In-Reply-To: <200408041244.i74CihO19344@mu.meteo.fr>
References: <200408041244.i74CihO19344@mu.meteo.fr>

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

view thread (15+ messages)  latest in thread

Message-ID: <1091625999.58593.47.camel@jester>
Permalink:  ../1091625999.58593.47.camel@jester/
Also on:    postgresql.org/message-id/1091625999.58593.47.camel@jester

 · 

reply

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: rbt@rbt.ca, Valerie.Schneider@meteo.fr
  Subject: Re: Tuning queries on large database
  In-Reply-To: <1091625999.58593.47.camel@jester>

* 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