mirror of
https://github.com/HypoPG/hypopg
synced 2026-05-24 09:38:21 +00:00
93 lines
3.6 KiB
Markdown
93 lines
3.6 KiB
Markdown
HypoPG
|
|
=======
|
|
|
|
HypoPG is a PostgreSQL extension adding support for hypothetical indexes.
|
|
|
|
An hypothetical, or virtual, index is an index that doesn't really exists, and
|
|
thus doesn't cost CPU, disk or any resource to create. They're useful to know
|
|
if specific indexes can increase performance for problematic queries, since
|
|
you can know if PostgreSQL will use these indexes or not without having to
|
|
spend resources to create them.
|
|
|
|
For more thorough informations, pease consult the [oficial
|
|
documentation](https://hypopg.readthedocs.io).
|
|
|
|
For other general informations, you can also consult [this blog
|
|
post](https://rjuju.github.io/postgresql/2015/07/02/how-about-hypothetical-indexes.html).
|
|
|
|
Installation
|
|
------------
|
|
|
|
- Compatible with PostgreSQL 9.2 and above
|
|
- Needs PostgreSQL header files
|
|
- decompress the tarball
|
|
- sudo make install
|
|
- In every needed database: CREATE EXTENSION hypopg;
|
|
|
|
Usage
|
|
-----
|
|
|
|
NOTE: The hypothetical indexes are contained in a single backend. Therefore,
|
|
if you add multiple hypothetical indexes, concurrent connexions doing
|
|
EXPLAIN won't be bothered by your hypothetical indexes.
|
|
|
|
Assuming a simple test case:
|
|
|
|
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)
|
|
|
|
|
|
The easiest way to create an hypothetical index is to use the
|
|
**hypopg_create_index** functions with a regular CREATE INDEX statement as arg.
|
|
|
|
For instance:
|
|
|
|
rjuju=# SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
|
|
|
|
NOTE: Some information of the CREATE INDEX statement will be ignored, such as
|
|
the index name if provided. Some of ignored informations will be handled in
|
|
future release.
|
|
|
|
You can check the available hypothetical indexes in your own backend:
|
|
|
|
rjuju=# SELECT * FROM hypopg_list_indexes();
|
|
indexrelid | indexname | nspname | relname | amname
|
|
-----------+-------------------------------------------+---------+---------+--------
|
|
205101 | <41072>btree_hypo_id | public | hypo | btree
|
|
|
|
|
|
If you need more technical informations on the hypothetical indexes, the
|
|
hypopg() function will return the hypothetical indexes in a similar way as
|
|
pg_index system catalog.
|
|
|
|
And now, let's see if your previous EXPLAIN statement would use such an 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)
|
|
|
|
|
|
Of course, only EXPLAIN without analyze will use hypothetical indexes:
|
|
|
|
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)
|
|
|
|
To remove your backend's hypothetical indexes, you can use the function
|
|
**hypopg_drop_index(indexrelid)** with the OID that **hypopg_list_indexes()**
|
|
function returns, call **hypopg_reset()** to remove all at once or just close
|
|
your current connection.
|