![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Parmi toutes les extensions existantes pour PostgreSQL il existe une petite extension pour aider dans le domaine de la performance.
\nCette extension se nomme HypoPG est d\u00e9velopp\u00e9e et maintenue par plusieurs personnes de la communaut\u00e9 PostgreSQL.<\/p>\n
Elle sert notamment \u00e0 pouvoir cr\u00e9er et tester des index hypoth\u00e9tiques sur votre base de donn\u00e9es. Un index hypoth\u00e9tique est un index qui n’existe pas vraiment. Il n’est pas cr\u00e9\u00e9, donc il ne prend pas de place en m\u00e9moire ou de CPU ni m\u00eame de place disque puisque par d\u00e9finition il n’existe pas. Le but est de savoir si PostgreSQL utilisera votre index dans la requ\u00eate, et s’il serait utile \u00e0 sa performance, et tout ceci sans avoir \u00e0 d\u00e9penser de ressource pour le tester.
\nLes indexes cr\u00e9\u00e9s ne sont stock\u00e9s dans aucun catalogues, ils sont uniquement stock\u00e9s dans la m\u00e9moire de votre connexion. De ce fait, ils n’impacteront aucune connexion concurrente.<\/p>\n
HypoPG supporte plusieurs types d’index : les btree, les brin, les hash ou m\u00eame encore les bloom qui proviennent d’une extension \u00e0 part enti\u00e8re.<\/p>\n
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\u00e9rieures \u00e0 la 9.2. Elle est disponible pour une large diversit\u00e9 de syst\u00e8me diff\u00e9rents. Pour Ubuntu, comme la majorit\u00e9 des extensions, il faudra la compiler pour la faire fonctionner.<\/p>\n
Etape 1 : Installer un Postgresql<\/p>\n
root@sarah:~# sudo apt-get -y install postgresql-13\r\nReading package lists... Done\r\nBuilding dependency tree\r\nReading state information... Done\r\nThe following packages were automatically installed and are no longer required:\r\n libfreetype6 libpq-dev\r\nUse 'sudo apt autoremove' to remove them.\r\nThe following additional packages will be installed:\r\n postgresql-client-13\r\nSuggested packages:\r\n postgresql-doc-13\r\nThe following NEW packages will be installed:\r\n postgresql-13 postgresql-client-13\r\n0 upgraded, 2 newly installed, 0 to remove and 21 not upgraded.\r\nNeed to get 16.4 MB of archives.\r\nAfter this operation, 54.1 MB of additional disk space will be used.\r\nGet:1 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-client-13 amd64 13.7-1.pgdg18.04+1 [1515 kB]\r\nGet:2 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-13 amd64 13.7-1.pgdg18.04+1 [14.9 MB]\r\nFetched 16.4 MB in 2s (6985 kB\/s)\r\nPreconfiguring packages ...\r\nSelecting previously unselected package postgresql-client-13.\r\n(Reading database ... 39462 files and directories currently installed.)\r\nPreparing to unpack ...\/postgresql-client-13_13.7-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-client-13 (13.7-1.pgdg18.04+1) ...\r\nSelecting previously unselected package postgresql-13.\r\nPreparing to unpack ...\/postgresql-13_13.7-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-13 (13.7-1.pgdg18.04+1) ...\r\nSetting up postgresql-client-13 (13.7-1.pgdg18.04+1) ...\r\nupdate-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\r\nSetting up postgresql-13 (13.7-1.pgdg18.04+1) ...\r\nCreating new PostgreSQL cluster 13\/main ...\r\n\/usr\/lib\/postgresql\/13\/bin\/initdb -D \/var\/lib\/postgresql\/13\/main --auth-local peer --auth-host md5\r\nThe files belonging to this database system will be owned by user "postgres".\r\nThis user must also own the server process.\r\n\r\nThe database cluster will be initialized with locale "C.UTF-8".\r\nThe default database encoding has accordingly been set to "UTF8".\r\nThe default text search configuration will be set to "english".\r\n\r\nData page checksums are disabled.\r\n\r\nfixing permissions on existing directory \/var\/lib\/postgresql\/13\/main ... ok\r\ncreating subdirectories ... ok\r\nselecting dynamic shared memory implementation ... posix\r\nselecting default max_connections ... 100\r\nselecting default shared_buffers ... 128MB\r\nselecting default time zone ... Etc\/UTC\r\ncreating configuration files ... ok\r\nrunning bootstrap script ... ok\r\nperforming post-bootstrap initialization ... ok\r\nsyncing data to disk ... ok\r\n\r\nSuccess. You can now start the database server using:\r\n\r\n pg_ctlcluster 13 main start\r\n\r\nupdate-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\r\nProcessing triggers for postgresql-common (241.pgdg18.04+1) ...\r\nBuilding PostgreSQL dictionaries from installed myspell\/hunspell packages...\r\nRemoving obsolete dictionary files:<\/pre>\nEtape 2 : Installer les outils de dev pour postgresql-13<\/p>\n
root@sarah:~# sudo apt-get install postgresql-server-dev-13\r\nReading package lists... Done\r\nBuilding dependency tree\r\nReading state information... Done\r\nThe following package was automatically installed and is no longer required:\r\n libfreetype6\r\nUse 'sudo apt autoremove' to remove it.\r\nThe following NEW packages will be installed:\r\n postgresql-server-dev-13\r\n0 upgraded, 1 newly installed, 0 to remove and 21 not upgraded.\r\nNeed to get 1035 kB of archives.\r\nAfter this operation, 6260 kB of additional disk space will be used.\r\nGet: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]\r\nFetched 1035 kB in 1s (1126 kB\/s)\r\nSelecting previously unselected package postgresql-server-dev-13.\r\n(Reading database ... 41285 files and directories currently installed.)\r\nPreparing to unpack ...\/postgresql-server-dev-13_13.7-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-server-dev-13 (13.7-1.pgdg18.04+1) ...\r\nSetting up postgresql-server-dev-13 (13.7-1.pgdg18.04+1) ...<\/pre>\nEtape 3 : T\u00e9l\u00e9charger les sources <\/p>\n
root@sarah:~# wget https:\/\/github.com\/HypoPG\/hypopg\/archive\/refs\/tags\/1.3.1.zip\r\n--2022-07-28 09:01:31-- https:\/\/github.com\/HypoPG\/hypopg\/archive\/refs\/tags\/1.3.1.zip\r\nResolving github.com (github.com)... 140.82.121.4\r\nConnecting to github.com (github.com)|140.82.121.4|:443... connected.\r\nHTTP request sent, awaiting response... 302 Found\r\nLocation: https:\/\/codeload.github.com\/HypoPG\/hypopg\/zip\/refs\/tags\/1.3.1 [following]\r\n--2022-07-28 09:01:31-- https:\/\/codeload.github.com\/HypoPG\/hypopg\/zip\/refs\/tags\/1.3.1\r\nResolving codeload.github.com (codeload.github.com)... 140.82.121.10\r\nConnecting to codeload.github.com (codeload.github.com)|140.82.121.10|:443... connected.\r\nHTTP request sent, awaiting response... 200 OK\r\nLength: unspecified [application\/zip]\r\nSaving to: \u20181.3.1.zip\u2019\r\n\r\n1.3.1.zip [ <=> ] 78.42K --.-KB\/s in 0.02s\r\n\r\n2022-07-28 09:01:31 (3.91 MB\/s) - \u20181.3.1.zip\u2019 saved [80304]<\/pre>\nEtape 4 : Unzip et installation<\/p>\n
root@sarah:~# unzip 1.3.1.zip\r\nArchive: 1.3.1.zip\r\n57d711bc4e37164c8edac81580a5f477e2a33d86\r\n creating: hypopg-1.3.1\/\r\n inflating: hypopg-1.3.1\/.gitignore\r\n inflating: hypopg-1.3.1\/CHANGELOG.md\r\n inflating: hypopg-1.3.1\/CONTRIBUTORS.md\r\n inflating: hypopg-1.3.1\/LICENSE\r\n inflating: hypopg-1.3.1\/META.json\r\n inflating: hypopg-1.3.1\/Makefile\r\n inflating: hypopg-1.3.1\/README.md\r\n inflating: hypopg-1.3.1\/TODO.md\r\n creating: hypopg-1.3.1\/debian\/\r\n inflating: hypopg-1.3.1\/debian\/changelog\r\n extracting: hypopg-1.3.1\/debian\/compat\r\n inflating: hypopg-1.3.1\/debian\/control\r\n inflating: hypopg-1.3.1\/debian\/control.in\r\n inflating: hypopg-1.3.1\/debian\/copyright\r\n extracting: hypopg-1.3.1\/debian\/pgversions\r\n inflating: hypopg-1.3.1\/debian\/rules\r\n creating: hypopg-1.3.1\/debian\/source\/\r\n extracting: hypopg-1.3.1\/debian\/source\/format\r\n creating: hypopg-1.3.1\/debian\/tests\/\r\n inflating: hypopg-1.3.1\/debian\/tests\/control\r\n inflating: hypopg-1.3.1\/debian\/tests\/control.in\r\n extracting: hypopg-1.3.1\/debian\/tests\/installcheck\r\n inflating: hypopg-1.3.1\/debian\/watch\r\n creating: hypopg-1.3.1\/docs\/\r\n extracting: hypopg-1.3.1\/docs\/.gitignore\r\n inflating: hypopg-1.3.1\/docs\/Makefile\r\n inflating: hypopg-1.3.1\/docs\/conf.py\r\n inflating: hypopg-1.3.1\/docs\/contributing.rst\r\n inflating: hypopg-1.3.1\/docs\/hypothetical_indexes.rst\r\n inflating: hypopg-1.3.1\/docs\/index.rst\r\n inflating: hypopg-1.3.1\/docs\/installation.rst\r\n inflating: hypopg-1.3.1\/docs\/make.bat\r\n inflating: hypopg-1.3.1\/docs\/requirements.txt\r\n inflating: hypopg-1.3.1\/docs\/usage.rst\r\n creating: hypopg-1.3.1\/expected\/\r\n inflating: hypopg-1.3.1\/expected\/hypo_brin.out\r\n inflating: hypopg-1.3.1\/expected\/hypo_hash.out\r\n inflating: hypopg-1.3.1\/expected\/hypo_include.out\r\n inflating: hypopg-1.3.1\/expected\/hypo_index_part.out\r\n inflating: hypopg-1.3.1\/expected\/hypo_index_part_10.out\r\n inflating: hypopg-1.3.1\/expected\/hypopg.out\r\n inflating: hypopg-1.3.1\/hypopg--1.3.1.sql\r\n inflating: hypopg-1.3.1\/hypopg.c\r\n inflating: hypopg-1.3.1\/hypopg.control\r\n inflating: hypopg-1.3.1\/hypopg_index.c\r\n creating: hypopg-1.3.1\/import\/\r\n inflating: hypopg-1.3.1\/import\/hypopg_import.c\r\n inflating: hypopg-1.3.1\/import\/hypopg_import_index.c\r\n creating: hypopg-1.3.1\/include\/\r\n inflating: hypopg-1.3.1\/include\/hypopg.h\r\n inflating: hypopg-1.3.1\/include\/hypopg_import.h\r\n inflating: hypopg-1.3.1\/include\/hypopg_import_index.h\r\n inflating: hypopg-1.3.1\/include\/hypopg_index.h\r\n creating: hypopg-1.3.1\/test\/\r\n creating: hypopg-1.3.1\/test\/sql\/\r\n inflating: hypopg-1.3.1\/test\/sql\/hypo_brin.sql\r\n inflating: hypopg-1.3.1\/test\/sql\/hypo_hash.sql\r\n inflating: hypopg-1.3.1\/test\/sql\/hypo_include.sql\r\n inflating: hypopg-1.3.1\/test\/sql\/hypo_index_part.sql\r\n inflating: hypopg-1.3.1\/test\/sql\/hypo_index_part_10.sql\r\n inflating: hypopg-1.3.1\/test\/sql\/hypopg.sql\r\n inflating: hypopg-1.3.1\/typedefs.list\r\nroot@sarah:~# cd hypopg-1.3.1\/\r\nroot@sarah:~\/hypopg-1.3.1# make\r\ngcc -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\r\ngcc -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\r\ngcc -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\r\ngcc -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\r\ngcc -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\r\n\/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\r\n\/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\r\n\/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\r\n\/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\r\nroot@sarah:~\/hypopg-1.3.1# sudo make install\r\n\/bin\/mkdir -p '\/usr\/lib\/postgresql\/13\/lib'\r\n\/bin\/mkdir -p '\/usr\/share\/postgresql\/13\/extension'\r\n\/bin\/mkdir -p '\/usr\/share\/postgresql\/13\/extension'\r\n\/usr\/bin\/install -c -m 755 hypopg.so '\/usr\/lib\/postgresql\/13\/lib\/hypopg.so'\r\n\/usr\/bin\/install -c -m 644 .\/\/hypopg.control '\/usr\/share\/postgresql\/13\/extension\/'\r\n\/usr\/bin\/install -c -m 644 .\/\/hypopg--1.3.1.sql '\/usr\/share\/postgresql\/13\/extension\/'\r\n\/bin\/mkdir -p '\/usr\/lib\/postgresql\/13\/lib\/bitcode\/hypopg'\r\n\/bin\/mkdir -p '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/ '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/import\/\r\n\/usr\/bin\/install -c -m 644 hypopg.bc '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/.\/\r\n\/usr\/bin\/install -c -m 644 hypopg_index.bc '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/.\/\r\n\/usr\/bin\/install -c -m 644 import\/hypopg_import.bc '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/import\/\r\n\/usr\/bin\/install -c -m 644 import\/hypopg_import_index.bc '\/usr\/lib\/postgresql\/13\/lib\/bitcode'\/hypopg\/import\/\r\ncd '\/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 <\/pre>\nEtape 5 : Cr\u00e9ation de la base, mise en place de l’extension<\/p>\n
postgres@sarah:~$ psql\r\npsql (13.7 (Ubuntu 13.7-1.pgdg18.04+1))\r\nType "help" for help.\r\n\r\npostgres=# Create database clients;\r\nCREATE DATABASE\r\npostgres=# \\c clients\r\nYou are now connected to database "clients" as user "postgres".\r\nclients=# CREATE EXTENSION hypopg ;\r\nCREATE EXTENSION\r\nclients=# \\dx\r\n List of installed extensions\r\n Name | Version | Schema | Description\r\n---------+---------+------------+-------------------------------------\r\n hypopg | 1.3.1 | public | Hypothetical indexes for PostgreSQL\r\n plpgsql | 1.0 | pg_catalog | PL\/pgSQL procedural language\r\n(2 rows) <\/pre>\nPour tester son efficacit\u00e9, il ne nous reste plus qu’\u00e0 faire un petit test rapide. On va cr\u00e9er une table de test et la remplir avec des donn\u00e9es al\u00e9atoires.<\/p>\n
clients=# CREATE TABLE test (id integer, val text) ;\r\nCREATE TABLE\r\nclients=# INSERT INTO test SELECT i, 'line ' || i FROM generate_series(1, 100000 ) i ;\r\nINSERT 0 100000\r\nclients=# select * from test limit 10;\r\n id | val\r\n----+---------\r\n 1 | line 1\r\n 2 | line 2\r\n 3 | line 3\r\n 4 | line 4\r\n 5 | line 5\r\n 6 | line 6\r\n 7 | line 7\r\n 8 | line 8\r\n 9 | line 9\r\n 10 | line 10\r\n(10 rows) \r\nclients=# VACUUM ANALYZE test ;\r\nVACUUM <\/pre>\nCette table n’a donc aucun index, puisqu’on viens juste de la cr\u00e9er. Imaginons que nous voudrions savoir si un index sur cette table permettrait d’am\u00e9liorer les performances d’une requ\u00eate sur cette table.
\nAinsi, si on fait un explain d’un simple select voici ce qu’on obtient :<\/p>\n
clients=# EXPLAIN SELECT val from test where id = 1;\r\n QUERY PLAN\r\n--------------------------------------------------------\r\n Seq Scan on test (cost=0.00..1791.00 rows=1 width=10)\r\n Filter: (id = 1)\r\n(2 rows)<\/pre>\nPour am\u00e9liorer cette requ\u00eate, on pourrait cr\u00e9er un simple index btree sur la colonne de l’id. Faisons un test en cr\u00e9ant cet index \u00e0 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\u00e9aliser.<\/p>\n
clients=# SELECT * FROM hypopg_create_index('CREATE INDEX ON test (id)') ;\r\n indexrelid | indexname\r\n------------+----------------------\r\n 13369 | <13369>btree_test_id\r\n(1 row)<\/pre>\nCette fonction retourne deux r\u00e9sultats : l’identifiant d’objet de l’index hypoth\u00e9tique qu’on vient de r\u00e9aliser, et le nom g\u00e9n\u00e9r\u00e9 pour cet index. Ne reste plus qu’\u00e0 faire un test en relancant notre explain pour voir si PostgreSQL utilise bien notre nouvel index.<\/p>\n
clients=# EXPLAIN SELECT val from test where id = 1;\r\n QUERY PLAN\r\n------------------------------------------------------------------------------------\r\n Index Scan using "<13369>btree_test_id" on test (cost=0.04..8.06 rows=1 width=10)\r\n Index Cond: (id = 1)\r\n(2 rows)<\/pre>\nOn voit alors bien que le plan d’ex\u00e9cution utilise notre index hypoth\u00e9tique, et que celui-ci nous fait gagner en performance.
\nAttention, HypoPG ne fonctionne pas avec un EXPLAIN ANALYZE, car l’index n’existe pas, il ne peut donc pas \u00eatre utilis\u00e9. Si on vient \u00e0 vouloir faire un analyze sur notre requ\u00eate pr\u00e9c\u00e9dente, on se rendra rapidement compte qu’il n’est fait mention de notre index nulle part.<\/p>\n
clients=# EXPLAIN ANALYZE SELECT val FROM test WHERE id = 1;\r\n QUERY PLAN\r\n--------------------------------------------------------------------------------------------------\r\n Seq Scan on test (cost=0.00..1791.00 rows=1 width=10) (actual time=0.017..6.983 rows=1 loops=1)\r\n Filter: (id = 1)\r\n Rows Removed by Filter: 99999\r\n Planning Time: 0.048 ms\r\n Execution Time: 7.071 ms\r\n(5 rows)<\/pre>\nL’extension comporte plusieurs autres fonctions qui pourraient vous \u00eatre utiles lors de son utilisation :<\/p>\n
– Pour voir la liste des index hypoth\u00e9tiques que vous avez cr\u00e9\u00e9 vous pouvez utiliser la vue hypopg_list_indexes ou la fonction hypopg()<\/p>\n
clients=# select * from hypopg();\r\n-[ RECORD 1 ]+---------------------\r\nindexname | <13369>btree_test_id\r\nindexrelid | 13369\r\nindrelid | 16398\r\ninnatts | 1\r\nindisunique | f\r\nindkey | 1\r\nindcollation | 0\r\nindclass | 1978\r\nindoption |\r\nindexprs |\r\nindpred |\r\namid | 403\r\n<\/pre>\n– Si vous voulez cr\u00e9er r\u00e9ellement un index hypoth\u00e9tique que vous avez essay\u00e9, mais que vous n’avez bien la requ\u00eate qui vous a permis de le cr\u00e9er, vous pouvez utliliser la fonction hypopg_get_indexdef(oid) en passant en param\u00e8tre l’oid de votre index hypoth\u00e9tique, 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\u00e9tiques :<\/p>\n
clients=# SELECT *, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;\r\n-[ RECORD 1 ]-------+---------------------------------------------\r\nindexrelid | 13369\r\nindex_name | <13369>btree_test_id\r\nschema_name | public\r\ntable_name | test\r\nam_name | btree\r\nhypopg_get_indexdef | CREATE INDEX ON public.test USING btree (id)\r\n<\/pre>\n– La fonction hypopg_relation_size(oid) permet d’\u00e9valuer la taille que ferait l’index hypoth\u00e9tique si il \u00e9tait r\u00e9ellement cr\u00e9\u00e9 :<\/p>\n
clients=# SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))\r\n FROM hypopg_list_indexes ;\r\n-[ RECORD 1 ]--+---------------------\r\nindex_name | <13369>btree_test_id\r\npg_size_pretty | 2544 kB\r\n<\/pre>\n– La fonction hypopg_drop_index(oid) qui permet de supprimer un index hypoth\u00e9tique existant et la fonction hypopg_reset() qui supprime d’un coup tout les index hypoth\u00e9tique poss\u00e9d\u00e9s.<\/p>\n
clients=# select * from hypopg_reset();\r\n-[ RECORD 1 ]+-\r\nhypopg_reset |\r\n<\/pre>\nCette petite extension est donc tr\u00e8s pratique quand on cherche \u00e0 tester des index sans vouloir r\u00e9server de l’espace pour ceux-ci dans la base de donn\u00e9e. Facile d’utilisation, elle pourrait bien devenir le prochain alli\u00e9 de vos performances.<\/p>\n![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a><\/a>","protected":false},"excerpt":{"rendered":"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\u00e9velopp\u00e9e et maintenue par plusieurs personnes de la communaut\u00e9 PostgreSQL. Elle sert notamment \u00e0… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":41,"featured_media":9313,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[424,222,221],"class_list":["post-9285","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-extension","tag-index","tag-performance"],"yoast_head":"\nHypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"HypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"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\u00e9velopp\u00e9e et maintenue par plusieurs personnes de la communaut\u00e9 PostgreSQL. Elle sert notamment \u00e0… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-05T09:35:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-16T07:35:18+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/08\/1_lGw7Yu8Aus3ek5cO_5yn6Q.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1600\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sarah FAVEERE\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sarah FAVEERE\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\"},\"author\":{\"name\":\"Sarah FAVEERE\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2\"},\"headline\":\"HypoPG et les index hypoth\u00e9tiques\",\"datePublished\":\"2022-08-05T09:35:13+00:00\",\"dateModified\":\"2022-08-16T07:35:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\"},\"wordCount\":2709,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"extension\",\"index\",\"performance\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\",\"name\":\"HypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2022-08-05T09:35:13+00:00\",\"dateModified\":\"2022-08-16T07:35:18+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"HypoPG et les index hypoth\u00e9tiques\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2\",\"name\":\"Sarah FAVEERE\",\"sameAs\":[\"http:\/\/blog.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"HypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/","og_locale":"fr_FR","og_type":"article","og_title":"HypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG","og_description":"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\u00e9velopp\u00e9e et maintenue par plusieurs personnes de la communaut\u00e9 PostgreSQL. Elle sert notamment \u00e0… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2022-08-05T09:35:13+00:00","article_modified_time":"2022-08-16T07:35:18+00:00","og_image":[{"width":1600,"height":480,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/08\/1_lGw7Yu8Aus3ek5cO_5yn6Q.jpeg","type":"image\/jpeg"}],"author":"Sarah FAVEERE","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Sarah FAVEERE","Dur\u00e9e de lecture estim\u00e9e":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/"},"author":{"name":"Sarah FAVEERE","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2"},"headline":"HypoPG et les index hypoth\u00e9tiques","datePublished":"2022-08-05T09:35:13+00:00","dateModified":"2022-08-16T07:35:18+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/"},"wordCount":2709,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["extension","index","performance"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/","url":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/","name":"HypoPG et les index hypoth\u00e9tiques - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2022-08-05T09:35:13+00:00","dateModified":"2022-08-16T07:35:18+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/hypopg-et-les-index-hypothetiques\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"HypoPG et les index hypoth\u00e9tiques"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2","name":"Sarah FAVEERE","sameAs":["http:\/\/blog.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=9285"}],"version-history":[{"count":10,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9285\/revisions"}],"predecessor-version":[{"id":9308,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9285\/revisions\/9308"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/9313"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=9285"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=9285"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=9285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}