Discussion :

[mortimer.pw]

Bonjour,

Je travaille sur une base 8.1 sous Red-Hat 4.

Je fais un Delete d'un enregistrement au travers d'une interface et cette action prend environ 2 MINUTES.

Dans le fichier de log, je vois ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
2009-12-08 17:10:59 CSTLOG:  duration: 123897.495 ms  statement: SELECT * , CTID, OID FROM geo.Ptg_pers  where ctid = currtid2('"geo"."ptg_pers"', '(252119, 14)') and oid  = 25790957
2009-12-08 17:12:38 CSTLOG:  duration: 430.547 ms  statement: SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname, et.typname as elemtypname,
          cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,
          (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,
          EXISTS(SELECT 1 FROM  pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk
          FROM pg_attribute att
          JOIN pg_type ty ON ty.oid=atttypid
          JOIN pg_namespace tn ON tn.oid=ty.typnamespace
          JOIN pg_class cl ON cl.oid=attrelid
          JOIN pg_namespace na ON na.oid=cl.relnamespace
          LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
          LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND adnum=attnum
          LEFT OUTER JOIN pg_description des ON des.objoid=attrelid AND des.objsubid=attnum
          LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=attrelid AND refobjsubid=attnum
          LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
          LEFT OUTER JOIN pg_index pi ON pi.indrelid=attrelid AND indisprimary
         WHERE attrelid = 20964::oid
           AND attnum > 0
           AND attisdropped IS FALSE
         ORDER BY attnum

Est-ce que cela correspond à mon Delete ?

Y-a-t'il un problème ?

Merci de votre aide.

[CinePhil]

Je ne vois aucune requête DELETE dans ce log mais des requêtes SELECT :
La première qui prends effectivement 2 minutes utilise une fonction currtid2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT * , CTID, OID 
FROM geo.Ptg_pers  
WHERE ctid = currtid2('"geo"."ptg_pers"', '(252119, 14)') 
  AND oid  = 25790957

Dans la mesure où il n'y a qu'une table dans le FROM, je suppose que CTID et OID font partie de cette table ?
Pourquoi les répéter alors qu'il y a déjà un SELECT * ?
D'ailleurs SELECT * c'est pas top ! Il vaut mieux préciser les colonnes dont on a vraiment besoin.

Est-ce que les tables sont bien indexées ?
N'est-ce pas la fonction qui est consommatrice de ressources ?
Quel est le volume de données ?

La seconde requête, bien que beaucoup plus complexe, met moins d'une demie-seconde à s'exécuter.

[estofilo]

Tout le temps d'exécution doit être consommé dans la fonction currtid2, car le ctid est une pseudo-colonne qui référence une position physique d'un enregistrement dans une table et avec cette information le serveur peut aller chercher la ligne immédiatement.
Ce serait facile à vérifier en exécutant juste cette fonction dans psql.

[estofilo]

Sinon, à la question initiale: est-ce une commande DELETE?
A première vue non, puisqu'il s'agit d'un SELECT, mais on ne peut pas exclure que la fonction appelée dans le WHERE fasse un DELETE en effet de bord, il faut aller voir le code de cette fonction.

[mortimer.pw]

Bonjour à tous,

Merci pour les réponses.

Alors j'ai testé, sous PSQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select currtid2('"geo"."ptg_pers"', '(252119, 14)');

réponse trés rapide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * , CTID, OID FROM geo.Ptg_pers  where ctid = currtid2('"geo"."ptg_pers"', '(252119, 14)') and oid  = 25790957;

réponse trés lente

Cette requête n'existe pas dans le source de l'application (Visual C++) ; d'aprés le développeur, le driver pour PostgreSQL serait responsable de cette requête est de la fonction CURRTID2.

Avez-vous d'autres pistes ? ou y a t'il un moyen de trouver le source de cette fonction ?

[estofilo]

Il serait intéressant de voir le plan d'exécution. Sous psql ça s'obtient par:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
EXPLAIN ANALYZE
 SELECT * , CTID, OID FROM geo.Ptg_pers  WHERE ctid = currtid2('"geo"."ptg_pers"', '(252119, 14)') AND oid  = 25790957;

Pour le source de la fonction, faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

\df+ currtid2

toujours sous psql

[mortimer.pw]

Estofilo,

La commande EXPLAIN ANALYZE me retourne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
geo=# EXPLAIN ANALYZE SELECT * , CTID, OID FROM geo.Ptg_pers  WHERE ctid = currtid2('"geo"."ptg_pers"', '(252119, 14)') AND oid  = 25790957;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on ptg_pers  (cost=0.00..403115.50 rows=1 width=314) (actual time=92383.040..92383.040 rows=0 loops=1)
   Filter: ((ctid = currtid2('"geo"."ptg_pers"'::text, '(252119,14)'::tid)) AND (oid = 25790957::oid))
 Total runtime: 92383.130 ms
(3 rows)

Et pour le \df+ currtid2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
geo=# \df+ currtid2
                                                        List of functions
   Schema   |   Name   | Result data type | Argument data types |  Owner   | Language |    Source code    |      Description
------------+----------+------------------+---------------------+----------+----------+-------------------+-----------------------
 pg_catalog | currtid2 | tid              | text, tid           | postgres | internal | currtid_byrelname | latest tid of a tuple
(1 row)

J'espère que cela peut t'aider.

Merci encore pour ton aide.

[estofilo]

Apparemment il n'y a pas de statistiques sur cette table, ça se voit par le fait que l'estimation "rows=1" est incompatible avec la durée de la requête qui implique qu'il y a bcp de lignes.
Il faudrait faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ANALYZE geo.ptg_pers

pour permettre à l'exécuteur d'avoir les statistiques.
Eventuellement faire ANALYZE tout seul pour le faire sur toute la base.
D'autre part s'il n'y a pas d'index sur l'OID je pense qu'il faudrait en mettre un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX geo.index_oid_ptg_pers ON geo.ptg_pers(oid);

En principe ça devrait aller mieux après.

[mortimer.pw]

Estofilo,

Chaque dimanche, les tâches suivantes sont exécutées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
vacuumdb -a -f -z
reindexdb -a
reindexdb -s

N'est-ce pas suffisant ?

Le champ OID est interne PostgreSQL, faut-il vraiment l'indexer ?

[estofilo]

Ah désolé j'ai mal lu le plan. L'estimation rows=1 est post-filtre, donc en fait est relativement normale. La taille globale estimée de la table n'apparait pas dans l'explain, on ne peut pas savoir ici si elle est OK ou pas, mais si analyze est exécuté régulièrement ça doit être bon.

En revanche je reste sur l'idée qu'il faut indexer l'OID. C'est une colonne interne mais qui est devenue optionnelle y compris en 8.1, et qui ne permet pas spécialement d'accéder rapidement aux données, contrairement au ctid.

[mortimer.pw]

Estofilo,

La colonne OID étant une colonne interne PostgreSQL, est-il possible de créer un index sur cette colonne du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX ptg_pers_idx ON geo.ptg_pers(oid);

PostgreSQL n'a t'il pas déjà ce genre d'index ?

[estofilo]

PostgreSQL n'a t'il pas déjà ce genre d'index ?

Non. Voir http://wiki.postgresql.org/wiki/FAQ#What_is_an_OID.3F pour un (petit peu) plus d'informations.