0

HypoPG et les index hypothétiques

twitterlinkedinmail

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 :

twitterlinkedinmail

Sarah FAVEERE

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.