Re: references et index

Bonjour,

Une question que je me repose à chaque fois que je recré des tables...

Faut-il créer un index sur un champ references ?

Par exemple

create table entete(
id_entete serial primary key
...
)
create table ligne(
ref_entete integer references entete
...
)

faut-il créer un index sur ligne(ref_entete) si on veut faire un select
* from ligne where ref_entete=... ?

--
William Dodé - http://flibuste.net

Bonjour,

Le Sun, 18 Jun 2006 17:00:23 +0000 (UTC)
William Dode <wilk-ml(at)flibuste(dot)net> a écrit:

> Bonjour,
>
> Une question que je me repose à chaque fois que je recré des
> tables...
>
> Faut-il créer un index sur un champ references ?
>
> Par exemple
>
> create table entete(
> id_entete serial primary key
> ...
> )
> create table ligne(
> ref_entete integer references entete
> ...
> )
>
> faut-il créer un index sur ligne(ref_entete) si on veut faire un
> select * from ligne where ref_entete=... ?
>
A mon avis NON : pas sur l'index "primary key", il doit être créé
automatiquement ...
Si quelqu'un à une autre vision sur ce problème ...

Sur les autres colonnes pouvant servir de critére de recherche OUI
et encore faut'il modifier possgresql.conf pour mettre
enable_seqscan = false, si non ça peut servir à pas grand chose,
car il semblerait que postgres fasse volontier des recherches
séquentielles (infos trouvées dans la liste "in english").
Mais ce peut-être différent d'une version majeure à une autre
et je ne suis pas un expert.

Amicalement,
--
Alain Lucari (Eurlix)

On 18-06-2006, Alain Lucari wrote:
> Bonjour,
>
> Le Sun, 18 Jun 2006 17:00:23 +0000 (UTC)
> William Dode <wilk-ml(at)flibuste(dot)net> a écrit:
>
>> Bonjour,
>>
>> Une question que je me repose à chaque fois que je recré des
>> tables...
>>
>> Faut-il créer un index sur un champ references ?
>>
>> Par exemple
>>
>> create table entete(
>> id_entete serial primary key
>> ...
>> )
>> create table ligne(
>> ref_entete integer references entete
>> ...
>> )
>>
>> faut-il créer un index sur ligne(ref_entete) si on veut faire un
>> select * from ligne where ref_entete=... ?
>>
> A mon avis NON : pas sur l'index "primary key", il doit être créé
> automatiquement ...

Je parlais du champ ref_entete de ligne, pas de id_entete qui a
effectivement un index automatiquement.

> Si quelqu'un à une autre vision sur ce problème ...
>
> Sur les autres colonnes pouvant servir de critére de recherche OUI
> et encore faut'il modifier possgresql.conf pour mettre
> enable_seqscan = false, si non ça peut servir à pas grand chose,
> car il semblerait que postgres fasse volontier des recherches
> séquentielles (infos trouvées dans la liste "in english").

Il me semble que ce n'est que dans le cas où la table est lu entièrement
ou qu'elle est de toute petite taille, afin d'éviter les déplacement de
bras pour le disque.

> Mais ce peut-être différent d'une version majeure à une autre
> et je ne suis pas un expert.
>
> Amicalement,

--
William Dodé - http://flibuste.net

On Sun, Jun 18, 2006 at 07:57:25PM +0200,
Alain Lucari <eurlix(dot)alain(at)free(dot)fr> wrote
a message of 46 lines which said:

> et encore faut'il modifier possgresql.conf pour mettre
> enable_seqscan = false,

Surtout pas ! Ce n'est pas par hasard qu'il est à true par défaut.

> car il semblerait que postgres fasse volontier des recherches
> séquentielles

Et à juste titre et c'est bien expliqué pourquoi par William Dodé.

D'une manière générale, pour tout ce qui est optimisation, si on n'est
pas un expert (un vrai), il vaut mieux ne pas se fier à son intuition
et *tester*. Mettez enable_seqscan = false si vous voulez mais testez
le résultat et voyez si c'est vraiment plus rapide (indication : cela
ne le sera pas).

On 18-06-2006, William Dode wrote:
> Bonjour,
>
> Une question que je me repose à chaque fois que je recré des tables...
>
> Faut-il créer un index sur un champ references ?

