Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use PostgreSQL's Hypothetical Indexes". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What is Hypothetical Indexes

Hypothetical Indexes literally translates as "hypothetical index", which is relative to "physical index". It can be understood as an index that assumes to exist but actually does not exist physically, and its function lies in the adjustment and optimization of SQL. In the test environment, when the amount of data is not too large, SQL can be tuned by adding the actual index, but in the production environment, because adding the index will affect the normal operation of the business and the database, it is necessary to use the technology of Hypothetical Indexes to assume that the index exists. After adding Hypothetical Indexes, observe and verify the changes in the execution plan, such as the added index is expected to meet the requirements, then the physical index is actually added. Therefore, the cost of the test is effectively reduced.

Install

Download the source code on Github, put it in the contrib directory, compile & install

[root@localhost contrib] # cd hypopg-1.1.3/ [root@localhost hypopg-1.1.3] # lsCHANGELOG.md debian expected hypopg.c hypopg_index.c include Makefile README.md TODO.mdCONTRIBUTORS.md docs hypopg--1.1.3.sql hypopg.control import LICENSE META.json test typedefs.list [root@localhost hypopg-1.1.3] # makegcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith -Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-I. -I. /-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-o hypopg.o hypopg.c-MMD-MP-MF .deps / hypopg.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g- O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-I. -I. /-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-o hypopg_index.o hypopg_index.c-MMD-MP-MF .deps / hypopg_index.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv- Fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-I. -I. /-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-o import/hypopg_import.o import/hypopg_import.c-MMD-MP-MF .deps / hypopg_import.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict- Aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-I. -I. /-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-o import/hypopg_import_index.o import/hypopg_import_index.c-MMD-MP-MF .deps / hypopg_import_index.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat- Security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-shared-o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o-L/appdb/xdb/pg12beta1/lib-Wl -- as-needed-Wl,-rpath,'/appdb/xdb/pg12beta1/lib' -- enable-new-dtags [root@localhost hypopg-1.1.3] # make install/usr/bin/mkdir-p'/ appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/mkdir-p'/ appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/ appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/install-c-m 755 hypopg.so'/ appdb/xdb/pg12beta1/lib/postgresql/hypopg.so'/usr/ Bin/install-c-m 644. / / hypopg.control'/ appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m 644. / / hypopg--1.1.3.sql'/ appdb/xdb/pg12beta1/share/postgresql/extension/'

Create extension

Testdb=# create schema pgextensions;CREATE SCHEMAtestdb=# CREATE EXTENSION hypopg WITH SCHEMA pgextensions;CREATE EXTENSION

Hypopg extension

First create a test table

Testdb=# create table t_hypopg (id int,c1 varchar (20)); CREATE TABLEtestdb=# insert into t_hypopg select x Magneto insert c1' | x from generate_series (1m 100000) as x tern insert 0 100000

Hypopg extension provides eight functions:

Testdb=# select proname from pg_proc where pronamespace INtestdb-# (select oid from pg_namespace where nspname = 'pgextensions'); proname-- hypopg_reset_index hypopg_reset hypopg_create_index hypopg_drop_index hypopg hypopg_list_indexes hypopg_relation_size hypopg_get_indexdef (8 rows)

1.hypopg_create_index-create an index

Testdb=# SELECT pgextensions.hypopg_create_index ('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE (id)'); indexrelid | indexname-+-- 99425 | btree_t_hypopg_id (1 row) testdb=# SELECT pgextensions.hypopg_create_index ('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE (id)') Hypopg_create_index-- (99426) (1 row)

2.hypopg_drop_index-Delete the index

Testdb=# select pgextensions.hypopg_drop_index (99425); hypopg_drop_index-t (1 row)

3.hypopg_list_indexes-list index information

Testdb=# select pgextensions.hypopg_list_indexes () psql: ERROR: function hypopg () does not existLINE 3: FROM hypopg () h ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.QUERY: SELECT h.indexrelid, h.indexname, n.nspname, c.relname, am.amname FROM hypopg () h JOIN pg_class c ON c.oid = h.indrelid JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_am am ON am.oid = h.amidCONTEXT: SQL function "hypopg_list_indexes" during startuptestdb=# set search_path = "$user", public, pgextensions;SETtestdb=# select pgextensions.hypopg_list_indexes () Hypopg_list_indexes-(99426) (1 row)

4.hypopg_get_indexdef-list index definition

Testdb=# select hypopg_get_indexdef (99426); hypopg_get_indexdef-CREATE INDEX ON public.t_hypopg USING btree (id) (1 row)

5.hypopg_reset_index-Delete the index

Testdb=# select hypopg_reset_index (); hypopg_reset_index-(1 row)

6.hypopg_reset-Delete all indexes

Testdb=# select hypopg_reset (); hypopg_reset-(1 row) testdb=# select pgextensions.hypopg_list_indexes (); hypopg_list_indexes-(0 rows) testdb=# SELECT hypopg_create_index ('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE (id)') Hypopg_create_index-- (99427) (1 row)

7.hypopg-lists the raw information of the index

Testdb=# select hypopg (); hypopg-- (btree_t_hypopg_id,99427,99422,1,f,1,0,1978,403) (1 row)

8.hypopg_relation_size-estimate index size

Testdb=# select hypopg_relation_size (99427); hypopg_relation_size-2605056 (1 row) testdb=# select pg_size_pretty (hypopg_relation_size (99427)); pg_size_pretty-2544 kB (1 row)

Practical use

Execute the query without an index

Testdb=# select hypopg_reset (); hypopg_reset-(1 row) testdb=# explain verbose select * from t_hypopg where id = 1000 QUERY PLAN-Seq Scan on public.t_hypopg (cost=0.00..1791.00 rows=1 width=11) Output: id C1 Filter: (t_hypopg.id = 1000) (3 rows)

PG uses sequential scanning

Create Hypothetical Index: idx_t_hypopg_id, and use explain again to check the execution plan of the query statement:

Testdb=# SELECT hypopg_create_index ('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE (id)'); hypopg_create_index-- (99429) (1 row) testdb=# testdb=# explain verbose select * from t_hypopg where id = 1000 QUERY PLAN -Index Scan using btree_t_hypopg_id on public.t_hypopg (cost=0.04..8.06 rows=1 width=11) Output: id C1 Index Cond: (t_hypopg.id = 1000) (3 rows)

You can view the execution plan after index creation without actually creating the index, which is the value of Hypothetical Indexes.

It is worth noting that if explain uses the analyze option, Hypothetical Indexes has no effect.

Testdb=# explain analyze select * from t_hypopg where id = 1000 QUERY PLAN -- Seq Scan on t_hypopg (cost=0.00..1791.00 rows=1 width=11) (actual time=2.544..98.130 rows=1 loops=1) Filter: (id = 1000) Rows Removed by Filter: 99999 Planning Time: 1.341 ms Execution Time: 98.193 ms (5 rows) "how to use PostgreSQL's Hypothetical Indexes" ends here Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.