Forums PostgreSQL.fr

herve.lefebvre

Membre

Index GIN et égalité

Bonjour,

Je suis tombé sur un comportement étrange avec pg 12.4, je ne sais pas bien si cela relève d'un bug de l'optimiseur PG ou bien de quelque chose qui m'échappe...

J'ai une table avec 10 millions de lignes (propre en terme de VACUUM ANALYZE). Elle comporte un champ "EXTRA" de type JSONB dont l'attribut "celex" est indexé en GIN :

 "ix_ds_document_id_celex" gin ((extra ->> 'id_celex'::text) gin_trgm_ops) WHERE (extra ->> 'id_celex'::text) IS NOT NULL

Si je recherche une ligne en faisant une égalité, il fait un full scan de la table, alors que si je fais un LIKE il utilise bien l'index !?

dev=> explain select id from ds.document where extra->>'id_celex' = 'ABC';
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Gather  (cost=1000.00..2682290.02 rows=50435 width=8)
   Workers Planned: 2
   ->  Parallel Seq Scan on document  (cost=0.00..2676246.52 rows=21015 width=8)
         Filter: ((extra ->> 'id_celex'::text) = 'ABC'::text)
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)

dev=> explain select id from ds.document where extra->>'id_celex' like 'ABC';
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on document  (cost=634.87..180484.00 rows=50435 width=8)
   Recheck Cond: ((extra ->> 'id_celex'::text) ~~ 'ABC'::text)
   ->  Bitmap Index Scan on ix_ds_document_id_celex  (cost=0.00..622.26 rows=50435 width=0)
         Index Cond: ((extra ->> 'id_celex'::text) ~~ 'ABC'::text)
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

J'aimerais bien comprendre....

rjuju

Administrateur

Re : Index GIN et égalité

Bonjour,

Il s'agit du comportement souhaité, car pg_trgm supporte l'opérateur like mais pas l'égalité (vu que ce n'est pas le but) :

=# SELECT c.opcname,
op.amoplefttype::regtype,
op.amoprighttype::regtype,
op.amopopr::regoper
FROM pg_am am
JOIN pg_opfamily f ON f.opfmethod = am.oid
JOIN pg_opclass c ON c.opcfamily = f.oid
JOIN pg_amop op ON op.amopfamily = f.oid
WHERE c.opcname = 'gin_trgm_ops';
   opcname    | amoplefttype | amoprighttype |    amopopr
--------------+--------------+---------------+----------------
 gin_trgm_ops | text         | text          | public.%
 gin_trgm_ops | text         | text          | pg_catalog.~~
 gin_trgm_ops | text         | text          | pg_catalog.~~*
 gin_trgm_ops | text         | text          | pg_catalog.~
 gin_trgm_ops | text         | text          | pg_catalog.~*
 gin_trgm_ops | text         | text          | %>
 gin_trgm_ops | text         | text          | %>>
(7 rows)

---

Julien.
https://rjuju.github.io/

herve.lefebvre

Membre

Re : Index GIN et égalité

Merci de la réponse.

Je trouve cela étrange, puisque je ne vois pas bien la différence entre LIKE 'ABC' et = 'ABC' ...

rjuju

Administrateur

Re : Index GIN et égalité

Il n'y a pas de différence dans ce cas particulier effectivement.  J'imagine qu'il serait simple d'ajouter le support pour l'opérateur =, mais que cela n'a jamais été fait car pg_trgm est utilisé pour des recherches type '%pattern%', et les index créés ne sont du coup pas spécialement adapté à une recherche d'égalité (plus volumineux et moins efficaces qu'un simple btree).

---

Julien.
https://rjuju.github.io/

herve.lefebvre

Membre

Re : Index GIN et égalité

Je suis bien d'accord, mais les rares fois où on a besoin d'une recherche exacte, c'est ballot de faire un table scan alors qu'on a un index qui peut bien accélérer les choses (même si c'est pas aussi bien qu'un B-Tree dans ce cas particulier) ;-)

