X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7E261D1B192 for ; Wed, 4 Aug 2004 10:26:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38701-01 for ; Wed, 4 Aug 2004 13:26:46 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id EBAC4D1B1C8 for ; Wed, 4 Aug 2004 10:26:35 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id 5D66276B36; Wed, 4 Aug 2004 09:26:45 -0400 (EDT) Subject: Re: Tuning queries on large database From: Rod Taylor To: Valerie Schneider DSI/DEV Cc: Postgresql Performance In-Reply-To: <200408041244.i74CihO19344@mu.meteo.fr> References: <200408041244.i74CihO19344@mu.meteo.fr> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-nHPhfWiYy5OOuJH9qNB8" Message-Id: <1091625999.58593.47.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 04 Aug 2004 09:26:42 -0400 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/49 X-Sequence-Number: 7685 --=-nHPhfWiYy5OOuJH9qNB8 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2004-08-04 at 08:44, Valerie Schneider DSI/DEV wrote: > Hi, >=20 > 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 ! >=20 > 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 repl= ace > 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 =3D 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? --=20 Rod Taylor Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL PGP Key: http://www.rbt.ca/signature.asc --=-nHPhfWiYy5OOuJH9qNB8 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (FreeBSD) iD8DBQBBEOQO6DETLow6vwwRAll7AJ0WFK5XCtJMKSB2/0k2bgp5pxpCDACfXmZO i6ic30tZ+P81wrwzyP49E6E= =CuEp -----END PGP SIGNATURE----- --=-nHPhfWiYy5OOuJH9qNB8--