Re: Index pour requêtes de non-existence en PostgreSQL

Le 19/05/2010 15:20, Stephane Bortzmeyer a écrit :
> Je suis sûr que, vue les compétences rassemblées ici, il y aura
> beaucoup de critiques sur la première partie (indexation de
> sous-chaînes avec PostgreSQL) et peut-être une solution pour la
> seconde (index pour les requêtes <>) :-)
>
> http://www.bortzmeyer.org/indexation-sous-chaine.html
>

Intéressant. Un petit ajout sur les classes d'opérateur :

Concernant la requête "SELECT person FROM PhonesPersons WHERE phone LIKE
'+33%';"

Elle ne passe pas par l'index car le planificateur estime qu'il va
récupérer 800153 lignes sur une table d'un million de lignes (ie 80%).
Dans ce cas, il serait plus coûteux d'utiliser l'index.

Concernant la classe d'opérateur, en effet, c'est important de
l'utiliser si on n'est pas en locale C (bref tout le temps). Ze proof.

b7=# EXPLAIN SELECT person FROM PhonesPersons WHERE phone LIKE '+13%';
QUERY PLAN
------------------------------------------------------------------
Seq Scan on phonespersons (cost=0.00..18064.00 rows=99 width=4)
Filter: (phone ~~ '+13%'::text)
(2 lignes)

Il estime le résultat à 99 lignes et il fait quand même un parcours
séquentiel d'un million de lignes. Un peu overkill... Créons l'index :

b7=# create index phone2_idx
on phonespersons (phone varchar_pattern_ops);
CREATE INDEX
b7=# analyze;
ANALYZE
b7=# EXPLAIN SELECT person FROM PhonesPersons WHERE phone LIKE '+33%';
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on phonespersons (cost=0.00..18064.00 rows=999901 width=4)
Filter: (phone ~~ '+33%'::text)
(2 lignes)

b7=# EXPLAIN SELECT person FROM PhonesPersons WHERE phone LIKE '+13%';
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on phonespersons (cost=3.65..81.23 rows=99 width=4)
Filter: (phone ~~ '+13%'::text)
-> Bitmap Index Scan on phone2_idx (cost=0.00..3.62 rows=27 width=0)
Index Cond: ((phone ~>=~ '+13'::text) AND (phone ~<~ '+14'::text))
(4 lignes)

Et là, c'est bon. Question perfs aussi vu que je suis passé de 285 ms à
5 ms.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com