Mais bon, il suffit de le savoir, et on fait du LIKE 'ABC' au lieu de = 'ABC', c'est juste un peu contre-intuitif.

Dernière modification par herve.lefebvre (23/10/2020 18:23:02)

rjuju

Administrateur

Re : Index GIN et égalité

Pour information j'ai proposé de gérer ce cas pour gin_trgm_ops et gin_trgm_ops et l'idée est plutôt bienvenue : https://www.postgresql.org/message-id/f … .gmail.com .  J'ai également proposé un patch pour ça, avec un peu de chance cela sera intégré dans la version 14 de postgres.

---

Julien.
https://rjuju.github.io/

herve.lefebvre

Membre

Re : Index GIN et égalité

Pour information j'ai proposé de gérer ce cas pour gin_trgm_ops et gin_trgm_ops et l'idée est plutôt bienvenue : https://www.postgresql.org/message-id/f … .gmail.com .  J'ai également proposé un patch pour ça, avec un peu de chance cela sera intégré dans la version 14 de postgres.

Cool ;-)

Ça évitera probablement quelques "accidents"...

herve.lefebvre

Membre

Re : Index GIN et égalité

Bonjour,

Il y a quand même toujours quelque chose qui m'échappe avec ce GIN index et le calcul du plan d'exécution :

Là, pour moi tout est normal :

dev_ds=> explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1';
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on document  (cost=2530.38..183518.22 rows=50372 width=50)
   Recheck Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
   ->  Bitmap Index Scan on ix_ds_document_id_cellar  (cost=0.00..2517.79 rows=50372 width=0)
         Index Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

Pourquoi diable l'ajout de "limit 1" le dissuade d'utiliser l'index ? (enfin là encore je pourrais comprendre si on suppose que le like ramène un % de lignes assez élevé...)

dev_ds=> explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' limit 1;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..61.28 rows=1 width=50)
   ->  Seq Scan on document  (cost=0.00..3086581.90 rows=50372 width=50)
         Filter: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(3 rows)

Mais pourquoi l'ajout d'un joker (peut importe lequel et peut importe sa position)  va à nouveau faire choisir au planner l'usage de l'index ??

dev_ds=> explain select uri, date from ds.document where extra->>'id_cellar' like '%487d4f99-f65b-11ea-991b-01aa75ed71a1' limit 1;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit  (cost=2051.81..2055.77 rows=1 width=50)
   ->  Bitmap Heap Scan on document  (cost=2051.81..6038.96 rows=1007 width=50)
         Recheck Cond: ((extra ->> 'id_cellar'::text) ~~ '%487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
         ->  Bitmap Index Scan on ix_ds_document_id_cellar  (cost=0.00..2051.56 rows=1007 width=0)
               Index Cond: ((extra ->> 'id_cellar'::text) ~~ '%487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(5 rows)

gleu

Administrateur

Re : Index GIN et égalité

Lorsque vous ne comprenez pas pourquoi il utilise tel nœud plutôt qu'un autre, essayez de le désactiver. Dans le cas présente, faite un "SET enable_seqscan TO off;". Ça ne désactive pas complètement la possibilité d'avoir un parcours séquentiel, mais ça limite très fortement son utilisation.

De plus, quel est la taille de la table et de l'index ? (SELECT pg_table_size('ds.document'), pg_table_size('ix_ds_document_id_cellar'); ... non, il n'y a pas d'erreur dans la requête, c'est bien pg_table_size dans les deux cas).

---

Guillaume.

rjuju

Administrateur

Re : Index GIN et égalité

Bonne nouvelle, le patch a été committé aujourd'hui: https://git.postgresql.org/gitweb/?p=po … e5aa05a066

Le support de l'opérateur = sera donc dispnible avec la version 14.

---

Julien.
https://rjuju.github.io/

herve.lefebvre

Membre

Re : Index GIN et égalité

Super. Merci !