Je viens de voir sur une doc d'oracle qu'il est hautement préférable de
créer les index... Quelqu'un confirme que pour postgresql aussi ?

>
> Par exemple
>
> create table entete(
> id_entete serial primary key
> ...
> )
> create table ligne(
> ref_entete integer references entete
> ...
> )
>
> faut-il créer un index sur ligne(ref_entete) si on veut faire un select
> * from ligne where ref_entete=... ?
>

--
William Dodé - http://flibuste.net

Bonjour,

Créer un index sur les champs qui servent de critére de recherche
OUI.
Je ne sais pas ce que dit la doc Oracle à ce sujet, mais ça me semble bien
écrit dans la doc postgres.

Maintenant, je viens de faire un test sur une machine ayant deux tables
sensiblement équivalentes (nombre de lignes) voir plus loin et dans
lesquelles l'interro porte sur une zone, avec à priori avec une seule
ID, indexée dans les deux. Les résultats :
gesdil-# select * from dil where wp02 ='9789232039354';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using d_k1 on dil (cost=0.00..6.01 rows=1 width=530) (actual time=120.418..120.437 rows=1 loops=1)
Index Cond: (wp02 = '9789232039354'::bpchar)
Total runtime: 120.657 ms
(3 lignes)

gesdil=# explain analyze
gesdil-# select * from art where aean ='9789232039354';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Index Scan using a_k2 on art (cost=0.00..11.29 rows=3 width=306) (actual time=0.119..0.137 rows=1 loops=1)
Index Cond: (aean = '9789232039354'::bpchar)
Total runtime: 0.289 ms
(3 lignes)

gesdil=# select count(1) from dil;
count
--------
860039
(1 ligne)

gesdil=# select count(1) from art;
count
--------
794647
(1 ligne)
gesdil=# select count(1) from art where aean ='9789232039354';
count
-------
1
(1 ligne)

gesdil=# select count(1) from dil where wp02 ='9789232039354';
count
-------
1
(1 ligne)

Bon ! Si un expert peut me dire quelle pourrait être (juste les possibilités)
la raison de la différence :120.657 / 0.289
Je suis vraiment intéressé !

A part ça, c'est ingres qui est (je crois) l'origine commune d'Oracle et de
postgres et, même s'il y a pu y avoir beaucoup de developpements divergents
depuis le "fork" (ce qui enrichit l'ensemble et a enrichit le patron d'Oracle)

A part ça, aussi, si un "expert" pouvait me donner une réponse à une question que
j'ai posée il y a qq jours (3/6) sur un blocage de pg_dump, ce serait vraiment sympa.

Je ne suis pas un "expert". J'utilise postgres depuis plus de 5 ans, après avoir
lu beaucoup la doc et fait ce qu'il fallait pour créer un interface sous Linux/postgres
avec ... le langage dans lequel était écrits les programmes à convertir.

Je suis pleinement satisfait de postgres et je ne changerais oas, mais, parfois
comme dans le cas ci-dessus, j'aimerais comprendre, en supposant que mon cerveau
fatigué en soit capable ;-)
Ok, on est français : on a le droit de faire un peu d'humour, NON ?

Le Tue, 20 Jun 2006 19:17:40 +0000 (UTC)
William Dode <wilk-ml(at)flibuste(dot)net> a écrit:

> On 18-06-2006, William Dode wrote:
> > Bonjour,
> >
> > Une question que je me repose à chaque fois que je recré des
> > tables...
> >
> > Faut-il créer un index sur un champ references ?
>
> Je viens de voir sur une doc d'oracle qu'il est hautement préférable
> de créer les index... Quelqu'un confirme que pour postgresql aussi ?
>
> >
> > Par exemple
> >
> > create table entete(
> > id_entete serial primary key
> > ...
> > )
> > create table ligne(
> > ref_entete integer references entete
> > ...
> > )
> >
> > faut-il créer un index sur ligne(ref_entete) si on veut faire un
> > select * from ligne where ref_entete=... ?
> >
>
Ben oui, je crois ! voir ci-dessus ... et il n'y a que la foi qui sauve !

Cordialement,
--
Alain Lucari (Eurlix)