postgres utilisation des index dans le requete IN

Sur fr.comp.applications.sgbd, WebShaker disait :

select max(e_date) FROM table WHERE idtable IN (xx, xx, xx, xx, ...)

byzarrement PostgreSQL n'utilise par l'index sur idtable.

Que dit EXPLAIN ?

- y a t il un moyen de faire comprendre a postgresql qu'il serait
plus judicieux d'utiliser un index.

Tu peux essayer de positionner enable_seqscan à false, pour tester.

--
Fufe n'est pas la vraie vie, c'est un espace de travail.
Il ne sert à rien de relativiser, c'est hors de propos.
-+- FF dans fufe - Neuneu=MC² -+-

A partir du moment ou il y a des valeurs discrètes multiples, aucun
index ne peut s'activer de manière bénéfique.

Vous feriez mieux de mettre ces valeurs dans une table temporaires
indexée et de faire des jointures dessus.

A +

WebShaker a écrit :

Salut
j'execute une requete

select max(e_date) FROM table WHERE idtable IN (xx, xx, xx, xx, ...)

byzarrement PostgreSQL n'utilise par l'index sur idtable.
alors il est vrai que j'ai 60 valeurs dans le IN.

le probleme c'est que la requete prend plus de temps que si j'en fais 60
a la suite en specifiant les idtable un par un.

Normal, même avec un UNION ALL cela sera plus vite car là il est
possible d'utiliser un idex sur chacune des 60 requêtes !

Une fois la requete executer une premiere fois (ca prend 250 secondes
quand meme) les executions suivantes ne prennent plus que 4 secondes.

Normal, le plan doit être en cache et les données aussi. Quel est la
volume de la BD et de la RAM ? Est du 32 ou 64 bits ?

Donc j'ai deux questions:
- y a t il un moyen de faire comprendre a postgresql qu'il serait plus
judicieux d'utiliser un index.

Strictement aucun intérêt ! Aucun gain !!!

- peut on supprimer les caches de postgres afin de faire des tests
réalistes dans des conditions réel d'une première utilisation.

C'est d'une grande stupidité : les bases de données fonctionne
essentiellement en cache (donc RAM) le disque n'est là que pour assurer
la persistance en attendant des techniques plus rapide comme les flash
disk.

merci
Etienne

Lisez les article que j'ai écrit sur l'optimisation dans mon site web !

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
Enseignant aux Arts & Métiers PACA et à L'ISEN Toulon - Var Technologies
*********************** http://www.sqlspot.com *************************

Le Wed, 08 Jul 2009 08:37:52 +0200, WebShaker a écrit:

select max(e_date) FROM table WHERE idtable IN (xx, xx, xx, xx, ...)

byzarrement PostgreSQL n'utilise par l'index sur idtable. alors il est

Pourquoi "bizarrement" ?
La présence d'un index n'est pas une solution magique, son usage n'est
pas nécessairement toujours un gain. PostgreSQL choisit la solution
qui lui parait meilleure au vu de votre requête et des données
statistiques connues sur vos tables.

Deux pistes en tout état de cause :
- mettre à jour PostgreSQL : quelle version avez-vous ? à chaque nouvelle
version il peut y avoir des améliorations de performance
D'ailleurs dans les vieilles versions un SELECT MAX() n'utilise jamais un
index alors qu'un
SELECT whatever ... ORDER BY whatever DESC LIMIT 1
(qui donne le même résultat que MAX() sauf en présence de NULL)
utilisera éventuellement un index
- avez-vous fait un ANALYZE récemment (ou utilisez-vous autovacuum ?) ?
(voire un vacuum ou un REINDEX selon les cas de figure où vous êtes,
vous ne donnez pas assez de renseignement, comme on vous a déjà répondu,
on ne peut pas "débugger" une requête SQL sans faire d'EXPLAIN/EXPLAIN
ANALYZE, un article récent d'ailleurs à ce sujet qui pourrait vous aider :
http://www.bortzmeyer.org/explain-postgresql.html
)

