Parmi toutes les extensions existantes pour PostgreSQL il existe une petite extension pour aider dans le domaine de la performance.
Cette extension se nomme HypoPG est développée et maintenue par plusieurs personnes de la communauté PostgreSQL.
Elle sert notamment à pouvoir créer et tester des index hypothétiques sur votre base de données. Un index hypothétique est un index qui n’existe pas vraiment. Il n’est pas créé, donc il ne prend pas de place en mémoire ou de CPU ni même de place disque puisque par définition il n’existe pas. Le but est de savoir si PostgreSQL utilisera votre index dans la requête, et s’il serait utile à sa performance, et tout ceci sans avoir à dépenser de ressource pour le tester.
Les indexes créés ne sont stockés dans aucun catalogues, ils sont uniquement stockés dans la mémoire de votre connexion. De ce fait, ils n’impacteront aucune connexion concurrente.
HypoPG supporte plusieurs types d’index : les btree, les brin, les hash ou même encore les bloom qui proviennent d’une extension à part entière.
Pour pouvoir utiliser cette extension, la seule restriction c’est la version de PostgreSQL que vous utilisez. Elle ne fonctionnera que sur des version supérieures à la 9.2. Elle est disponible pour une large diversité de système différents. Pour Ubuntu, comme la majorité des extensions, il faudra la compiler pour la faire fonctionner.
Etape 1 : Installer un Postgresql
root@sarah:~# sudo apt-get -y install postgresql-13
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libfreetype6 libpq-dev
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
postgresql-client-13
Suggested packages:
postgresql-doc-13
The following NEW packages will be installed:
postgresql-13 postgresql-client-13
0 upgraded, 2 newly installed, 0 to remove and 21 not upgraded.
Need to get 16.4 MB of archives.
After this operation, 54.1 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-13 amd64 13.7-1.pgdg18.04+1 [1515 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-13 amd64 13.7-1.pgdg18.04+1 [14.9 MB]
Fetched 16.4 MB in 2s (6985 kB/s)
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-13.
(Reading database ... 39462 files and directories currently installed.)
Preparing to unpack .../postgresql-client-13_13.7-1.pgdg18.04+1_amd64.deb ...
Unpacking postgresql-client-13 (13.7-1.pgdg18.04+1) ...
Selecting previously unselected package postgresql-13.
Preparing to unpack .../postgresql-13_13.7-1.pgdg18.04+1_amd64.deb ...
Unpacking postgresql-13 (13.7-1.pgdg18.04+1) ...
Setting up postgresql-client-13 (13.7-1.pgdg18.04+1) ...
update-alternatives: using /usr/share/postgresql/13/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-13 (13.7-1.pgdg18.04+1) ...
Creating new PostgreSQL cluster 13/main ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/13/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctlcluster 13 main start
update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for postgresql-common (241.pgdg18.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:Etape 2 : Installer les outils de dev pour postgresql-13
root@sarah:~# sudo apt-get install postgresql-server-dev-13 Reading package lists... Done Building dependency tree Reading state information... Done The following package was automatically installed and is no longer required: libfreetype6 Use 'sudo apt autoremove' to remove it. The following NEW packages will be installed: postgresql-server-dev-13 0 upgraded, 1 newly installed, 0 to remove and 21 not upgraded. Need to get 1035 kB of archives. After this operation, 6260 kB of additional disk space will be used. Get:1 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-server-dev-13 amd64 13.7-1.pgdg18.04+1 [1035 kB] Fetched 1035 kB in 1s (1126 kB/s) Selecting previously unselected package postgresql-server-dev-13. (Reading database ... 41285 files and directories currently installed.) Preparing to unpack .../postgresql-server-dev-13_13.7-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-server-dev-13 (13.7-1.pgdg18.04+1) ... Setting up postgresql-server-dev-13 (13.7-1.pgdg18.04+1) ...
Etape 3 : Télécharger les sources
root@sarah:~# wget https://github.com/HypoPG/hypopg/archive/refs/tags/1.3.1.zip --2022-07-28 09:01:31-- https://github.com/HypoPG/hypopg/archive/refs/tags/1.3.1.zip Resolving github.com (github.com)... 140.82.121.4 Connecting to github.com (github.com)|140.82.121.4|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://codeload.github.com/HypoPG/hypopg/zip/refs/tags/1.3.1 [following] --2022-07-28 09:01:31-- https://codeload.github.com/HypoPG/hypopg/zip/refs/tags/1.3.1 Resolving codeload.github.com (codeload.github.com)... 140.82.121.10 Connecting to codeload.github.com (codeload.github.com)|140.82.121.10|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/zip] Saving to: ‘1.3.1.zip’ 1.3.1.zip [ <=> ] 78.42K --.-KB/s in 0.02s 2022-07-28 09:01:31 (3.91 MB/s) - ‘1.3.1.zip’ saved [80304]
Etape 4 : Unzip et installation
root@sarah:~# unzip 1.3.1.zip Archive: 1.3.1.zip 57d711bc4e37164c8edac81580a5f477e2a33d86 creating: hypopg-1.3.1/ inflating: hypopg-1.3.1/.gitignore inflating: hypopg-1.3.1/CHANGELOG.md inflating: hypopg-1.3.1/CONTRIBUTORS.md inflating: hypopg-1.3.1/LICENSE inflating: hypopg-1.3.1/META.json inflating: hypopg-1.3.1/Makefile inflating: hypopg-1.3.1/README.md inflating: hypopg-1.3.1/TODO.md creating: hypopg-1.3.1/debian/ inflating: hypopg-1.3.1/debian/changelog extracting: hypopg-1.3.1/debian/compat inflating: hypopg-1.3.1/debian/control inflating: hypopg-1.3.1/debian/control.in inflating: hypopg-1.3.1/debian/copyright extracting: hypopg-1.3.1/debian/pgversions inflating: hypopg-1.3.1/debian/rules creating: hypopg-1.3.1/debian/source/ extracting: hypopg-1.3.1/debian/source/format creating: hypopg-1.3.1/debian/tests/ inflating: hypopg-1.3.1/debian/tests/control inflating: hypopg-1.3.1/debian/tests/control.in extracting: hypopg-1.3.1/debian/tests/installcheck inflating: hypopg-1.3.1/debian/watch creating: hypopg-1.3.1/docs/ extracting: hypopg-1.3.1/docs/.gitignore inflating: hypopg-1.3.1/docs/Makefile inflating: hypopg-1.3.1/docs/conf.py inflating: hypopg-1.3.1/docs/contributing.rst inflating: hypopg-1.3.1/docs/hypothetical_indexes.rst inflating: hypopg-1.3.1/docs/index.rst inflating: hypopg-1.3.1/docs/installation.rst inflating: hypopg-1.3.1/docs/make.bat inflating: hypopg-1.3.1/docs/requirements.txt inflating: hypopg-1.3.1/docs/usage.rst creating: hypopg-1.3.1/expected/ inflating: hypopg-1.3.1/expected/hypo_brin.out inflating: hypopg-1.3.1/expected/hypo_hash.out inflating: hypopg-1.3.1/expected/hypo_include.out inflating: hypopg-1.3.1/expected/hypo_index_part.out inflating: hypopg-1.3.1/expected/hypo_index_part_10.out inflating: hypopg-1.3.1/expected/hypopg.out inflating: hypopg-1.3.1/hypopg--1.3.1.sql inflating: hypopg-1.3.1/hypopg.c inflating: hypopg-1.3.1/hypopg.control inflating: hypopg-1.3.1/hypopg_index.c creating: hypopg-1.3.1/import/ inflating: hypopg-1.3.1/import/hypopg_import.c inflating: hypopg-1.3.1/import/hypopg_import_index.c creating: hypopg-1.3.1/include/ inflating: hypopg-1.3.1/include/hypopg.h inflating: hypopg-1.3.1/include/hypopg_import.h inflating: hypopg-1.3.1/include/hypopg_import_index.h inflating: hypopg-1.3.1/include/hypopg_index.h creating: hypopg-1.3.1/test/ creating: hypopg-1.3.1/test/sql/ inflating: hypopg-1.3.1/test/sql/hypo_brin.sql inflating: hypopg-1.3.1/test/sql/hypo_hash.sql inflating: hypopg-1.3.1/test/sql/hypo_include.sql inflating: hypopg-1.3.1/test/sql/hypo_index_part.sql inflating: hypopg-1.3.1/test/sql/hypo_index_part_10.sql inflating: hypopg-1.3.1/test/sql/hypopg.sql inflating: hypopg-1.3.1/typedefs.list root@sarah:~# cd hypopg-1.3.1/ root@sarah:~/hypopg-1.3.1# make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o hypopg.o hypopg.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o hypopg_index.o hypopg_index.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o import/hypopg_import.o import/hypopg_import.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o import/hypopg_import_index.o import/hypopg_import_index.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -shared -o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-6.0/lib -Wl,--as-needed /usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o hypopg.bc hypopg.c /usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o hypopg_index.bc hypopg_index.c /usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o import/hypopg_import.bc import/hypopg_import.c /usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o import/hypopg_import_index.bc import/hypopg_import_index.c root@sarah:~/hypopg-1.3.1# sudo make install /bin/mkdir -p '/usr/lib/postgresql/13/lib' /bin/mkdir -p '/usr/share/postgresql/13/extension' /bin/mkdir -p '/usr/share/postgresql/13/extension' /usr/bin/install -c -m 755 hypopg.so '/usr/lib/postgresql/13/lib/hypopg.so' /usr/bin/install -c -m 644 .//hypopg.control '/usr/share/postgresql/13/extension/' /usr/bin/install -c -m 644 .//hypopg--1.3.1.sql '/usr/share/postgresql/13/extension/' /bin/mkdir -p '/usr/lib/postgresql/13/lib/bitcode/hypopg' /bin/mkdir -p '/usr/lib/postgresql/13/lib/bitcode'/hypopg/ '/usr/lib/postgresql/13/lib/bitcode'/hypopg/import/ /usr/bin/install -c -m 644 hypopg.bc '/usr/lib/postgresql/13/lib/bitcode'/hypopg/./ /usr/bin/install -c -m 644 hypopg_index.bc '/usr/lib/postgresql/13/lib/bitcode'/hypopg/./ /usr/bin/install -c -m 644 import/hypopg_import.bc '/usr/lib/postgresql/13/lib/bitcode'/hypopg/import/ /usr/bin/install -c -m 644 import/hypopg_import_index.bc '/usr/lib/postgresql/13/lib/bitcode'/hypopg/import/ cd '/usr/lib/postgresql/13/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o hypopg.index.bc hypopg/hypopg.bc hypopg/hypopg_index.bc hypopg/import/hypopg_import.bc hypopg/import/hypopg_import_index.bc
Etape 5 : Création de la base, mise en place de l’extension
postgres@sarah:~$ psql
psql (13.7 (Ubuntu 13.7-1.pgdg18.04+1))
Type "help" for help.
postgres=# Create database clients;
CREATE DATABASE
postgres=# \c clients
You are now connected to database "clients" as user "postgres".
clients=# CREATE EXTENSION hypopg ;
CREATE EXTENSION
clients=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-------------------------------------
hypopg | 1.3.1 | public | Hypothetical indexes for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows) Pour tester son efficacité, il ne nous reste plus qu’à faire un petit test rapide. On va créer une table de test et la remplir avec des données aléatoires.
clients=# CREATE TABLE test (id integer, val text) ; CREATE TABLE clients=# INSERT INTO test SELECT i, 'line ' || i FROM generate_series(1, 100000 ) i ; INSERT 0 100000 clients=# select * from test limit 10; id | val ----+--------- 1 | line 1 2 | line 2 3 | line 3 4 | line 4 5 | line 5 6 | line 6 7 | line 7 8 | line 8 9 | line 9 10 | line 10 (10 rows) clients=# VACUUM ANALYZE test ; VACUUM
Cette table n’a donc aucun index, puisqu’on viens juste de la créer. Imaginons que nous voudrions savoir si un index sur cette table permettrait d’améliorer les performances d’une requête sur cette table.
Ainsi, si on fait un explain d’un simple select voici ce qu’on obtient :
clients=# EXPLAIN SELECT val from test where id = 1;
QUERY PLAN
--------------------------------------------------------
Seq Scan on test (cost=0.00..1791.00 rows=1 width=10)
Filter: (id = 1)
(2 rows)Pour améliorer cette requête, on pourrait créer un simple index btree sur la colonne de l’id. Faisons un test en créant cet index à l’aide de HypoPG, pour cela on utilisera la fonction hypopg_create_index a laquelle on passera en argument l’ordre create de l’index qu’on veut réaliser.
clients=# SELECT * FROM hypopg_create_index('CREATE INDEX ON test (id)') ;
indexrelid | indexname
------------+----------------------
13369 | <13369>btree_test_id
(1 row)Cette fonction retourne deux résultats : l’identifiant d’objet de l’index hypothétique qu’on vient de réaliser, et le nom généré pour cet index. Ne reste plus qu’à faire un test en relancant notre explain pour voir si PostgreSQL utilise bien notre nouvel index.
clients=# EXPLAIN SELECT val from test where id = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using "<13369>btree_test_id" on test (cost=0.04..8.06 rows=1 width=10)
Index Cond: (id = 1)
(2 rows)On voit alors bien que le plan d’exécution utilise notre index hypothétique, et que celui-ci nous fait gagner en performance.
Attention, HypoPG ne fonctionne pas avec un EXPLAIN ANALYZE, car l’index n’existe pas, il ne peut donc pas être utilisé. Si on vient à vouloir faire un analyze sur notre requête précédente, on se rendra rapidement compte qu’il n’est fait mention de notre index nulle part.
clients=# EXPLAIN ANALYZE SELECT val FROM test WHERE id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1791.00 rows=1 width=10) (actual time=0.017..6.983 rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 99999
Planning Time: 0.048 ms
Execution Time: 7.071 ms
(5 rows)L’extension comporte plusieurs autres fonctions qui pourraient vous être utiles lors de son utilisation :
– Pour voir la liste des index hypothétiques que vous avez créé vous pouvez utiliser la vue hypopg_list_indexes ou la fonction hypopg()
clients=# select * from hypopg(); -[ RECORD 1 ]+--------------------- indexname | <13369>btree_test_id indexrelid | 13369 indrelid | 16398 innatts | 1 indisunique | f indkey | 1 indcollation | 0 indclass | 1978 indoption | indexprs | indpred | amid | 403
– Si vous voulez créer réellement un index hypothétique que vous avez essayé, mais que vous n’avez bien la requête qui vous a permis de le créer, vous pouvez utliliser la fonction hypopg_get_indexdef(oid) en passant en paramètre l’oid de votre index hypothétique, cela vous permettra de retrouver votre ordre create. Ou vous pouvez utiliser la vue qui s’appelle hypopg_list_indexes et qui vous donne l’information pour tout vos index hypothétiques :
clients=# SELECT *, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ; -[ RECORD 1 ]-------+--------------------------------------------- indexrelid | 13369 index_name | <13369>btree_test_id schema_name | public table_name | test am_name | btree hypopg_get_indexdef | CREATE INDEX ON public.test USING btree (id)
– La fonction hypopg_relation_size(oid) permet d’évaluer la taille que ferait l’index hypothétique si il était réellement créé :
clients=# SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid)) FROM hypopg_list_indexes ; -[ RECORD 1 ]--+--------------------- index_name | <13369>btree_test_id pg_size_pretty | 2544 kB
– La fonction hypopg_drop_index(oid) qui permet de supprimer un index hypothétique existant et la fonction hypopg_reset() qui supprime d’un coup tout les index hypothétique possédés.
clients=# select * from hypopg_reset(); -[ RECORD 1 ]+- hypopg_reset |
Cette petite extension est donc très pratique quand on cherche à tester des index sans vouloir réserver de l’espace pour ceux-ci dans la base de donnée. Facile d’utilisation, elle pourrait bien devenir le prochain allié de vos performances.
Continuez votre lecture sur le blog :
- PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
- Pseudonymiser vos bases PostgreSQL (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL 14 et le scram-sha-256 (Sarah FAVEERE) [PostgreSQL]
- Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]