Forums PostgreSQL.fr

Jean-Marie

Membre

Requêtes par rapport à la date d'insertion des enregistrements

Bonjour

je souhaite faire des requêtes sur la date d'insertion des enregistrements
mais je m'aperçois que la requête ne se termine pas

si je prends l'exemple suivant basé sur le champ date de la donnée (et non pas date d'insertion) :

select date, x(coord), y(coord), insertdate from table where date between '2011-03-15 09:35:30' and '2011-03-15 9:35:40' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);

j'obtiens bien un résultat immédiat :

          date           |    x     |    y     |         insertdate         
-------------------------+----------+----------+----------------------------
2011-03-15 09:35:34.974 | -57.4305 |  -2.4284 | 2011-03-15 09:37:00.679191
2011-03-15 09:35:36.755 | -36.9402 | -21.7172 | 2011-03-15 09:37:02.429322
2011-03-15 09:35:37.27  | -37.2161 | -21.6957 | 2011-03-15 09:37:02.743891
2011-03-15 09:35:39.295 | -14.6501 |   0.0889 | 2011-03-15 09:37:04.958068
2011-03-15 09:35:39.366 | -14.6871 |   0.1062 | 2011-03-15 09:37:05.073541
(5 rows)

si maintenant j'essaie sur la date d'insertion :

select date, x(coord), y(coord) from table where insertdate between '2011-03-15 09:37:00.679191' and '2011-03-15 09:37:05.073541' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);

la requête bloque indéfiniment et je suis obligé de l'interrompre

quelqu'un sait-il comment je peux résoudre ce problème ?

merci d'avance

Jean-Marie

gleu

Administrateur

Re : Requêtes par rapport à la date d'insertion des enregistrements

Comme ça, sans plus d'infos, non.

Il serait intéressant de fournir la version de PostgreSQL, un EXPLAIN de la requête, ce que vous entendez par "bloque indéfiniment" (autrement dit, au bout de combien de temps l'avez-vous arrêté?), des infos sur la table (volumétrie, stats sur les données), etc.

---

Guillaume.

Jean-Marie

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

après analyse :
la requête a mis environ 11 minutes pour s'exécuter
il y a environ 34 000 000 enregistrements
la table a une taille de 15 GB

Jean-Marie

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

ah j'ai oublié la version :
PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu

gleu

Administrateur

Re : Requêtes par rapport à la date d'insertion des enregistrements

Manque l'EXPLAIN. Et comme la requête peut s'exécuter, un EXPLAIN ANALYZE serait beaucoup plus intéressant.

---

Guillaume.

Jean-Marie

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

désolé je n'avais pas capté

voici le résultat :

EXPLAIN ANALYZE select date, x(coord), y(coord) from ldb.stroke where insertdate between '2011-03-15 09:37:00.679191' and '2011-03-15 09:37:05.073541' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);
                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on stroke  (cost=0.00..1528695.10 rows=1 width=33) (actual time=681410.009..684291.580 rows=5 loops=1)
   Filter: ((insertdate >= '2011-03-15 09:37:00.679191'::timestamp without time zone) AND (insertdate <= '2011-03-15 09:37:05.073541'::timestamp without time zone) AND (coord && '0103000020E6100000010000000500000000000000008066C000000000008056C000000000008066
C0000000000080564000000000008066400000000000805640000000000080664000000000008056
C000000000008066C000000000008056C0'::geometry))
Total runtime: 684306.400 ms
(3 rows)

gleu

Administrateur

Re : Requêtes par rapport à la date d'insertion des enregistrements

Le planificateur estime qu'il va récupérer une seule ligne sur toute la table, ce qui laisse supposer qu'il choisirait un index s'il en avait un qui soit valable. On pourrait avoir la définition de la table ? par exemple, ce qu'affiche la commande \d stroke à partir de psql. Ou simplement la définition des index sur cette table ?

---

Guillaume.

Jean-Marie

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

Indexes:
    "stroke_coord_key" gist (coord)
    "stroke_date_key" btree (date)
    "stroke_num_key" btree (num)
Check constraints:
    "enforce_dims_coord" CHECK (ndims(coord) = 2)
    "enforce_geotype_coord" CHECK (geometrytype(coord) = 'POINT'::text OR coord IS NULL)
    "enforce_srid_coord" CHECK (srid(coord) = 4326)

Marc Cousin

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

Vous n'avez pas d'index sur insertdate.

---

Marc.

Jean-Marie

Membre

Re : Requêtes par rapport à la date d'insertion des enregistrements

ok, merci bien pour votre aide

Jean-Marie