Forums PostgreSQL.fr

RM/AGILE

Membre

Lien entre ROW et les INDEXs

Bonjour
Nous passons un de nos logiciels sous PostGreSQL

Nous somme sous Windows et avons installé :
PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 32-bit
Nous vous remercions vivement de nous aider a résoudre le Pb rencontré.
Nous avons créé la table suivante :
CREATE TABLE ecri
(
  c_soci character(2) NOT NULL,
  c_type smallint NOT NULL,
  c_cpte character(10) NOT NULL,
  c_date integer NOT NULL,
  c_jnal character(4) NOT NULL,
  c_piece character(10) NOT NULL,
  c_noecrp integer NOT NULL,
  CONSTRAINT ecri_pkey PRIMARY KEY (c_soci, c_type, c_cpte, c_date, c_jnal, c_piece, c_noecrp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ecri OWNER TO postgres;
COMMENT ON TABLE ecri IS 'Ecritures)';

CREATE INDEX ecri_keyc_soecri
  ON ecri
  USING btree
  (c_soci COLLATE pg_catalog."default", c_type, c_cpte COLLATE pg_catalog."default", c_date DESC, c_jnal COLLATE pg_catalog."default", c_piece COLLATE pg_catalog."default", c_noecrp);

Elle contient 229203 enregistrements.
Elle n'est pas naturellement ordonnée sur aucune des 2 clefs.

.

CAS NUMERO 1: Utilisation de l'index ecri_pkey

Quand je selectionne l'une des dernieres pages avec
SELECT * FROM ecri WHERE (c_soci,c_type,c_cpte,c_date,c_jnal,c_piece,c_noecrp)>('01',1,'T00610',20071108,'VTT','F070005635',1)
                  ORDER BY c_soci,c_type,c_cpte,c_date,c_jnal,c_piece,c_noecrp LIMIT 100;
Durée totale d'exécution de la requête :62 ms.
100 lignes récupérées

Pour l'intégarlité de la table, avec des requetes tous les 100 rows, temps total=48 secondes

EXPLAIN ANALYZE DONNE :
"Limit  (cost=0.02..6.16 rows=1 width=287) (actual time=0.041..0.663 rows=100 loops=1)"
"  ->  Index Scan using ecri_pkey on ecri  (cost=0.02..6.16 rows=1 width=287) (actual time=0.035..0.347 rows=100 loops=1)"
"        Index Cond: (ROW(c_soci, c_type, c_cpte, c_date, c_jnal, c_piece, c_noecrp) > ROW('01'::bpchar, 1, 'T00610'::bpchar, 20071108, 'VTT'::bpchar, 'F070005635'::bpchar, 1))"
"Total runtime: 0.909 ms"

Comme ROW(Field1,...,Fieldn) operateur (ValField1,...,ValField1) est equivalent à (Je note opérateur par @ pour plus de commodités) :
Field1 @ ValField1 OR (Field1=ValField1 AND Field2 @ ValField2) OR (Field1=ValField1 AND Field2=ValField2 AND Field3 @ ValField3) OR .... et ainsi de suite

donc le meme select sans utiliser ROW donne :
SELECT * FROM ecri WHERE
c_soci>'01' OR (c_soci='01' AND c_type>1) OR
(c_soci='01' AND c_type=1 AND c_cpte>'T00610') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date>20071108) OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20071108 AND c_jnal>'VTT') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20071108 AND c_jnal='VTT' AND c_piece>'F070005635') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20071108 AND c_jnal='VTT' AND c_piece='F070005635' AND c_noecrp>1)
ORDER BY c_soci,c_type,c_cpte,c_date,c_jnal,c_piece,c_noecrp LIMIT 100;

Durée totale d'exécution de la requête :859 ms.
100 lignes récupérées.
Pour l'intégarlité de la table, avec des requetes tous les 100 rows, temps total=10 Minutes

