怎么使用PostgreSQL的Hypothetical Indexes

本篇內容介紹了“怎么使用PostgreSQL的Hypothetical Indexes”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

What is Hypothetical Indexes
Hypothetical Indexes直譯為”假設索引”,是相對于”物理索引”而言的,可以理解為假設存在但實際上物理不存在的索引,其作用在于對SQL的調整和優化.在測試環境,數據量不太大的情況下,可以通過添加實際的索引來對SQL進行調優,但在生產環境,由于添加索引會影響業務和數據庫的正常運行,因此需要使用Hypothetical Indexes這種技術假設索引存在,在添加Hypothetical Indexes后,通過觀察驗證執行計劃的變化,如添加的索引合符期望滿足需求,則實際添加物理索引,因此有效的降低了試驗的成本.

Install
在Github上下載源碼,放在contrib目錄下,編譯&安裝

[root@localhost contrib]# cd hypopg-1.1.3/
[root@localhost hypopg-1.1.3]# ls
CHANGELOG.md     debian  expected           hypopg.c        hypopg_index.c  include  Makefile   README.md  TODO.md
CONTRIBUTORS.md  docs    hypopg--1.1.3.sql  hypopg.control  import          LICENSE  META.json  test       typedefs.list
[root@localhost hypopg-1.1.3]# make
gcc -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.Po
gcc -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.Po
gcc -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.Po
gcc -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.Po
gcc -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/'

創建extension

testdb=# create schema pgextensions;
CREATE SCHEMA
testdb=# CREATE EXTENSION hypopg WITH SCHEMA pgextensions;
CREATE EXTENSION

hypopg extension
首先創建測試表

testdb=# create table t_hypopg(id int,c1 varchar(20));
CREATE TABLE
testdb=# insert into t_hypopg select x,'c1'||x from generate_series(1,100000) as x;
INSERT 0 100000

hypopg extension提供了8個函數:

testdb=# select proname from pg_proc where pronamespace IN
testdb-# (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 - 創建索引

testdb=# SELECT pgextensions.hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
 indexrelid |        indexname         
------------+--------------------------
      99425 | <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,<99426>btree_t_hypopg_id)
(1 row)

2.hypopg_drop_index - 刪除索引

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

3.hypopg_list_indexes - 列出索引信息

testdb=# select pgextensions.hypopg_list_indexes();
psql: ERROR:  function hypopg() does not exist
LINE 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.amid
CONTEXT:  SQL function "hypopg_list_indexes" during startup
testdb=# set search_path = "$user", public, pgextensions;
SET
testdb=# select pgextensions.hypopg_list_indexes();
                  hypopg_list_indexes                   
--------------------------------------------------------
 (99426,<99426>btree_t_hypopg_id,public,t_hypopg,btree)
(1 row)

4.hypopg_get_indexdef — 列出索引定義

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

5.hypopg_reset_index — 刪除索引

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

6.hypopg_reset - 刪除所有的索引

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,<99427>btree_t_hypopg_id)
(1 row)

7.hypopg - 列出索引原始信息

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

8.hypopg_relation_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)

實際使用
在沒有索引的情況下,執行查詢

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使用順序掃描

創建Hypothetical Index : idx_t_hypopg_id,再次使用explain檢查查詢語句的執行計劃:

testdb=# SELECT hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
       hypopg_create_index        
----------------------------------
 (99429,<99429>btree_t_hypopg_id)
(1 row)
testdb=# 
testdb=# explain verbose select * from t_hypopg where id = 1000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Index Scan using <99429>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)

在不需要實際創建索引的情況下可以查看創建索引后的執行計劃,這是Hypothetical Indexes的價值所在.
值得注意的是,如果explain使用analyze選項,則Hypothetical Indexes無效.

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)

“怎么使用PostgreSQL的Hypothetical Indexes”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！