planer en delire !!!

salut
comme je l'avais deja dit bonne idee de faire une liste en francais

je commence tout de suite avec une question de _planer_ et postgresql
7.3.3 (debian)
j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
int)(>5000000 de lignes), index unique sur (serie) + index unique sur
(a,b,c,d,e,f) et index sur (a)
lorsque je fais explain select * from matable where a=12; le planer ne
veut pas utiliser les index pourtant il y a un index actif pour la
colonne a, par contre si je fais explain select * from matable where
a=12 and b=14; la sa marche il utilise l'index (a,b,c,d,e,f).avant
j'utilisais postgres 7.2.1 et cela fanctionnais tres bien.
je sais qu'il y a une discution sur ce sujet sur la liste anglo.
(pourtant sans solution).
j'ai du forcer l'utilisation des index dans le fichier de config pour
avoir le resultat voulu
si qlq'un a une autre solution

daniel

Daniel,

Le Mercredi 27 Août 2003 16:03, Daniel a écrit :
> je commence tout de suite avec une question de _planer_ et postgresql
> 7.3.3 (debian)
> j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
> int)(>5000000 de lignes), index unique sur (serie) + index unique sur
> (a,b,c,d,e,f) et index sur (a)
> lorsque je fais explain select * from matable where a=12; le planer ne
> veut pas utiliser les index pourtant il y a un index actif pour la
> colonne a, par contre si je fais explain select * from matable where
> a=12 and b=14; la sa marche il utilise l'index (a,b,c,d,e,f).avant
> j'utilisais postgres 7.2.1 et cela fanctionnais tres bien.

Elle represente quel volume de données ta table, et quelle disparité dans les
données de la colonne a ?
Dans certain cas il est plus intéressant de ne pas utiliser l'index pour le
planner ... L'utilisation d'index n'est pas forcément la meilleure solution
pour lui ...

Peux tu nous faire un explain de cette requête ?

Cordialement,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Hervé Piedvache wrote:

>Daniel,
>
>Le Mercredi 27 Août 2003 16:03, Daniel a écrit :
>
>
>>je commence tout de suite avec une question de _planer_ et postgresql
>>7.3.3 (debian)
>>j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
>>int)(>5000000 de lignes), index unique sur (serie) + index unique sur
>>(a,b,c,d,e,f) et index sur (a)
>>lorsque je fais explain select * from matable where a=12; le planer ne
>>veut pas utiliser les index pourtant il y a un index actif pour la
>>colonne a, par contre si je fais explain select * from matable where
>>a=12 and b=14; la sa marche il utilise l'index (a,b,c,d,e,f).avant
>>j'utilisais postgres 7.2.1 et cela fanctionnais tres bien.
>>
>>
>
>Elle represente quel volume de données ta table, et quelle disparité dans les
>données de la colonne a ?
>Dans certain cas il est plus intéressant de ne pas utiliser l'index pour le
>planner ... L'utilisation d'index n'est pas forcément la meilleure solution
>pour lui ...
>
>Peux tu nous faire un explain de cette requête ?
>
>Cordialement,
>
>
voila, voila

la table a +de 5000000 de rows

set enable_seqscan=true;
SET
explain analyze select * from combinaison_bis where a=11;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on combinaison_bis (cost=0.00..104144.32 rows=65198 width=28)
(actual time=12652.61..31637.17 rows=169911 loops=1)
Filter: (a = 11)
Total runtime: 31749.48 msec
(3 rows)

set enable_seqscan=false;
SET
daniel=# explain analyze select * from combinaison_bis where a=11;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using a_combinaison_bis_key on combinaison_bis
(cost=0.00..211305.74 rows=65198 width=28) (actual time=111.80..2872.23
rows=169911 loops=1)
Index Cond: (a = 11)
Total runtime: 2976.17 msec
(3 rows)

daniel

>>> lorsque je fais explain select * from matable where a=12; le planer
>>> ne

Et en faisant: select * from matable where a=12::int ou select * from
matable where a='12' ?

Lorsque l'on ne met rien, le planer fait une hypothèse sur le type de
données et si ça ne matche pas celui de la colonne, il ne prend pas
l'index. Faire un cast explicite ou utiliser des apostrophes corrige
cela.

A+

---------------
Francois

Home page: http://www.monpetitcoin.com/

"Les cons gagnent toujours. Ils sont trop." - Cavanna

Le Wed, 27 Aug 2003 16:03:51 +0200
Daniel <daniel(at)12move(dot)be> me disait que :