EXPLAIN ANALYZE DONNE :
"Limit  (cost=0.00..3252.04 rows=100 width=287) (actual time=796.362..797.099 rows=100 loops=1)"
"  ->  Index Scan using ecri_pkey on ecri  (cost=0.00..44780.57 rows=1377 width=287) (actual time=796.356..796.777 rows=100 loops=1)"
"        Filter: ((c_soci > '01'::bpchar) OR ((c_soci = '01'::bpchar) AND (c_type > 1)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte > 'T00610'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date > 20071108)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20071108) AND (c_jnal > 'VTT'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20071108) AND (c_jnal = 'VTT'::bpchar) AND (c_piece > 'F070005635'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20071108) AND (c_jnal = 'VTT'::bpchar) AND (c_piece = 'F070005635'::bpchar) AND (c_noecrp > 1)))"
"        Rows Removed by Filter: 227900"
"Total runtime: 797.353 ms"

J'en tire donc la conclusion que ROW permet non seulement de selectionner un index mais également de positionner le StartKeyValue de cet index...
POUVEZ VOUS CONFIRMER ?

.

CAS NUMERO 2: Utilisation de l'index ecri_keyc_soecri

l'une des dernieres pages est accessible via :
SELECT * FROM ecri WHERE
c_soci>'01' OR (c_soci='01' AND c_type>1) OR
(c_soci='01' AND c_type=1 AND c_cpte>'T00610') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date<20070710) OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20070710 AND c_jnal>'B501') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20070710 AND c_jnal='B501' AND c_piece>'B070001636') OR
(c_soci='01' AND c_type=1 AND c_cpte='T00610' AND c_date=20070710 AND c_jnal='B501' AND c_piece='B070001636' AND c_noecrp>3)
ORDER BY c_soci,c_type,c_cpte,c_date DESC,c_jnal,c_piece,c_noecrp LIMIT 100;

Durée totale d'exécution de la requête :922 ms.
100 lignes récupérées.
Pour l'intégarlité de la table, avec des requetes tout les 100 rows, temps total=14 Minutes

Mais comme vu précédamment, comme on n'utilise pas ROW, l'index ne positionne pas le StartKeyValue d'ou un temps de réponse désastreux ...

EXPLAIN ANALYZE DONNE :
"Limit  (cost=0.00..2834.39 rows=100 width=287) (actual time=765.561..766.288 rows=100 loops=1)"
"  ->  Index Scan using ecri_keyc_soecri on ecri  (cost=0.00..39936.52 rows=1409 width=287) (actual time=765.554..765.960 rows=100 loops=1)"
"        Filter: ((c_soci > '01'::bpchar) OR ((c_soci = '01'::bpchar) AND (c_type > 1)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte > 'T00610'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date < 20070710)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20070710) AND (c_jnal > 'B501'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20070710) AND (c_jnal = 'B501'::bpchar) AND (c_piece > 'B070001636'::bpchar)) OR ((c_soci = '01'::bpchar) AND (c_type = 1) AND (c_cpte = 'T00610'::bpchar) AND (c_date = 20070710) AND (c_jnal = 'B501'::bpchar) AND (c_piece = 'B070001636'::bpchar) AND (c_noecrp > 3)))"
"        Rows Removed by Filter: 227900"
"Total runtime: 766.546 ms"

Et la gros probleme, le ROW ne permet de comparer que des elements dans le meme order (pas de desc)
Il y a bien une solution :

SELECT * FROM ecri WHERE (c_soci,c_type,c_cpte,20070710,c_jnal,c_piece,c_noecrp)>('01',1,'B501',c_date,'VTT','B070001636',3)
                  ORDER BY c_soci,c_type,c_cpte,c_date DESC,c_jnal,c_piece,c_noecrp LIMIT 100;

cela permet d'inverser la comparaison sur c_date ....