Une fois la requete executer une premiere fois (ca prend 250 secondes
quand meme) les executions suivantes ne prennent plus que 4 secondes.

Merci le cache :-)

Donc j'ai deux questions:
- y a t il un moyen de faire comprendre a postgresql qu'il serait plus
judicieux d'utiliser un index.

Comment savez-vous vous qu'il est plus judicieux d'utiliser un index ?
Vous savez exactement où sont stockés physiquement les enregistrements
qui vous intéressent sur le disque, et vous savez que la tête de lecture
de votre disque dur prendra moins de temps à lire l'index puis vos données
que lire toutes vos données séquentiellement ?
Dis autrement : êtes-vous sûr d'être plus intelligent que PostgreSQL ?

La réponse des développeurs PostgreSQL a toujours été la même : pas
de possibilité de spécifier des "pragmas" dans les requêtes pour forcer
le SGBDR à faire tel ou tel plan d'exécution (ce qui est possible dans
d'autres SGBDR) car ils (les développeurs) préfèrent, quand ils sont
confrontés à des mauvais choix du planificateur, travailler dessus
pour améliorer le planificateur pour tous les choix futurs, plutôt que
d'encourager les utilisateurs à des hacks ponctuels qui se retourneront
un jour ou l'autre contre eux.

- peut on supprimer les caches de postgres afin de faire des tests
réalistes dans des conditions réel d'une première utilisation.

Redémarrer PostgreSQL.
PostgreSQL n'a pas de cache spécifique, il utilise les fonctionnalités
de l'OS. Il faut donc redémarrer l'application pour les faire disparaître.

Et faire un vacuum des bases. Voire démarrer à chaque fois avec une
nouvelle installation du SGBDR de zero et importer des données, si vous
voulez avoir des jeux reproductibles.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le Fri, 10 Jul 2009 15:00:55 +0200, SQLpro a écrit:

A partir du moment ou il y a des valeurs discrètes multiples, aucun
index ne peut s'activer de manière bénéfique.

C'est bien péremptoire comme afirmation :

patrick@test83=> select version();

version
----------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2
(1 ligne)

patrick@test83=> create table w (i int4);
CREATE TABLE
patrick@test83=> create index wi on w(i);
CREATE INDEX
patrick@test83=> insert into w select generate_series(1,10000);
INSERT 0 10000
patrick@test83=> select count(*) from w;
count
-------
10000
(1 ligne)

patrick@test83=> analyze w;
ANALYZE
patrick@test83=> explain select * from w where i in (37,6982,587,9);
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on w (cost.03..29.30 rows=4 width=4)
Recheck Cond: (i = ANY ('{37,6982,587,9}'::integer[]))
-> Bitmap Index Scan on wi (cost=0.00..17.03 rows=4 width=0)
Index Cond: (i = ANY ('{37,6982,587,9}'::integer[]))
(4 lignes)

patrick@test83=> explain analyze select * from w where i in
(37,6982,587,9);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on w (cost.03..29.30 rows=4 width=4) (actual
time=0.172..0.178 rows=4 loops=1)
Recheck Cond: (i = ANY ('{37,6982,587,9}'::integer[]))
-> Bitmap Index Scan on wi (cost=0.00..17.03 rows=4 width=0) (actual
time=0.164..0.164 rows=4 loops=1)
Index Cond: (i = ANY ('{37,6982,587,9}'::integer[]))
Total runtime: 0.236 ms
(5 lignes)


patrick@test83=> drop index wi;
DROP INDEX
patrick@test83=> explain select * from w where i in (37,6982,587,9);
QUERY PLAN
----------------------------------------------------
Seq Scan on w (cost=0.00..190.00 rows=4 width=4)
Filter: (i = ANY ('{37,6982,587,9}'::integer[]))
(2 lignes)

patrick@test83=> explain analyze select * from w where i in
(37,6982,587,9);
QUERY
PLAN
---------------------------------------------------------------------------------------------
Seq Scan on w (cost=0.00..190.00 rows=4 width=4) (actual
time=0.024..3.522 rows=4 loops=1)
Filter: (i = ANY ('{37,6982,587,9}'::integer[]))
Total runtime: 3.553 ms
(3 lignes)


