Pb de perf sur une grosse base

Bonjour,

J'ai des problèmes de performance sur une base PG, et je ne sais comment
améliorer les choses. J'ai 2 questions : une au sujet du mode de stockage
des données dans la base, l'autre concernant l'optimisation de requêtes.

Je cherche à comparer Oracle et PG. Toutes nos bases opérationnelles
sont sous oracle depuis environ 15 ans. Maintenant j'essaie de remplacer
Oracle par PG.

J'ai une plateforme de test sous linux (un server Dell, 4 Gb RAM,
bi-processor, Linux Red Hat 9 (2.4.20-31.9)) avec 2 bases, 1 sous
oracle (8i), 1 sous PG 7.4.2. Les 3 bases ont la même structure, même
contenu environ 100 Gb chacune. J'ai écrit des benches, représentatifs
de notre activité sur les bases. Mon problème est que je travaille avec
de très grosses tables, plus de 100 millions de lignes, chaque ligne
a environ 160 colonnes et une taille moyenne de 256 octets.

Sous oracle la SGA est de 500 Mb.
Pour PG le postgresql.conf :
max_connections = 1500
shared_buffers = 30000
sort_mem = 50000
effective_cache_size = 200000
et les valeurs par défaut pour les autres paramètres.

J'ai une table nommée "data" qui ressemble à :
bench=> \d data
Table "public.data"
Column | Type | Modifiers
------------+-----------------------------+-----------
num_poste | numeric(9,0) | not null
dat | timestamp without time zone | not null
datrecu | timestamp without time zone | not null
rr1 | numeric(5,1) |
qrr1 | numeric(2,0) | ...
... all numeric fields
...
Indexes:
"pk_data" primary key, btree (num_poste, dat)
"i_data_dat" btree (dat)

Elle contient 1000 valeurs # de "num_poste" et pour chacune on compte
environ 125000 valeurs # de "dat" (en fait 1 ligne par heure sur 15 ans).

J'ai exécuté un vacuum analyze sur la table.

bench=> select * from tailledb ;
schema | relfilenode | table | index | reltuples | size
--------+-------------+------------------+------------+-------------+----------
public | 125615917 | data | | 1.25113e+08 | 72312040
public | 251139049 | data | i_data_dat | 1.25113e+08 | 2744400
public | 250870177 | data | pk_data | 1.25113e+08 | 4395480

Ma première remarque est que la table occupe beaucoup plus de place
sur PG (70 Gb) que sur oracle (35 Gb).
Le calcul : 125 000 000 rows x 256 b = 32 Gb donne une idée du volume
occupé, pas si mauvais pour oracle. Qu'en est-il pour PG ?
Comment les données sont stockées ?

D'autre part les différentes requêtes du bench sont "simples" : pas
de jointures, sous-interrogation, ... et utilisent les index (testées
avec un explain pour en être sûre) :
Q1 select_court : access to about 700 rows : 1 "num_poste" et 1 mois
(using PK : num_poste=p1 and dat between p2 and p3)
Q2 select_moy : access to about 7000 rows : 10 "num_poste" et 1 mois
(using PK : num_poste between p1 and p1+10 and dat between p2 and p3)
Q3 select_long : about 250 000 rows : 2 "num_poste"
(using PK : num_poste in (p1,p1+2))
Q4 select_tres_long : about 3 millions rows : 25 "num_poste"
(using PK : num_poste between p1 and p1 + 25)

Les requêtes "courtes" (Q1 et Q2) s'exécutent en quelques secondes sur
oracle et PG. Mais les différences deviennent importantes pour Q3 puis Q4 :
Q3 : 8 seconds avec oracle
80 sec avec PG
et Q4 : 28s avec oracle
17m20s avec PG !

Bien sûr lorsque je lance en parallèle 1000 requêtes Q3 ou Q4 les perf
deviennent désastreuses !
Je n'arrive pas à comprendre ce résultat. Le mode d'exécution de toutes
ces requêtes est le même, par index. J'ai lu tous les articles recommendés
sur le site PG.
J'ai essayé avec une table contenant 30 millions de lignes, les résultats
sont similaires.

Qu'est-ce que je peux faire ?
Merci pour votre aide !

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr *
********************************************************************