> salut
> comme je l'avais deja dit bonne idee de faire une liste en francais
>
> je commence tout de suite avec une question de _planer_ et postgresql
> 7.3.3 (debian)
> j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
> int)(>5000000 de lignes), index unique sur (serie) + index unique sur
> (a,b,c,d,e,f) et index sur (a)
> lorsque je fais explain select * from matable where a=12; le planer ne
> veut pas utiliser les index pourtant il y a un index actif pour la
> colonne a, par contre si je fais explain select * from matable where
> a=12 and b=14; la sa marche il utilise l'index (a,b,c,d,e,f).avant
> j'utilisais postgres 7.2.1 et cela fanctionnais tres bien.
> je sais qu'il y a une discution sur ce sujet sur la liste anglo.
> (pourtant sans solution).
> j'ai du forcer l'utilisation des index dans le fichier de config pour
> avoir le resultat voulu
> si qlq'un a une autre solution

Salut Daniel,

En fait, d'aprés ce que j'ai cru comprendre, le planner utilise les
statistiques des tables et index afin de choisir la manière dont il va
construire sa requête. Dans le cas d'une table où il y a peu de lignes dans ta
table, le planner choisira à coup sûr (si tu as un nombre de colonnes
limitées) de taper directement dans la table plutôt que de charger l'index,
puis la table. Lorsque tu modifies le fichier de configuration de PG afin de
l'obliger à utiliser les index, tu restreint le planner dans son efficacité
(AMHA) en l'obligeant à faire deux parcours (Index+table).
Si ta table est pleine de lignes (bien peuplée donc), et que ton
explain ne change pas il y a fort à parier que les stats ne soient pas à jour.
Il te sera donc nécessaire de faire un VACUUM ANALYZE sur la table ou un
REINDEX sur les index qui t'intéressent ou carrément sur la table.

Voilà, c'est ce que j'ai pu observer lors de mes diverses expériences
avec PG et que certaines docs piochées de droite et de gauche, on confirmé.

A+

--
Jean-Christophe ARNU

Francois Suter wrote:

>>>> lorsque je fais explain select * from matable where a=12; le planer ne
>>>
>
> Et en faisant: select * from matable where a=12::int ou select * from
> matable where a='12' ?
>
> Lorsque l'on ne met rien, le planer fait une hypothèse sur le type de
> données et si ça ne matche pas celui de la colonne, il ne prend pas
> l'index. Faire un cast explicite ou utiliser des apostrophes corrige
> cela.
>
> A+
>
> ---------------
> Francois
>
> Home page: http://www.monpetitcoin.com/
>
> "Les cons gagnent toujours. Ils sont trop." - Cavanna
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
cela ne marche pas, avant j'avais une version 7.2.1, et cela fonction.
impec.
depuis que je suis passe a la version 7.3.3 !!!!
ma solution pour le moment _"set enable_seqscan=true;"_
daniel
ps: pourquoi l'adresse de reply donne l'adresse de l'expediteur et pas
l'adresse de la liste ?

Jean-Christophe ARNU (JX) wrote:

>Le Wed, 27 Aug 2003 16:03:51 +0200
>Daniel <daniel(at)12move(dot)be> me disait que :
>
>
>
>>salut
>>comme je l'avais deja dit bonne idee de faire une liste en francais
>>
>>je commence tout de suite avec une question de _planer_ et postgresql
>>7.3.3 (debian)
>>j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
>>int)(>5000000 de lignes), index unique sur (serie) + index unique sur
>>(a,b,c,d,e,f) et index sur (a)
>>lorsque je fais explain select * from matable where a=12; le planer ne
>>veut pas utiliser les index pourtant il y a un index actif pour la
>>colonne a, par contre si je fais explain select * from matable where
>>a=12 and b=14; la sa marche il utilise l'index (a,b,c,d,e,f).avant
>>j'utilisais postgres 7.2.1 et cela fanctionnais tres bien.
>>je sais qu'il y a une discution sur ce sujet sur la liste anglo.
>>(pourtant sans solution).
>>j'ai du forcer l'utilisation des index dans le fichier de config pour
>>avoir le resultat voulu
>>si qlq'un a une autre solution
>>
>>
>
> Salut Daniel,
>
> En fait, d'aprés ce que j'ai cru comprendre, le planner utilise les
>statistiques des tables et index afin de choisir la manière dont il va
>construire sa requête. Dans le cas d'une table où il y a peu de lignes dans ta
>table, le planner choisira à coup sûr (si tu as un nombre de colonnes
>limitées) de taper directement dans la table plutôt que de charger l'index,
>puis la table. Lorsque tu modifies le fichier de configuration de PG afin de
>l'obliger à utiliser les index, tu restreint le planner dans son efficacité
>(AMHA) en l'obligeant à faire deux parcours (Index+table).
> Si ta table est pleine de lignes (bien peuplée donc), et que ton
>explain ne change pas il y a fort à parier que les stats ne soient pas à jour.
>Il te sera donc nécessaire de faire un VACUUM ANALYZE sur la table ou un
>REINDEX sur les index qui t'intéressent ou carrément sur la table.
>
> Voilà, c'est ce que j'ai pu observer lors de mes diverses expériences
>avec PG et que certaines docs piochées de droite et de gauche, on confirmé.
>
> A+
>
>
>
>
>
salut Jean-Christophe

