mirror of
https://github.com/HypoPG/hypopg
synced 2026-05-23 17:18:44 +00:00
392 lines
13 KiB
ReStructuredText
392 lines
13 KiB
ReStructuredText
.. _usage:
|
|
|
|
Usage
|
|
=====
|
|
|
|
Introduction
|
|
------------
|
|
|
|
HypoPG is useful if you want to check if some index would help one or multiple
|
|
queries. Therefore, you should already know what are the queries you need to
|
|
optimize, and ideas on which indexes you want to try.
|
|
|
|
Also, the hypothetical indexes that HypoPG will create are not stored in any
|
|
catalog, but in your connection private memory. Therefore, it won't bloat any
|
|
table and won't impact any concurrent connection.
|
|
|
|
Also, since the hypothetical indexes doesn't really exists, HypoPG makes sure
|
|
they will only be used using a simple EXPLAIN statement (without the ANALYZE
|
|
option).
|
|
|
|
Install the extension
|
|
---------------------
|
|
|
|
As any other extension, you have to install it on all the databases where you
|
|
want to be able to use it. This is simply done executing the following query,
|
|
connected on the database you want to install HypoPG with a user having enough
|
|
privileges:
|
|
|
|
.. code-block:: psql
|
|
|
|
CREATE EXTENSION hypopg ;
|
|
|
|
HypoPG is now available. You can check easily if the extension is present
|
|
using `psql <https://www.postgresql.org/docs/current/static/app-psql.html>`_:
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 5
|
|
|
|
\dx
|
|
List of installed extensions
|
|
Name | Version | Schema | Description
|
|
---------+---------+------------+-------------------------------------
|
|
hypopg | 1.1.0 | public | Hypothetical indexes for PostgreSQL
|
|
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
|
|
(2 rows)
|
|
|
|
As you can see, hypopg version 1.1.0 is installed. If you need to check using
|
|
plain SQL, please refer to the `pg_extension table documentation
|
|
<https://www.postgresql.org/docs/current/static/catalog-pg-extension.html>`_.
|
|
|
|
Configuration
|
|
-------------
|
|
|
|
The following configuration parameters (GUCs) are available, and can be changed
|
|
interactively:
|
|
|
|
hypopg.enabled:
|
|
Default to ``on``.
|
|
Use this parameter to globally enable or disable HypoPG. When HypoPG is
|
|
disabled, no hypothetical index will be used, but the defined hypothetical
|
|
indexes won't be removed.
|
|
|
|
hypopg.use_real_oids:
|
|
Default to ``off``.
|
|
By default, HypoPG won't use "real" object identifiers, but instead borrow
|
|
ones from the ~ 14000 / 16384 (respectively the lowest unused oid less then
|
|
FirstNormalObjectId and FirstNormalObjectId) range, which are reserved by
|
|
PostgreSQL for future usage in future releases. This doesn't cause any
|
|
problem, as the free range is dynamically computed the first time a
|
|
connection uses HypoPG, and has the advantage to work on a standby server.
|
|
But the drawback is that you can't have more than approximately 2500
|
|
hypothetical indexes at the same time, and creating a new hypothetical index
|
|
will become very slow once more than the maximum number of objects has been
|
|
created until ``hypopg_reset()`` is called.
|
|
|
|
If those drawbacks are problematic, you can enable this parameter. HypoPG
|
|
will then ask for a real object identifier, which will need to obtain more
|
|
locks and won't work on a standby, but will allow to use the full range of
|
|
object identifiers.
|
|
|
|
Note that switching this parameter doesn't require to reset the entries, both
|
|
can coexist at the same time.
|
|
|
|
Supported access methods
|
|
------------------------
|
|
|
|
The following access methods are supported:
|
|
|
|
- btree
|
|
- brin
|
|
- hash (requires PostgreSQL 10 or above)
|
|
- bloom (requires the bloom extension to be installed)
|
|
|
|
Create a hypothetical index
|
|
---------------------------
|
|
|
|
.. note::
|
|
|
|
Using HypoPG require some knowledge on the **EXPLAIN** command. If you need
|
|
more information about this command, you can check `the official
|
|
documentation
|
|
<https://www.postgresql.org/docs/current/static/using-explain.html>`_. There
|
|
are also a lot of very good resources available.
|
|
|
|
For clarity, let's see how it works with a very simple test case:
|
|
|
|
.. code-block:: psql
|
|
|
|
CREATE TABLE hypo (id integer, val text) ;
|
|
INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i ;
|
|
VACUUM ANALYZE hypo ;
|
|
|
|
This table doesn't have any index. Let's assume we want to check if an index
|
|
would help a simple query. First, let's see how it behaves:
|
|
|
|
.. code-block:: psql
|
|
|
|
EXPLAIN SELECT val FROM hypo WHERE id = 1;
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on hypo (cost=0.00..1791.00 rows=1 width=14)
|
|
Filter: (id = 1)
|
|
(2 rows)
|
|
|
|
A plain sequential scan is used, since no index exists on the table. A simple
|
|
btree index on the **id** column should help this query. Let's check with
|
|
HypoPG. The function **hypopg_create_index()** will accept any standard
|
|
**CREATE INDEX** statement(s) (any other statement passed to this function will be
|
|
ignored), and create a hypothetical index for each:
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
|
|
indexrelid | indexname
|
|
------------+----------------------
|
|
18284 | <18284>btree_hypo_id
|
|
(1 row)
|
|
|
|
The function returns two columns:
|
|
|
|
- the object identifier of the hypothetical index
|
|
- the generated hypothetical index name
|
|
|
|
We can run the EXPLAIN again to see if PostgreSQL would use this index:
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 4
|
|
|
|
EXPLAIN SELECT val FROM hypo WHERE id = 1;
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------
|
|
Index Scan using <18284>btree_hypo_id on hypo (cost=0.04..8.06 rows=1 width=10)
|
|
Index Cond: (id = 1)
|
|
(2 rows)
|
|
|
|
Yes, PostgreSQL would use such an index. Just to be sure, let's check that the
|
|
hypothetical index won't be used to acually run the query:
|
|
|
|
.. code-block:: psql
|
|
|
|
EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;
|
|
QUERY PLAN
|
|
---------------------------------------------------------------------------------------------------
|
|
Seq Scan on hypo (cost=0.00..1791.00 rows=1 width=10) (actual time=0.046..46.390 rows=1 loops=1)
|
|
Filter: (id = 1)
|
|
Rows Removed by Filter: 99999
|
|
Planning time: 0.160 ms
|
|
Execution time: 46.460 ms
|
|
(5 rows)
|
|
|
|
That's all you need to create hypothetical indexes and see if PostgreSQL would
|
|
use such indexes.
|
|
|
|
Manipulate hypothetical indexes
|
|
-------------------------------
|
|
|
|
Some other convenience functions and views are available:
|
|
|
|
- **hypopg_list_indexes**: view that lists all hypothetical indexes that have
|
|
been created
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT * FROM hypopg_list_indexes ;
|
|
indexrelid | index_name | schema_name | table_name | am_name
|
|
------------+-----------------------+-------------+------------+---------
|
|
18284 | <18284>btree_hypo_id | public | hypo | btree
|
|
(1 row)
|
|
|
|
- **hypopg()**: function that lists all hypothetical indexes that have
|
|
been created with the same format as **pg_index**
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT * FROM hypopg() ;
|
|
indexname | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
|
|
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
|
|
<18284>btree_hypo_id | 13543 | 18122 | 1 | f | 1 | 0 | 1978 | <NULL> | <NULL> | <NULL> | 403
|
|
(1 row)
|
|
|
|
- **hypopg_get_indexdef(oid)**: function that lists the CREATE INDEX statement
|
|
that would recreate a stored hypothetical index
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;
|
|
index_name | hypopg_get_indexdef
|
|
-----------------------+----------------------------------------------
|
|
<18284>btree_hypo_id | CREATE INDEX ON public.hypo USING btree (id)
|
|
(1 row)
|
|
|
|
- **hypopg_relation_size(oid)**: function that estimates how big a hypothetical
|
|
index would be:
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
|
|
FROM hypopg_list_indexes ;
|
|
index_name | pg_size_pretty
|
|
-----------------------+----------------
|
|
<18284>btree_hypo_id | 2544 kB
|
|
(1 row)
|
|
|
|
- **hypopg_drop_index(oid)**: function that removes the given hypothetical
|
|
index
|
|
- **hypopg_reset()**: function that removes all hypothetical indexes
|
|
|
|
Hypothetically hide existing indexes
|
|
------------------------------------
|
|
|
|
You can hide both existing and hypothetical indexes hypothetically.
|
|
If you want to test it as described in the documentation,
|
|
you should first use **hypopg_reset()** to clear the effects of any other hypothetical indexes.
|
|
|
|
As a simple case, let's consider two indexes:
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT hypopg_reset();
|
|
CREATE INDEX ON hypo(id);
|
|
CREATE INDEX ON hypo(id, val);
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 4
|
|
|
|
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)
|
|
|
|
The query plan is using the **hypo_id_val_idx** index now.
|
|
|
|
- **hypopg_hide_index(oid)**: function that allows you to hide an index in the EXPLAIN output by using its OID.
|
|
It returns `true` if the index was successfully hidden, and `false` otherwise.
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 10
|
|
|
|
SELECT hypopg_hide_index('hypo_id_val_idx'::REGCLASS);
|
|
hypopg_hide_index
|
|
-------------------
|
|
t
|
|
(1 row)
|
|
|
|
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)
|
|
|
|
As an example, let's assume that the query plan is currently using the **hypo_id_val_idx** index.
|
|
To continue testing, use the **hypopg_hide_index(oid)** function to hide another index.
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 10
|
|
|
|
SELECT hypopg_hide_index('hypo_id_idx'::REGCLASS);
|
|
hypopg_hide_index
|
|
-------------------
|
|
t
|
|
(1 row)
|
|
|
|
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)
|
|
|
|
- **hypopg_unhide_index(oid)**: function that restore a previously hidden index in the EXPLAIN output by using its OID.
|
|
It returns `true` if the index was successfully restored, and `false` otherwise.
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 10
|
|
|
|
SELECT hypopg_unhide_index('hypo_id_idx'::regclass);
|
|
hypopg_unhide_index
|
|
-------------------
|
|
t
|
|
(1 row)
|
|
|
|
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)
|
|
|
|
- **hypopg_unhide_all_index()**: function that restore all hidden indexes and returns void.
|
|
|
|
- **hypopg_hidden_indexes()**: function that returns a list of OIDs for all hidden indexes.
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT * FROM hypopg_hidden_indexes();
|
|
indexid
|
|
---------
|
|
526604
|
|
(1 rows)
|
|
|
|
- **hypopg_hidden_indexes**: view that returns a formatted list of all hidden indexes.
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT * FROM hypopg_hidden_indexes;
|
|
indexrelid | index_name | schema_name | table_name | am_name | is_hypo
|
|
-------------+----------------------+-------------+------------+---------+---------
|
|
526604 | hypo_id_val_idx | public | hypo | btree | f
|
|
(1 rows)
|
|
|
|
.. note::
|
|
|
|
Hypothetical indexes can be hidden as well.
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 10
|
|
|
|
SELECT hypopg_create_index('CREATE INDEX ON hypo(id)');
|
|
hypopg_create_index
|
|
------------------------------
|
|
(12659,<12659>btree_hypo_id)
|
|
(1 row)
|
|
|
|
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)
|
|
|
|
Now that the hypothetical index is being used, we can try hiding it to see the change:
|
|
|
|
.. code-block:: psql
|
|
:emphasize-lines: 10
|
|
|
|
SELECT hypopg_hide_index(12659);
|
|
hypopg_hide_index
|
|
-------------------
|
|
t
|
|
(1 row)
|
|
|
|
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)
|
|
|
|
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
|
|
526604 | hypo_id_val_idx | public | hypo | btree | f
|
|
(2 rows)
|
|
|
|
.. note::
|
|
|
|
If a hypothetical index has been hidden, it will be automatically unhidden
|
|
when it is deleted using **hypopg_drop_index(oid)** or **hypopg_reset()**.
|
|
|
|
.. code-block:: psql
|
|
|
|
SELECT hypopg_drop_index(12659);
|
|
|
|
SELECT * FROM hypopg_hidden_indexes;
|
|
indexrelid | index_name | schema_name | table_name | am_name | is_hypo
|
|
-------------+----------------------+-------------+------------+---------+---------
|
|
526604 | hypo_id_val_idx | public | hypo | btree | f
|
|
(2 rows)
|