Forums PostgreSQL.fr

FFW_Rude

Membre

2 requettes similaires ont des traitements EXTREMEMENT différents

Bonjour,

Je suis nouveau sur postgres (commencé il y a quelques mois, venant de Mysql).
J'ai un soucis sur une requête qui met 2minutes pour tourner sur une table et 5H sur l'autre (c'est la même requête).

La première table contient des couples de coordonées GPS et la deuxième des carrés délimité par des coordonées GPS.

En gros ca ressemble à ca :

adresses_01 (id,X,Y)
gps_01 (id,x_min,x_max,y_min,y_max).

"
SELECT
    t2.id,
FROM
    tables_gps.gps_01 t1
INNER JOIN
    tables_adresses.adresses_01 t2
ON
    t2."X" BETWEEN t1.x_min AND t1.x_max AND t2."Y" BETWEEN t1.y_min AND t1.y_max
WHERE
    t2.id='0'
"

Il y a environ 250000 lignes dans chaque table (adresses_XX et gps_XX)

J'ai des index sur le couple X,Y de la table adresse et un autre dans la table gps sur les quatres champs (x,y)
Etant donné que je fais des update fréquent sur les deux tables, id est une clé primaire sur adresses_XX et gps_XX

Du coup je ne comprend pas quelle peut etre la raison de ces différences de temps de traitement. Je me demandais si la CLUSTERISATION des index ne pourrait pas m'aider mais si oui, dois-je mettre le CLUSTER sur la clé primaire ? Ou sur mes index X,Y et x_min,y_min,x_max,y_max ?

Je sais que c'est pas forcement très clair mais si quelqu'un à besoin de précision, n'hésitez pas à demander.

Merci d'avance.

rjuju

Administrateur

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Bonjour,

pourriez-vous fournir un explain analyze des 2 requêtes ? Ainsi que la liste des index créés.

Vous pouvez sinon dans un premier dans effectuer un analyze sur les tables impactées par la requête lente (tables_gps.gps_01 et tables_adresses.adresses_01 si votre exemple porte sur les tables en question).

---

Julien.
https://rjuju.github.io/

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Bonjour

Voici

Nested Loop  (cost=0.00..353722.89 rows=124893 width=16) (actual time=261158.061..10304193.501 rows=99 loops=1)
  Join Filter: ((t2."X" >= (t1.x_min)::double precision) AND (t2."X" <= (t1.x_max)::double precision) AND (t2."Y" >= (t1.y_min)::double precision) AND (t2."Y" <= (t1.y_max)::double precision))
  ->  Seq Scan on gps_22 t1  (cost=0.00..3431.80 rows=177480 width=44) (actual time=0.036..1399.621 rows=177480 loops=1)
  ->  Materialize  (cost=0.00..20572.83 rows=57 width=20) (actual time=0.012..10.274 rows=2924 loops=177480)
        ->  Seq Scan on adresses_22 t2  (cost=0.00..20572.55 rows=57 width=20) (actual time=1570.240..1726.376 rows=2924 loops=1)
              Filter: ((id_maille_200m)::text = '0'::text)
Total runtime: 10304211.648 ms

Nested Loop  (cost=0.00..88186069.17 rows=33397899 width=16) (actual time=3060.373..3060.373 rows=0 loops=1)
  Join Filter: ((t2."X" >= (t1.x_min)::double precision) AND (t2."X" <= (t1.x_max)::double precision) AND (t2."Y" >= (t1.y_min)::double precision) AND (t2."Y" <= (t1.y_max)::double precision))
  ->  Seq Scan on gps_31 t1  (cost=0.00..3096.38 rows=161738 width=44) (actual time=4.612..442.935 rows=161738 loops=1)
  ->  Materialize  (cost=0.00..12562.25 rows=16726 width=20) (actual time=0.012..0.012 rows=0 loops=161738)
        ->  Seq Scan on adresses_31 t2  (cost=0.00..12478.62 rows=16726 width=20) (actual time=1504.082..1504.082 rows=0 loops=1)
              Filter: ((id_maille_200m)::text = '0'::text)
Total runtime: 3060.469 ms

Dernière modification par FFW_Rude (27/09/2012 10:57:43)

flo

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

On dirait qu'il manque un index sur tables_adresses.adresses_01.id
Ça permettrait déjà d'éviter la lecture séquentielle de tables_adresses pour ne ramener que 2924 lignes pour la première requête et... zéro pour l'autre.

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Tu veux dire sur id_maille_200m ? Je dois indexer ce champ ?

Franchement j'avoue ne pas du tout savoir comme lire le EXPLAIN.

Dernière modification par FFW_Rude (27/09/2012 13:32:07)

Marc Cousin

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Si le but c'est de savoir si un point se trouve dans un carré, le plus efficace, c'est peut-être de stocker ces «carrés» dans un type box, dans PostgreSQL: ça permet de passer par un index sur le type box. Un petit exemple pour montrer comment faire:

marc=# create table points (a point);
CREATE TABLE
marc=# create table carres (b box);
CREATE TABLE
marc=# insert into points values (point '(1,1)');
INSERT 0 1


marc=# insert into points values (point '(1,1)');
INSERT 0 1
marc=# insert into carres values (box '(1,1),(2,2)');
INSERT 0 1
marc=# insert into carres values (box '(1,2),(2,3)');
INSERT 0 1
marc=# insert into carres values (box '(3,2),(3,3)');
INSERT 0 1
marc=# insert into carres values (box '(4,1),(4,2)');
INSERT 0 1
marc=# insert into carres values (box '(-5,3),(12.4,6)');
INSERT 0 1
marc=# insert into carres values (box '(-1,1),(1,1)');
INSERT 0 1


marc=# create index idx_box on carres using gist (b);
CREATE INDEX



marc=# create index idx_box on carres using gist (b);
CREATE INDEX
marc=# explain select * from points join carres on (box (points.a,points.a) && carres.b);
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Nested Loop  (cost=10000000000.00..10000000532.01 rows=53 width=48)
   ->  Seq Scan on points  (cost=10000000000.00..10000000027.70 rows=1770 width=16)
   ->  Index Scan using idx_box on carres  (cost=0.00..0.27 rows=1 width=32)
         Index Cond: (box(points.a, points.a) && b)
(4 lignes)

L'opérateur && est un opérateur entre box, pour savoir si la première est contenue dans la seconde. Je fais donc une boîte réduite à une seule point, et un && avec la boîte de la seconde table.

---

Marc.

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Hum... Ca m'a l'air une bonne idée. Je ne connaissais pas le type box
Par contre à quoi correspond le type "point" ?

En gros est-ce que je dois faire une table genre ca :

CREATE TABLE carres (id (integer), x_box(box), y_box(box));

Et je dois l'interoger comme ca :

SELECT * FROM points JOIN carres on (box(points.X,points.X) && carres.box_x) AND (box(points.Y,points.Y) && carres.box_y)

J'ai bien compris ?

Dernière modification par FFW_Rude (27/09/2012 14:52:40)

Marc Cousin

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Non.

CREATE TABLE carres (id integer, ma_box box).

box est un type.

Si la table point contient une abscisse et une ordonnée, dans deux champs séparés, c'est un peu plus compliqué à écrire:

select * from points join carres on (box(point(points.x,points.y),point(points.x,points.y)) && carres.b);

En fait, on n'est pas vraiment obligés de modifier le type de la table contenant les carrés. Si elle contient des colonnes x1,y1,x2,y2 par exemple, contenant les deux extrémités du carré, on peut écrire comme ça:

Création d'un index:

create index idx_box on carres using gist (box(point(x1,y1),point(x2,y2)));

Requête utilisant l'index:

select * from points join carres on (box(point(points.x,points.y),point(points.x,points.y)) && box(point(carres.x1,carres.y1),point(carres.x2,carres.y2)));

Évidemment, ça devient moins lisible à chaque opération. Mais ça évite de changer le schéma

---

Marc.

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Salut,

J'ai le temps de tester et je galère un peu.

En gros j'ai créer une table avec un champ points(x,y)
Et j'ai créer une table avec un champ coordonnees box(x_min,x_max,y_min,y_max) et je n'arrive pas à comprendre comment marche l'intérogation de cette table.
Ou est-ce que je dois plutôt mettre box(x_min,y_min,x_max,y_max) ?

Est-ce que ma requette de selection est bonne aussi ?

select * from tables_tests.adresses_77 t1 join tables_tests.gps_77 t2 on (box ('2,39299','40,1') && t2.coordonnees);
=>
select * from tables_tests.adresses_77 t1 join tables_tests.gps_77 t2 on (box (t1.points,t1.points) && t2.coordonnees);

Et aussi un truc qui me turlupine.

Lorsque je fais la requête je dois mettre des virgules dans les donnés en "points" alors que dans la box ce sont des points et non des virgules. C'est normal ?
J'arrive à inserrer des données mais impossible de faire sortir a la recherche un résultat.

Dernière modification par FFW_Rude (28/09/2012 15:44:17)

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Ok j'ai réussi à m'en sortir. Je suis en train de tester les performences pour voir si c'est mieux En tout cas merci du coup de main. Que ca marche ou que ca marche pas j'aurais appris quelque chose

Marc Cousin

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Sinon, les docs sur tout ça, c'est là:

http://docs.postgresql.fr/9.2/datatype-geometric.html
http://docs.postgresql.fr/9.2/functions-geometry.html

---

Marc.

FFW_Rude

Membre

Re : 2 requettes similaires ont des traitements EXTREMEMENT différents

Franchement ca marche du tonnerre. Un très très grand merci.

Au passage, je précise que cet index en box prend pas mal de place

C'est vraiment super.

Merci beaucoup.