Vous n'êtes pas identifié(e).
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 NULLSi 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....
Hors ligne
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/
Hors ligne
Merci de la réponse.
Je trouve cela étrange, puisque je ne vois pas bien la différence entre LIKE 'ABC' et = 'ABC' ...
Hors ligne
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/
Hors ligne
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)
Hors ligne
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/
Hors ligne
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"...
Hors ligne
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)Hors ligne
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.
Hors ligne
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/
Hors ligne
Super. Merci !
Hors ligne
LIMIT 1 impose un tri. Or les index GIN ne sont pas "triables"
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Certes, les index GIN ne sont pas triables mais "LIMIT" n'impose pas de tri.
Guillaume.
Hors ligne
D'ailleurs, où voyez-vous un tri dans les plans montrés par M. Lefebvre ?
Thomas Reiss
Hors ligne
LIMIT 1 impose un tri. Or les index GIN ne sont pas "triables"
Euh non.
aegir=# create table toto(id integer, lib text);
CREATE TABLE
aegir=# insert into toto values (1,'z');
INSERT 0 1
aegir=# insert into toto values (2,'y');
INSERT 0 1
aegir=# insert into toto values (3,'x');
INSERT 0 1
aegir=# select lib from toto;
lib
-----
z
y
x
(3 rows)
aegir=# select lib from toto limit 1;
lib
-----
z
(1 row)
aegir=# select id from toto limit 1;
id
----
1
(1 row)Hors ligne
Comment voulez vous retrouver la première ligne dans un système ensembliste si l'on y met pas un peu d'ordre ?
Certes il n'y a pas de ORDER BY (tri logique), mais il effectue un tri interne (physique) d'une quelconque manière (accès à la PREMIÈRE ligne de la page) pour retrouver l'information...
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Non, il faudrait que vous fassiez la différence entre ce que vous imaginez et la réalité. Je vous suggère donc de suivre vos propre conseils:
Bref, commencez par lire, apprendre, vous former à PostgreSQL...
Merci.
Julien.
https://rjuju.github.io/
Hors ligne
Pour revenir au problème sur le LIMIT OFFSET 1comme l'a suggéré gleu, pouvez-vous redonner le plan d'exécution de la requête après avoir fait un SET enable_seqscan = off; dans la même session ?
Thomas Reiss
Hors ligne
Pour revenir au problème sur le LIMIT OFFSET 1comme l'a suggéré gleu, pouvez-vous redonner le plan d'exécution de la requête après avoir fait un SET enable_seqscan = off; dans la même session ?
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=3645.60..189304.19 rows=51760 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..3632.66 rows=51760 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)
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..59.71 rows=1 width=50)
-> Seq Scan on document (cost=0.00..3090748.05 rows=51760 width=50)
Filter: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(3 rows)
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..2986.23 rows=1 width=50)
-> Seq Scan on document (cost=0.00..3090748.05 rows=1035 width=50)
Filter: ((extra ->> 'id_cellar'::text) ~~ '%487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(3 rows)
dev_ds=> SET enable_seqscan = off;
SET
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=3645.60..189304.19 rows=51760 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..3632.66 rows=51760 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)
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=3645.60..3649.18 rows=1 width=50)
-> Bitmap Heap Scan on document (cost=3645.60..189304.19 rows=51760 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..3632.66 rows=51760 width=0)
Index Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(5 rows)
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=3497.07..3501.03 rows=1 width=50)
-> Bitmap Heap Scan on document (cost=3497.07..7594.29 rows=1035 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..3496.81 rows=1035 width=0)
Index Cond: ((extra ->> 'id_cellar'::text) ~~ '%487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(5 rows)
Time: 0.939 msDernière modification par herve.lefebvre (04/02/2021 12:00:00)
Hors ligne
Merci. Pouvez-vous essayer avec cette requête, en prenant soin de remettre enable_seqscan à on :
WITH w AS NOT MATERIALIZED (
explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1'
)
SELECT * FROM w LIMIT 1;Vous tombez sur un des problèmes de l'optimiseur de PostgreSQL, où il "suppose" qu'il va trouver très rapidement une ligne qui colle au critère de filtrage.
Une idée, peut-être en l'air, serait de forcer un tri sur le résultat, là Postgres prend l'index pour retrouver la première ligne qui colle :
explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' order by extra->>'id_cellar' limit 1;Eventuellement, vous ne pouvez pas créer un btree pour voir ?
Thomas Reiss
Hors ligne
Merci. Pouvez-vous essayer avec cette requête, en prenant soin de remettre enable_seqscan à on :
WITH w AS NOT MATERIALIZED ( explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' ) SELECT * FROM w LIMIT 1;Vous tombez sur un des problèmes de l'optimiseur de PostgreSQL, où il "suppose" qu'il va trouver très rapidement une ligne qui colle au critère de filtrage.
Une idée, peut-être en l'air, serait de forcer un tri sur le résultat, là Postgres prend l'index pour retrouver la première ligne qui colle :
explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' order by extra->>'id_cellar' limit 1;
dev_ds=> explain WITH w AS NOT MATERIALIZED (
select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1'
)
SELECT * FROM w LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit (cost=0.00..59.71 rows=1 width=50)
-> Seq Scan on document (cost=0.00..3090748.05 rows=51760 width=50)
Filter: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
(3 rows)
Time: 1.377 ms
dev_ds=>
dev_ds=> explain select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' order by extra->>'id_cellar' limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Limit (cost=189692.39..189692.39 rows=1 width=82)
-> Sort (cost=189692.39..189821.79 rows=51760 width=82)
Sort Key: ((extra ->> 'id_cellar'::text))
-> Bitmap Heap Scan on document (cost=3645.60..189433.59 rows=51760 width=82)
Recheck Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
-> Bitmap Index Scan on ix_ds_document_id_cellar (cost=0.00..3632.66 rows=51760 width=0)
Index Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
JIT:
Functions: 5
Options: Inlining false, Optimization false, Expressions true, Deforming true
(10 rows)
Time: 4.387 ms
Eventuellement, vous ne pouvez pas créer un btree pour voir ?
Non, ce sont les dernières heures auxquelles j'ai accès à la db.
Pour le fun, la différence entre les 2 :
dev_ds=> select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' limit 1;
uri | date
--------------------------------------+---------------------
******************************R_0007 | 2020-09-14 00:00:00
(1 row)
Time: 54.391 ms
dev_ds=> SET enable_seqscan = on;
SET
Time: 0.335 ms
dev_ds=> select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' limit 1;
uri | date
--------------------------------------+---------------------
******************************R_0007 | 2020-09-14 00:00:00
(1 row)
Time: 1064404.383 ms (17:44.404)Dernière modification par herve.lefebvre (04/02/2021 12:36:30)
Hors ligne
Merci du retour; Le coup du ORDER BY pour forcer Postgres à utiliser l'index semble faire mouche. Vous pouvez valider le temps d'exécution avec EXPLAIN (ANALYZE, BUFFERS).
Thomas Reiss
Hors ligne
Merci du retour; Le coup du ORDER BY pour forcer Postgres à utiliser l'index semble faire mouche. Vous pouvez valider le temps d'exécution avec EXPLAIN (ANALYZE, BUFFERS).
Oui ça le fait avec le order by :
dev_ds=> SET enable_seqscan = on;
SET
Time: 0.331 ms
dev_ds=> EXPLAIN (ANALYZE, BUFFERS) select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1' order by extra->>'id_cellar' limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=189692.39..189692.39 rows=1 width=82) (actual time=64.791..64.792 rows=1 loops=1)
Buffers: shared hit=9021
-> Sort (cost=189692.39..189821.79 rows=51760 width=82) (actual time=59.176..59.176 rows=1 loops=1)
Sort Key: ((extra ->> 'id_cellar'::text))
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=9021
-> Bitmap Heap Scan on document (cost=3645.60..189433.59 rows=51760 width=82) (actual time=59.160..59.166 rows=4 loops=1)
Recheck Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
Heap Blocks: exact=4
Buffers: shared hit=9021
-> Bitmap Index Scan on ix_ds_document_id_cellar (cost=0.00..3632.66 rows=51760 width=0) (actual time=59.142..59.143 rows=4 loops=1)
Index Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
Buffers: shared hit=9017
Planning Time: 0.143 ms
JIT:
Functions: 5
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.803 ms, Inlining 0.000 ms, Optimization 0.385 ms, Emission 5.037 ms, Total 6.225 ms
Execution Time: 65.648 ms
(19 rows)
Time: 66.068 ms
dev_ds=> Hors ligne
Merci. J'ai fait une bourde pour la CTE, il faut utiliser AS MATERIALIZED et non AS NOT MATERIALIZED. A vous de voir si c'est meilleur, mais à mon avis le ORDER BY supplémentaire sera meilleur.
Thomas Reiss
Hors ligne
Merci. J'ai fait une bourde pour la CTE, il faut utiliser AS MATERIALIZED et non AS NOT MATERIALIZED. A vous de voir si c'est meilleur, mais à mon avis le ORDER BY supplémentaire sera meilleur.
Oui effectivement, ça le fait aussi :
dev_ds=> explain WITH w AS MATERIALIZED (
select uri, date from ds.document where extra->>'id_cellar' like '487d4f99-f65b-11ea-991b-01aa75ed71a1'
)
SELECT * FROM w LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=189304.19..189304.21 rows=1 width=40)
CTE w
-> Bitmap Heap Scan on document (cost=3645.60..189304.19 rows=51760 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..3632.66 rows=51760 width=0)
Index Cond: ((extra ->> 'id_cellar'::text) ~~ '487d4f99-f65b-11ea-991b-01aa75ed71a1'::text)
-> CTE Scan on w (cost=0.00..1035.20 rows=51760 width=40)
JIT:
Functions: 5
Options: Inlining false, Optimization false, Expressions true, Deforming true
(10 rows)
Time: 36.457 msMais bon, le problème n'était pas d'être à 10 ms près, mais à un quart d'heure près :-D
Hors ligne