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, please consult the [official
|
|
documentation](https://hypopg.readthedocs.io).
|
|
|
|
For other general information, 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 connections 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 from the `CREATE INDEX` statement will be ignored, such as
|
|
the index name if provided. Some of the ignored information will be handled in
|
|
a 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 information 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 the `hypopg_list_indexes()`
|
|
function returns and call `hypopg_reset()` to remove all at once, or just close
|
|
your current connection.
|