hypopg

Hypopg

HYPOPG est une extension postgresql ajoutant une prise en charge des index hypothétiques.

Un index hypothétique - ou virtuel - est un index qui n'existe pas vraiment, et ne coûte donc pas le processeur, le disque ou toute ressource à créer. Ils sont utiles pour savoir si des indices spécifiques peuvent augmenter les performances des requêtes problématiques, car vous pouvez savoir si PostgreSQL utilisera ces index ou non sans avoir à dépenser des ressources pour les créer.

Pour des informations plus approfondies, veuillez consulter la documentation officielle.

Pour d'autres informations générales, vous pouvez également consulter cet article de blog.

Installation

• Compatible avec PostgreSQL 9.2 et plus
• A besoin de fichiers d'en-tête postgresql
• Décompressez le tarball
• sudo make install
• Dans toutes les bases de données nécessaires: CREATE EXTENSION hypopg;

Mise à jour de l'extension

Notez que HYPOPG ne fournit pas de scripts de mise à niveau d'extension, car il n'y a pas de données enregistrées dans aucun des objets créés. Par conséquent, vous devez d'abord supprimer l'extension, puis la créer à nouveau pour obtenir la nouvelle version.

Usage

Remarque: les indices hypothétiques sont contenus dans un seul backend. Par conséquent, si vous ajoutez plusieurs index hypothétiques, des connexions simultanées faisant EXPLAIN ne seront pas dérangées par vos index hypothétiques.

En supposant un simple cas de test:

 rjuju=# CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id;
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13)
   Filter: (id = 1)
(2 rows)

La façon la plus simple de créer un index hypothétique consiste à utiliser les fonctions hypopg_create_index avec une instruction CREATE INDEX régulière comme arg.

Par exemple:

 rjuju=# SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');

Remarque: certaines informations de l'instruction CREATE INDEX seront ignorées, telles que le nom d'index si elles sont fournies. Certaines des informations ignorées seront gérées dans une future version.

Vous pouvez vérifier les index hypothétiques disponibles dans votre propre backend:

 rjuju=# SELECT * FROM hypopg_list_indexes ;
 indexrelid |          index_name           | schema_name | table_name | am_name 
------------+-------------------------------+-------------+------------+---------
      50573 | <50573>btree_hypo_id          | public      | hypo       | btree

Si la commande CREATE INDEX vous souhaitez utiliser doit également être citée, l'utilisation de la syntaxe de citation d'un dollar est recommandée. Par exemple:

 rjuju=# SELECT * FROM hypopg_create_index($$CREATE INDEX ON hypo (id) WHERE val = 'line 1'$$);

Si vous avez besoin de plus d'informations techniques sur les index hypothétiques, la fonction hypopg() renvoie les index hypothétiques de la même manière que le catalogue système pg_index .

Et maintenant, voyons si votre instruction EXPLAIN précédente utiliserait un tel index:

 rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using <41072>hypo_btree_hypo_id on hypo  (cost=0.29..8.30 rows=1 width=13)
   Index Cond: (id = 1)
(2 rows)

Bien sûr, EXPLAIN uniquement sans ANALYZE utilisera des indices hypothétiques:

 rjuju=# EXPLAIN ANALYZE SELECT * FROM hypo WHERE id = 1;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13) (actual time=0.036..6.072 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 9999
 Planning time: 0.109 ms
 Execution time: 6.113 ms
(5 rows)

Pour supprimer les index hypothétiques de votre backend, vous pouvez utiliser la fonction hypopg_drop_index(indexrelid) avec l'OID que la vue hypopg_list_indexes renvoie et appelez hypopg_reset() pour supprimer tout en une fois, ou tout simplement fermer votre connexion actuelle.

Poursuivant avec le cas ci-dessus, vous pouvez hide existing indexes , mais vous devez utiliser hypopg_reset() pour effacer les effets précédents d'autres index au début.

Créez deux index réels et exécutez EXPLAIN :

 rjuju=# SELECT hypopg_reset();
rjuju=# CREATE INDEX ON hypo(id);
rjuju=# CREATE INDEX ON hypo(id, val);
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using hypo_id_val_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

Le plan de requête utilise l'index hypo_id_val_idx . Utilisez hypopg_hide_index(oid) pour masquer l'un des index:

 rjuju=# SELECT hypopg_hide_index('hypo_id_val_idx'::REGCLASS);
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

Le plan de requête utilise maintenant l'autre index hypo_id_idx . Utilisez hypopg_hide_index(oid) pour le masquer:

 rjuju=# SELECT hypopg_hide_index('hypo_id_idx'::REGCLASS);
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                    QUERY PLAN
-------------------------------------------------------
Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13)
Filter: (id = 1)
(2 rows)

Et maintenant, le plan de requête revient en Seq Scan . Utilisez hypopg_unhide_index(oid) pour restaurer l'index:

 rjuju=# SELECT hypopg_unhide_index('hypo_id_idx'::regclass);
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

Bien sûr, vous pouvez également cacher des index hypothétiques:

 rjuju=# SELECT hypopg_create_index('CREATE INDEX ON hypo(id)');
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using "<12659>btree_hypo_id" on hypo  (cost=0.04..8.05 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

rjuju=# SELECT hypopg_hide_index(12659);
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1;
                    QUERY PLAN
-------------------------------------------------------
Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13)
Filter: (id = 1)
(2 rows)

Vous pouvez vérifier les index cachés à l'aide de hypopg_hidden_indexes() ou de la vue hypopg_hidden_indexes :

 rjuju=# SELECT * FROM hypopg_hidden_indexes();
indexid
---------
526604
526603
12659
(3 rows)

rjuju=# SELECT * FROM hypopg_hidden_indexes;
 indexrelid |      index_name      | schema_name | table_name | am_name | is_hypo
------------+----------------------+-------------+------------+---------+---------
      12659 | <12659>btree_hypo_id | public      | hypo       | btree   | t
     526603 | hypo_id_idx          | public      | hypo       | btree   | f
     526604 | hypo_id_val_idx      | public      | hypo       | btree   | f
(3 rows)

Pour restaurer tous les index existants, vous pouvez utiliser la fonction hypopg_unhide_all_indexes() . Notez que les fonctionnalités pour masquer les index existantes ne s'appliquent qu'à la commande Explication dans la session en cours et n'affecteront pas d'autres sessions.