Durée totale d'exécution de la requête :203 ms.
100 lignes récupérées.
Pour l'intégarlité de la table, avec des requetes tous les 100 rows, temps total=10 Minutes

EXPLAIN ANALYZE DONNE :
"Limit  (cost=11859.09..11859.10 rows=1 width=287) (actual time=283.925..284.395 rows=100 loops=1)"
"  ->  Sort  (cost=11859.09..11859.10 rows=1 width=287) (actual time=283.919..284.082 rows=100 loops=1)"
"        Sort Key: c_soci, c_type, c_cpte, c_date, c_jnal, c_piece, c_noecrp"
"        Sort Method: top-N heapsort  Memory: 42kB"
"        ->  Seq Scan on ecri  (cost=0.00..11859.08 rows=1 width=287) (actual time=0.098..191.763 rows=39402 loops=1)"
"              Filter: (ROW(c_soci, c_type, c_cpte, 20070710, c_jnal, c_piece, c_noecrp) > ROW('01'::bpchar, 1, 'B501'::bpchar, c_date, 'VTT'::bpchar, 'B070001636'::bpchar, 3))"
"              Rows Removed by Filter: 189801"
"Total runtime: 284.667 ms"

On constate que l'on n'utilise pas d'index ...

Question en conclusion :
Comment formuler une requete du type ROW(Field1,Field2 DESC) operator ROW(ValueField1,ValueField2) en ayant pour objetif d'utiliser un index(Field1,Field2 DESC) ?

Merci a tous
Mario RODRIGUES

RM/AGILE

Membre

Re : Lien entre ROW et les INDEXs

Il semble que je n'inspire pas beaucoup de monde ...

J'ai néanmoins trouvé une solution (qui n'est pas élégante a mon gout, mais elle fonctionne ....)

SELECT * FROM ecri WHERE (c_soci,c_type,c_cpte,20070710,c_jnal,c_piece,c_noecrp)>('01',1,'B501',c_date,'VTT','B070001636',3)
                  ORDER BY c_soci,c_type,c_cpte,c_date DESC,c_jnal,c_piece,c_noecrp LIMIT 100;

qui permet de travailler sur le champ c_date en en mode desc A ETE réécrit de la manière suivante (valable uniquement si le champ est du type numérique)

SELECT * FROM ecri WHERE (c_soci,c_type,c_cpte,-c_date,c_jnal,c_piece,c_noecrp)>('01',1,'B501',-20070710,'VTT','B070001636',3)
                  ORDER BY c_soci,c_type,c_cpte,-c_date,c_jnal,c_piece,c_noecrp LIMIT 100;

vous remarquerez que j'ai mis (....,-c_date...)>'....,-20070710...) et aussi dans le ORDER BY (obligatoire également, sinon il ne sait pas comment et trié le résultat et refait le tri !!! donc 250 ms de plus pour 100 rows )
et pour que PostgreSQL utilise un index, j'ai déclaré cet index de la manière suivante:

CREATE INDEX ecri_keyc_soecri  ON ecri  USING btree  (c_soci , c_type, c_cpte, -c_date, c_jnal, c_piece, c_noecrp);

et la, parfait , j'obtiens a peux près le même temps (10 ms de plus) que lors de l'utilisation du pkey !

Bon ok pour les champs numériques, mais quid des champs char,varchar,texte,date .....
pour les champs cha,varchar, texte, il faut écrire une fonction (chose faite et ça fonctionne comme pour les numériques).

JE N'AI PAR CONTRE AUCUNE SOLUTION POUR LES CHAMPS DATE

J'AI AVEC CETTE MÉTHODE RÉSOLU LE PB DE L'AFFICHAGE DES TABLES EN MODE PAGINATION (avec des index dont les champs ne sont pas tous dans le même order, bien sur !)

Ce qui serai super c'est que PostgreSQL autorise le descripteur DESC dans le ROW (plus propre et plus élégant ....)

QU'EN PENSEZ VOUS ?

Merci a tous