j'ai fais vacuum analyze et aussi reindex avant de me tourner vers la liste
(de plus suite a un explain il est concluant d'utiliser l'index et pas
un scan)

la table a +de 5000000 de rows

set enable_seqscan=true;
SET
explain analyze select * from combinaison_bis where a=11;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------

Seq Scan on combinaison_bis (cost=0.00..104144.32 rows=65198 width=28)
(actual time=12652.61..31637.17 rows=169911 loops=1)
Filter: (a = 11)
Total runtime: 31749.48 msec
(3 rows)

set enable_seqscan=false;
SET
daniel=# explain analyze select * from combinaison_bis where a=11;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------

Index Scan using a_combinaison_bis_key on combinaison_bis
(cost=0.00..211305.74 rows=65198 width=28) (actual time=111.80..2872.23
rows=169911 loops=1)
Index Cond: (a = 11)
Total runtime: 2976.17 msec
(3 rows)

daniel

ps: pourquoi l'adresse de reply est l'adresse de l'exp. et non l'adresse
de la liste ?

> Je lis ça (le coup du cast explicite) sur pgsql-general et surtout sur
> pgsql-performance depuis un moment, mais je me suis toujours posé la
> question
> suivante: le plannificateur ne prend-t'il pas par défaut le type de la
> colonne (a dans l'exemple) ? je suppose que non, sinon je ne vois pas
> l'intérêt de la conversion explicite. Mais du coup, je ne comprends pas
> pourquoi le plannificateur ne prendrait pas par défaut le type de la
> colonne
> forcément typée.

J'ai moi-même eu ce problème et avait posé la question sur la liste
générale. Je ne me souviens plus exactement de la réponse, mais c'est
un problème qui survient avec les champs numériques: quand le type
n'est pas spécifié explicitement dans un clause WHERE, le planer fait
une hypothèse quant à son type ("numeric", si je me souviens bien). Si
la colonne n'est pas justement "numeric" (elle est "int" ou "bigint",
peu importe), l'index n'est pas utilisé et on revient à un scan
séquentiel. Je suppose que la question qui se pose est de savoir si le
défaut est vraiment bien choisi. Il faudrait chercher dans les archives
pour voir ce qui a déjà été dit à ce sujet; je viens d'essayer, mais
elles sont plantées :-(

---------------
Francois

Home page: http://www.monpetitcoin.com/

"Les cons gagnent toujours. Ils sont trop." - Cavanna

Le Jeudi 28 Août 2003 14:34, Francois Suter a écrit :
> >>> lorsque je fais explain select * from matable where a=12; le planer
> >>> ne
>
> Et en faisant: select * from matable where a=12::int ou select * from
> matable where a='12' ?
>
> Lorsque l'on ne met rien, le planer fait une hypothèse sur le type de
> données et si ça ne matche pas celui de la colonne, il ne prend pas
> l'index. Faire un cast explicite ou utiliser des apostrophes corrige
> cela.
>
Je lis ça (le coup du cast explicite) sur pgsql-general et surtout sur
pgsql-performance depuis un moment, mais je me suis toujours posé la question
suivante: le plannificateur ne prend-t'il pas par défaut le type de la
colonne (a dans l'exemple) ? je suppose que non, sinon je ne vois pas
l'intérêt de la conversion explicite. Mais du coup, je ne comprends pas
pourquoi le plannificateur ne prendrait pas par défaut le type de la colonne
forcément typée.

--
Guillaume <!-- http://absfr.tuxfamily.org/ -->.

Daniel,

Le Jeudi 28 Août 2003 16:58, Jean-Christophe ARNU (JX) a écrit :
>
> En fait, d'aprés ce que j'ai cru comprendre, le planner utilise les
> statistiques des tables et index afin de choisir la manière dont il va
> construire sa requête. Dans le cas d'une table où il y a peu de lignes dans
> ta table, le planner choisira à coup sûr (si tu as un nombre de colonnes
> limitées) de taper directement dans la table plutôt que de charger l'index,
> puis la table. Lorsque tu modifies le fichier de configuration de PG afin
> de l'obliger à utiliser les index, tu restreint le planner dans son
> efficacité (AMHA) en l'obligeant à faire deux parcours (Index+table).
> Si ta table est pleine de lignes (bien peuplée donc), et que ton
> explain ne change pas il y a fort à parier que les stats ne soient pas à
> jour. Il te sera donc nécessaire de faire un VACUUM ANALYZE sur la table ou
> un REINDEX sur les index qui t'intéressent ou carrément sur la table.

Tout à fait d'accord ...

Vacuum full analyze peut aussi aider en nettoyant les index, et les données
effacées définitivement, et donc gagner en volume sur la table et en rapidité
d'accès.

Maintenant pour en revenir au problème même de Daniel ... si dans le mode
normal le planner ne te prends pas les index ... tu peux essayer de faire un

ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column SET STATISTICS integer;

où tu donnes un plus large espace de données pour les statistiques par exemple
100 au lieu des 10 de base ..., la valeur dépend en fait du volume de
disparité qui règne dans tes données ... et puis tu refais un VACUUM ANALYZE
dessus ... ainsi le planner va pouvoir mieux estimer la disparité des
données.

Cordialement,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

On Wed, Aug 27, 2003 at 04:03:51PM +0200, Daniel wrote:
...
> j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
> int)(>5000000 de lignes), index unique sur (serie) + index unique sur
> (a,b,c,d,e,f) et index sur (a)
..
Repondant a cote: il me semble que l'index sur (a) n'est pas necessaire,
celui sur (a,b,c,d,e,f) devrait etre utilise.. (C'est de memoire)

Patrick

Guillaume LELARGE writes:

> le plannificateur ne prend-t'il pas par défaut le type de la colonne (a
> dans l'exemple) ?

Non, le type d'une constante est décidé dans le parser, avant le
plannificateur. Mais dans ce cas, c'est sans intérêt, parce que la
constante 12 et la colonne "a" ont le même type (integer; voir
http://www.postgresql.org/docs/7.3/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS).

--
Peter Eisentraut peter_e(at)gmx(dot)net

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
Patrick Welche wrote:<br>
<blockquote type="cite"
cite="mid20030828193356(dot)G8899(at)quartz(dot)newn(dot)cam(dot)ac(dot)uk">
<pre wrap="">On Wed, Aug 27, 2003 at 04:03:51PM +0200, Daniel wrote:
...
</pre>
<blockquote type="cite">
<pre wrap="">j'ai une table matable(serie serial,a int,b, int c int,d int,e int,f
int)(&gt;5000000 de lignes), index unique sur (serie) + index unique sur
(a,b,c,d,e,f) et index sur (a)
</pre>
</blockquote>
<pre wrap=""><!---->..
Repondant a cote: il me semble que l'index sur (a) n'est pas necessaire,
celui sur (a,b,c,d,e,f) devrait etre utilise.. (C'est de memoire)

Patrick

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

</pre>
</blockquote>
je sais avec la version 7.2.1 que j'avais avant , cela fonctionnais comme
sa, il utilisait l'index (a,b,c,d,e,f).<br>
un autre probleme si je fais un index sur une autre colonne (e) et que je
fais un explain analyze select * from combinaison_bis where e=38, si je force
l'utilisation d'un index les resultats sont tres mauvais !!!!!<br>
set enable_seqscan=true;<br>
SET<br>
explain analyze select * from combinaison_bis where e=38;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN<br>
---------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Seq Scan on combinaison_bis&nbsp; (cost=0.00..104144.40 rows=278027 width=28)
(actual time=0.09..21532.63 rows=264180 loops=1)<br>
&nbsp;&nbsp; Filter: (e = 38)<br>
<u>&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&nbsp; Total runtime: 21720.69
msec &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;</u><br>
(3 rows)<br>
<br>
set enable_seqscan=false;<br>
SET<br>
&nbsp;explain analyze select * from combinaison_bis where e=38;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY<br>
PLAN<br>
------------------------------------------------------------------------------<br>
----------------------------------------------------------------------------<br>
&nbsp;Index Scan using e_combinaison_bis_key on combinaison_bis&nbsp; (cost=0.00..8880.05
rows=278027 width=28) (actual time=78.33..159628.99 rows=264180 loops=1)<br>
&nbsp;&nbsp; Index Cond: (e = 38)<br>
<u>&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&nbsp; Total runtime:
159843.76 msec &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;</u><br>
(3 rows)<br>
<br>
</body>
</html>