Hypothetical Indexes for PostgreSQL
Clone or download
Latest commit 8424300 Dec 11, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
debian Version 1.1.2 May 30, 2018
docs Fix typo in documentation. Dec 4, 2018
expected Check for (hypo) constraint compatibility with (hypo) partitioning Dec 9, 2018
include Fix hypothetical partitioning expansion for pg10 Dec 3, 2018
test/sql Check for (hypo) constraint compatibility with (hypo) partitioning Dec 9, 2018
.gitignore Add some basic regression test. Jul 11, 2015
CHANGELOG.md Add changelog for upcoming version 2.0.0 Dec 4, 2018
CONTRIBUTORS.md Use original postgresql license, thanks to Christoph Berg for noticing. Mar 26, 2018
LICENSE Use original postgresql license, thanks to Christoph Berg for noticing. Mar 26, 2018
META.json Change all references to point to new upstream Feb 23, 2018
Makefile Reintroduce childrel expanding order for pg10 Nov 23, 2018
README.md Mention the documentation in the main README file. Mar 24, 2018
TODO.md Add a hypopg_get_indexdef(oid) function Nov 16, 2016
hypopg--1.1.2.sql Start working on version 1.1.2 May 28, 2018
hypopg--2.0.0beta.sql Stamp 2.0.0beta Dec 11, 2018
hypopg.c Only use set_rel_pathlist_hook for pg10. Nov 24, 2018
hypopg.control Stamp 2.0.0beta Dec 11, 2018
hypopg_analyze.c Fix hypo_get_partition_constraints to follow upstream behavior. Nov 24, 2018
hypopg_import.c Start working on hypothetical partitioning pg10 compatibility. Nov 17, 2018
hypopg_index.c Check for (hypo) constraint compatibility with (hypo) partitioning Dec 9, 2018
hypopg_table.c Check for (hypo) constraint compatibility with (hypo) partitioning Dec 9, 2018
typedefs.list Import and tweak typedefs.list, needed for pgindent. Jun 23, 2015

README.md

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.

For other general informations, you can also consult this blog post.

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.