Re: [PERFORM] Tuning queries on large database
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. Show us the explain analyze on your queries. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning queries on large database
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
Re: [PERFORM] Tuning queries on large database
not so bad for oracle. What about for PG ? How data is stored I agree with the datatype issue. Smallint, bigint, integer... add a constraint... Also the way order of the records in the database is very important. As you seem to have a very large static population in your table, you should insert it, ordered by your favourite selection index (looks like it's poste). Also, you have a lot of static data which pollutes your table. Why not create two tables, one for the current year, and one for all the past years. Use a view to present a merged view. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Tuning queries on large database
You often make sums. Why not use separate tables to cache these sums by month, by poste, by whatever ? Rule on insert on the big table updates the cache tables. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
