est-ce une commande DELETE ?

Version imprimable

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:

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.

08/12/2009, 14h13
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:

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.
08/12/2009, 16h06
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.
08/12/2009, 16h10
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.
09/12/2009, 11h55
mortimer.pw

est-ce une commande DELETE ?

Bonjour à tous,

Merci pour les réponses.

Alors j'ai testé, sous PSQL :

Code:

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

réponse trés rapide

Code:

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 ?
09/12/2009, 12h10
estofilo
Il serait intéressant de voir le plan d'exécution. Sous psql ça s'obtient par:
Code:

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:

\df+ currtid2

toujours sous psql

est-ce une commande DELETE ?

Estofilo,

La commande EXPLAIN ANALYZE me retourne :

Code:

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:

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.

09/12/2009, 13h06
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:

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:

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

En principe ça devrait aller mieux après.
09/12/2009, 13h44
mortimer.pw

est-ce une commande DELETE ?
Estofilo,

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

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 ?
09/12/2009, 13h54
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.
09/12/2009, 15h27
mortimer.pw

est-ce une commande DELETE ?
Estofilo,

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

1 2 CREATE INDEX ptg_pers_idx ON geo.ptg_pers(oid);
PostgreSQL n'a t'il pas déjà ce genre d'index ?
09/12/2009, 16h24
estofilo

Citation:

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.