Le runtime est divisé par 10 dans cet exemple avec l'index.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

SQLpro a écrit :

A partir du moment ou il y a des valeurs discrètes multiples, aucun
index ne peut s'activer de manière bénéfique.

Vous feriez mieux de mettre ces valeurs dans une table temporaires
indexée et de faire des jointures dessus.

A +

WebShaker a écrit :

Salut
j'execute une requete

select max(e_date) FROM table WHERE idtable IN (xx, xx, xx, xx, ...)

byzarrement PostgreSQL n'utilise par l'index sur idtable.
alors il est vrai que j'ai 60 valeurs dans le IN.

le probleme c'est que la requete prend plus de temps que si j'en fais
60 a la suite en specifiant les idtable un par un.

Normal, même avec un UNION ALL cela sera plus vite car là il est
possible d'utiliser un idex sur chacune des 60 requêtes !

Une fois la requete executer une premiere fois (ca prend 250 secondes
quand meme) les executions suivantes ne prennent plus que 4 secondes.

Normal, le plan doit être en cache et les données aussi. Quel est la
volume de la BD et de la RAM ? Est du 32 ou 64 bits ?

Donc j'ai deux questions:
- y a t il un moyen de faire comprendre a postgresql qu'il serait plus
judicieux d'utiliser un index.

Strictement aucun intérêt ! Aucun gain !!!

- peut on supprimer les caches de postgres afin de faire des tests
réalistes dans des conditions réel d'une première utilisation.

C'est d'une grande stupidité : les bases de données fonctionne
essentiellement en cache (donc RAM) le disque n'est là que pour assurer
la persistance en attendant des techniques plus rapide comme les flash
disk.

merci
Etienne

Lisez les article que j'ai écrit sur l'optimisation dans mon site web !

A +

toujours aussi comique le Fred

SQLpro a écrit :

A partir du moment ou il y a des valeurs discrètes multiples, aucun
index ne peut s'activer de manière bénéfique.

Vous feriez mieux de mettre ces valeurs dans une table temporaires
indexée et de faire des jointures dessus.

Je vais essayer.

WebShaker a écrit :

Salut
j'execute une requete

select max(e_date) FROM table WHERE idtable IN (xx, xx, xx, xx, ...)

byzarrement PostgreSQL n'utilise par l'index sur idtable.
alors il est vrai que j'ai 60 valeurs dans le IN.

le probleme c'est que la requete prend plus de temps que si j'en fais
60 a la suite en specifiant les idtable un par un.

Normal, même avec un UNION ALL cela sera plus vite car là il est
possible d'utiliser un idex sur chacune des 60 requêtes !

bon je le note.

Une fois la requete executer une premiere fois (ca prend 250 secondes
quand meme) les executions suivantes ne prennent plus que 4 secondes.

Normal, le plan doit être en cache et les données aussi. Quel est la
volume de la BD et de la RAM ? Est du 32 ou 64 bits ?

En RAM je sais pas mais le dump fait 2.5 Go (ca t'aide peut etre)
Ce doit être en 32 bit.

Donc j'ai deux questions:
- y a t il un moyen de faire comprendre a postgresql qu'il serait plus
judicieux d'utiliser un index.

Strictement aucun intérêt ! Aucun gain !!!

Ah !

- peut on supprimer les caches de postgres afin de faire des tests
réalistes dans des conditions réel d'une première utilisation.

C'est d'une grande stupidité : les bases de données fonctionne
essentiellement en cache (donc RAM) le disque n'est là que pour assurer
la persistance en attendant des techniques plus rapide comme les flash
disk.

C'est juste pour tester...
Il se trouve que mon IN contient un ensemble d'identifiant de société.
Ca serait quand meme bien que je sache si lorsque les clients de mon
entreprise vont se connecter une première fois le matin, ca va prendre 4
secondes ou 4 minutes...

merci
Etienne

Lisez les article que j'ai écrit sur l'optimisation dans mon site web !

A +