Optimisation de postgres

Bonjour,

je suis actuellement en train de travailler sur la réalisation de cartes statistiques de population sur un département.

Le but de l'operation est de generer un quadrillage (avec des carrés de 1000m²) du departement, afin d'y generer une matrice.

J'obtiens 7760 carrés pour ce departement.
le but maintenant est de trouver les intersections de chaque carré avec les communes (472 communes au total).

les tables communes et quadrillages de postgres sont les suivantes.

CREATE TABLE commune
(
gid serial NOT NULL,
id_bdcarto float8,
population float8,
the_geom geometry,
CONSTRAINT commune_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE commune OWNER TO postgres;


-- Index: idx2_commune

-- DROP INDEX idx2_commune;

CREATE INDEX idx2_commune
ON commune
USING gist
(the_geom);

-- Index: idx_commune

-- DROP INDEX idx_commune;

CREATE INDEX idx_commune
ON commune
USING btree
(gid);

et quadrillage



CREATE TABLE quadrillage
(
id serial NOT NULL,
qrows int4,
qcols int4,
geom geometry,
CONSTRAINT quadrillage_pkey PRIMARY KEY (id),
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE quadrillage OWNER TO sig;


-- Index: idx2_quadrillage

-- DROP INDEX idx2_quadrillage;

CREATE INDEX idx2_quadrillage
ON quadrillage
USING gist
(geom);

-- Index: idx_quadrillage

-- DROP INDEX idx_quadrillage;



CREATE UNIQUE INDEX idx_quadrillage
ON quadrillage
USING btree
(id);


Mon code est le suivant:

$sql = "select * from quadrillage order by qrows desc, qcols asc ";
$res = pg_query($sql);
$nb =0;

$tabTimeR = array();

$matrice = "Z = [";
while($req = pg_fetch_object($res))
{
$id = $req->id;
$cols = $req->qcols;
$rows = $req->qrows;


$temps_debut_r = microtime_float();
$sql2 = "select b.population as population from quadrillage as a, commune as b where Intersects(a.geom,b.the_geom) and a.id = ".$id;

$res2 = pg_query($sql2);
$temps_fin_r = microtime_float();
array_push($tabTimeR,round($temps_fin_r - $temps_debut_r, 4));
$cpt = 0;
$total = 0;

while($req2 = pg_fetch_object($res2))
{

$intervention = $req2->population;
if($intervention > $tripleMoyenne)
$intervention = $tripleMoyenne;

$total += $intervention;
$cpt++;

}

if($cpt == 0)
$moyenne = 0;
else $moyenne = $total/$cpt;

if($cols == 0)
$matrice .= "[".$moyenne;

if($cols == $maxcols)
{
$nb++;
if($nb == $nb_rows)
$matrice .= ",".$moyenne."]";
else $matrice .= ",".$moyenne."],";

}

if($cols != 0 && $cols != $maxcols)
$matrice .= ",".$moyenne;

$nb++;
echo "\rNb requete: ".$nb."\t";
}

$matrice .="]";
$temps_fin = microtime_float();
echo "\nTemps d\'execution du script : ".round($temps_fin - $temps_debut, 4)."\nRequete plus courte :".min($tabTimeR)."\nrequete plus longue :".max($tabTimeR)."\n";


mon cpu : AMD Athlon(tm) 64 X2 Dual Core Processor 4400+
ma Ram : 2Go

J'obtiens comme resultat ceci:

Nb requete: 7760
Temps d\'execution du script : 88.212 s.
Requete plus courte :0.009 s.
requete plus longue :0.0312 s.


Le but est de faire de la generation de carte instantanée, mais mes temps de génération sont peu convainquant pour les visiteurs.
J'aurais voulu savoir donc, si j'avais fait les bons choix dans la création de mes index suivants les requetes effectuées.(des vacuum full analyze ont été executé en amont).

Cela revient à environ 93 requetes / s. est-ce que je peux faire plus rapide ?

je gagne en rapidité à partir du moment où j'augmente les carrés de mon quadrillage , mais la matrice devient plus petite, et je perds en qualité du résultat.

en esperant avoir été assez clair.

ps : un apercu de la réalisation pour y voir plus clair:
http://guibsou.free.fr/stats/image1.png
http://guibsou.free.fr/stats/pop.png


merci.

Je n'ai pas tout lu mais en général :
- les indexes doivent être créés sur les colonnes à forte cardinalité (beaucoup de valeurs distinctes)
- les indexes peuvent être crées sur les colonnes utilisées dans les jointures pour améliorer les perfs
- les indexes ne sont pas toujours utilisés si le nombre de lignes à renvoyer est d'au moins 20 à 30 % (en général) du nombre total de lignes dans la table
- effectuer souvent des vacuum full analyze pour rafraichir les stats à chaque nouvel ajoût de volumétrie conséquente
- regarde les plans d'exécution de tes requêtes pour voir comment il les execute : indexes, seq scan, hash join, nested loop, ...

Est ce que l'espacement des mailles de ton quadrillage varie ? Si oui combien de quadrillages possibles ? As tu essayé de faire le tri en deux passes ? Intersection horizontale d'abord, puis intersection verticale sur le résultat de l'intersection horizontale ?

j'ai reussi à contourner mon probleme de temps de mes requetes.
la generation de la 2eme image se fait avec matplotlib et la fonction pcolor().
Cela me demandait une plus grande precision d'où un quadrillage plus consequent et donc un temps de traitement aussi.
j'ai donc changé de fonction pour dessiner ma matrice à l'aide de la fonction contourf() de matplotlib.
Cela me permet de diminuer nettement mon quadrillage pour avoir des temps de requetage beaucoup moin long (des zones de 1500m² en 91s.)

l'explication sur ce poste:

http://http://www.developpez.net/for...d.php?t=553049

neanmoins, je gagne en qualité d'image mais je n'arrive pas à mes fins qui est d'avoir un dégradé fluide entre les differentes courbes genérées par la fonction contourf() de matplotlib


si quelqu'un à une solution ?

merci