Vous n'êtes pas identifié(e).
Normalement, en transformant les JOIN en LEFT JOIN ? Avec la jointure interne (soit JOIN ou INNER JOIN si on aime taper sur son clavier), on ne fait ressortir que les lignes qui correspondent strictement à la jointure, donc pour lesquels il y a une entrée dans pg_auth_members.
Parfait, merci du retour.
Avec la CTE, vous n'exécutez pas la requête, les 33 ms comptabilise uniquement le temps de planification. Il faut ajouter l'option ANALYZE à minima à l'explain pour que PostgreSQL exécute réellement la requête. Attention, si vous faites cela sur un INSERT/UPDATE/DELETE, Postgres réalisera VRAIMENT l'opération.
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.
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).
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 ?
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 ?
@SqlPro: Avez-vous noté que j'ai aussi augmenté le paramètre maintenance_work_mem à 1Go ? Vous ne l'avez pas augmenté dans votre test, et ça va vous aider à éviter de passer par un tri sur disque lors de la création des index par exemple.
Oups, je n'ai qu'un degré de parallélisme de 2 pour les opérations de maintenance. Passons le à 8 :
(index droppés)
test=# SET max_parallel_maintenance_workers = 8;
test=# \timing
Chronométrage activé.
test=# CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
CREATE INDEX
Durée : 4818,862 ms (00:04,819)
test=# CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
CREATE INDEX
Durée : 5000,068 ms (00:05,000)
test=# CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
CREATE INDEX
Durée : 11537,936 ms (00:11,538)
test=# VACUUM FULL t_personne_prs ;
VACUUM
Durée : 36408,680 ms (00:36,409)Bonjour,
Profitant d'un petit temps, je viens juste de reproduire votre "test" sous Linux, sur ma machine perso. Elle est plus modeste et les données sont stockées sur un SSD en SATA, donc pas l'idéal.
Config matérielle :
AMD Ryzen 7 3700X 8-Core Processor
32 Go de RAM
Kernel Linux sans tuning particulier: MD Ryzen 7 3700X 8-Core Processor
La config de PostgreSQL 13 est un tunée :
postgres=# SELECT name, setting, unit FROM pg_settings WHERE source <> 'default' AND sourcefile = '/home/thomas/postgresql/v13/pgdata/postgresql.conf';
name | setting | unit
------------------------------+-------------------------+------
checkpoint_completion_target | 0.9 |
checkpoint_flush_after | 128 | 8kB
effective_io_concurrency | 1000 |
maintenance_io_concurrency | 1000 |
maintenance_work_mem | 1048576 | kB
max_wal_size | 2048 | MB
min_wal_size | 80 | MB
shared_buffers | 524288 | 8kB
wal_buffers | 16384 | 8kB
work_mem | 32768 | kBVous remarquerez donc l'existence d'un paramètre maintenance_work_mem.
Voici ce que j'obtiens. Le COPY me prend 15 secondes. Pour le VACUUM FULL, il prend 9 secondes avant création des index et 41 secondes après leur création. L'INSERT/SELECT passe à 20 secondes. Les collectes de stats sont plus rapides que sur SQL Server.
Ah oui, en parlant du VACUUM FULL. Depuis la version 8.4 sortie en l'an de grâce 2009, le VACUUM FULL reconstruit intégralement les index. Je ne comprends pas la pertinence de passer un REINDEX derrière, à part pour être sûr de biaiser le résultat ou de démontrer que vous n'êtes pas pertinents pour parler de PostgreSQL (je n'ai rien à dire quant à SQL Server, je ne connais pas ce SGBD).
Dans tous les cas, quand on vous dit que PostgreSQL est plus performance sous Linux, il serait peut-être temps de réaliser que ce n'est pas pour rien.
test=# \timing
Chronométrage activé.
test=# CREATE TABLE T_PERSONNE_PRS
(PRS_ID SERIAL PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25));
Temps : 25,752 ms
test=# CREATE COLLATION fr_ci_ai
(provider = icu,
locale = 'fr-u-ks-level1-kc-false',
deterministic=false);
Temps : 12,895 ms
test=# CREATE TABLE t_personne_ci_ai_prs
(prs_id INT PRIMARY KEY,
prs_nom VARCHAR(32) COLLATE fr_ci_ai,
prs_prenom VARCHAR(25) COLLATE fr_ci_ai);
Temps : 23,535 ms
test=# COPY T_PERSONNE_PRS
(PRS_ID, PRS_NOM , PRS_PRENOM)
FROM '/home/thomas/postgresql/v13/personnes.txt'
WITH (DELIMITER ',',
ENCODING 'WIN1252');
Durée : 15257,902 ms (00:15,258)
test=# VACUUM FULL t_personne_prs;
Durée : 8950,434 ms (00:08,950)
test=# CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
Durée : 7126,100 ms (00:07,126)
test=# CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
Durée : 7526,953 ms (00:07,527)
test=# CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
Durée : 13931,502 ms (00:13,932)
test=# VACUUM FULL t_personne_prs;
Durée : 41881,512 ms (00:41,882)
test=# INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;
Durée : 20092,208 ms (00:20,092)
test=# ANALYZE T_PERSONNE_PRS (prs_nom);
Temps : 129,512 ms
test=# ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);
Temps : 149,290 msD'ailleurs, où voyez-vous un tri dans les plans montrés par M. Lefebvre ?
Comme rjuju vous l'a déjà indiqué, votre procédure stockée ne va pas fonctionner correctement. L'ordre COPY est appelé pour chaque ligne retournée par le curseur, et à chaque itération va écraser le fichier créé à l'itération précédente. Vous n'aurez alors que la définition du dernier index retourné par votre requête.
L'ordre suivant est un peu plus simple que ce que vous faites et vous récupère l'ordre de création de tous les index utilisateurs. La fonction et son curseur sont inutiles d'ailleurs:
COPY (SELECT pg_get_indexdef(indexrelid) FROM pg_stat_user_indexes) TO '..../testindex.sql'Bonjour,
Désolé de ne pas avoir donné de nouvelles depuis. L'auteur a poussé le correctif adéquat pour gérer correctement pg_stat_statements: le patch appliqué.
Vous devriez pouvoir récupérer la dernière version et faire fonctionner pgstatspack correctement dorénavant.
Uwe Bartels va réaliser le correctif. Une nouvelle version sera disponible dans quelques jours normalement.
Bonjour,
Dans un premier temps, il va vous falloir modifier le script sql/pgstatspack_create_snap.sql pour corriger la condition jointure mal écrite (ligne 203 du script SQL):
join pgstatspack_names n1 on s.query=n1.nameUne fois que c'est fait, il vous faudra recréer la fonction pgstatspack_snap en exécutant le script sur la base sur laquelle vous avez rencontrez ce problème.
Je remonte le bug à l'auteur.
Cordialement,
Thomas
Bonjour et bonne année à vous,
L'année 2012 démarre avec quelques changements pour l'association. Vous devriez recevoir un appel à cotisation bientôt.
Cordialement,
Thomas
Ce que je vois, c'est que votre nouvel index n'est utilisé dans aucun cas (cf première ligne de l'explain qui donne un seq scan, donc une lecture séquentielle pour les deux requêtes).
La différence de temps d'exécution s'explique car pour la première requête, PostgreSQL va calculer le résultat de lower(erpunaccent(name)) pour chaque ligne lues et le comparer à 'ogvvatoies%' avec le LIKE. Dans le second cas, PostgreSQL va comparer directement la colonne name à 'oGvvÀtÖiÉ%', mais sans le moindre calcul.
Voici un exemple d'index sur fonction, chose que vous avez probablement faite:
postgres=# EXPLAIN SELECT * FROM test WHERE nom = 'tata';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using idx_test_nom on test (cost=0.00..4.30 rows=1 width=5)
Index Cond: (nom = 'tata'::text)
(2 lignes)
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE nom = 'tata';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_test_nom on test (cost=0.00..8.45 rows=9 width=5) (actual time=0.070..0.073 rows=1 loops=1)
Index Cond: (nom = 'tata'::text)
Total runtime: 0.135 ms
(3 lignes)
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) = 'tata';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..5092.17 rows=1311 width=5) (actual time=278.159..278.161 rows=1 loops=1)
Filter: (lower(nom) = 'tata'::text)
Rows Removed by Filter: 262144
Total runtime: 278.215 ms
(4 lignes)
postgres=# CREATE INDEX idx_test_lower_nom ON test (lower(nom));
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) = 'tata';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=26.44..1261.33 rows=1311 width=5) (actual time=0.272..0.273 rows=1 loops=1)
Recheck Cond: (lower(nom) = 'tata'::text)
-> Bitmap Index Scan on idx_test_lower_nom (cost=0.00..26.11 rows=1311 width=0) (actual time=0.253..0.253 rows=1 loops=1)
Index Cond: (lower(nom) = 'tata'::text)
Total runtime: 0.343 ms
(5 lignes)Ensuite, pour utiliser LIKE, n'oubliez pas de créer l'index sur fonction avec l'option varchar_pattern_ops:
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) LIKE 'tat%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..5092.17 rows=1311 width=5) (actual time=244.566..244.568 rows=1 loops=1)
Filter: (lower(nom) ~~ 'tat%'::text)
Rows Removed by Filter: 262144
Total runtime: 244.620 ms
(4 lignes)
postgres=# DROP INDEX idx_test_lower_nom ;
DROP INDEX
postgres=# CREATE INDEX idx_test_lower_nom ON test (lower(nom) varchar_pattern_ops);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) LIKE 'tat%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=29.72..1264.61 rows=1311 width=5) (actual time=0.262..0.264 rows=1 loops=1)
Filter: (lower(nom) ~~ 'tat%'::text)
-> Bitmap Index Scan on idx_test_lower_nom (cost=0.00..29.39 rows=1311 width=0) (actual time=0.222..0.222 rows=1 loops=1)
Index Cond: ((lower(nom) ~>=~ 'tat'::text) AND (lower(nom) ~<~ 'tau'::text))
Total runtime: 0.335 ms
(5 lignes)Oups, désolé.
Je vous demandais de faire rpm -ql sur un paquet particulier. Vous saurez donc où se trouve les binaires.
De mémoire, c'est /usr/pgsql-9.1 mais ce n'est pas sûr.
Que donne la commande suivante:
rpm -ql postrgresql-9.1
Normalement, vous devrez éditer la variable $PATH pour pointer vers le répertoire des binaires de la 9.1. Cf le retour de la commande précédente.
Alors, je reviens avec ça. On avait loosé une conf de VMWare pour l'accès au SAN --> réglé.
Ensuite je n'ai pas suivi, mais il y aussi une histoire avec la version de vmware tools et probablement des pilotes. Là, je ne sais pas en dire plus, n'étant pas en charge de ça (et surtout formateur au moment de la résolution).
Pour le point 6, la vue pg_stat_all_indexes sert à quoi ?
Bonjour,
Attention concernant le vocabulaire. En vous connecter avec le user test et en allant voir chez toto,vous vous connectez sur la BASE toto et n'allez pas voir dans le schéma de toto. Une instance Postgres peut héberger plusieurs bases de données - pour preuve, les deux ordres CREATE DATABASE que vous avez passez.
Et pour vous répondre, lorsque vous faites le CREATE DATABASE, Postgres vous place implicitement un ordre "GRANT CONNECT, TEMPORARY ON DATABASE base TO public;" public étant un alias pour dire "tout le monde".
Donc pour interdire à test de se connecter sur la base toto, vous devrez y aller en deux étapes :
- REVOKE CONNECT, TEMPORARY ON DATABASE toto FROM public;
- GRANT CONNECT, TEMPORARY ON DATABASE toto TOTO;
Et contrairement à Oracle (car j'imagine que la confusion vient de là), un utilisateur ne dispose pas d'un schéma particulier qui lui est dédié. A vous de créer les bons schémas dans les bonnes bases. En revanche, chaque base possède un schéma "public" dans lequel tout le monde peut créer des objets.
De la même façon, le schéma public reçoit implicitement un "GRANT ALL ON SCHEMA public TO public;".
La philosophie du projet Postgres est assez claire en ce qui concerne la gestion de l'espace de stockage: vous devez superviser l'espace disque vous même. Et effectivement, cet espace de stockage ne se gère pas de la même façon qu'Oracle ou SQL Server. Je le vois comme une faiblesse et une force de Postgres: vous êtes plus limité mais le SGBD est bien moins complexe.
Donc à vous de mettre en œuvre une supervision pour que la saturation de l'espace disque n'arrive pas.
Je rejoints Frédéric sur l'absence de tablespace READ ONLY. J'aimerai bien que Postgres puisse disposer d'une telle fonctionnalité.
Et concernant le dernier point, c'est encore la philosophie de Postgres qui entre en jeu. Les développeurs vous fournissent un noyau, le SGBD lui-même, et vous laisse la possibilité de l'enrichir à votre guise. Ainsi sont nés un certain nombre de projets annexes, et notamment Slony et Londiste qui répondent au besoin de réplication des données.
Cdt,
Thomas
On a une nouvelle piste: les VM qui ont maintenant de bonnes perfs sont en RHEL 5. Celles qui posent encore problèmes sont en CentOS 5. De la même façon, les RHEL disposent de moins de RAM que les CentOS. Encore une fois, il nous faut pousser les tests pour avoir une réponse.
Concernant la charge, on est limite, mais pour l'instant on tient encore.