: 世界上功能最强大的开源数据库...

snsgs	pg_hint_plan插件使用导致crash ... ... 2019-05-11 15:33:45+08...1楼
	#PG实现索引提示 ####全表扫描场景 dbmspgdb=# create table sgs(id int); CREATE TABLE dbmspgdb=# create index ixsgs on sgs(id); CREATE INDEX dbmspgdb=# insert into sgs SELECT * from generate_series(1, 10); INSERT 0 10 dbmspgdb=# analyze sgs; ANALYZE dbmspgdb=# explain select * from sgs where id=1; ---由于记录少，优化器选择全表扫 QUERY PLAN --------------------------------------------------- Seq Scan on sgs (cost=0.00..1.12 rows=1 width=4) Filter: (id = 1) (2 rows) ####插件安装创建extension dbmspgdb=# create extension pg_hint_plan; CREATE EXTENSION 修改数据库参数 dbmspgdb=# show shared_preload_libraries ; shared_preload_libraries ------------------------------------------------------------- citus,pg_stat_statements,auth_delay,pg_pathman,pg_hint_plan (1 row) 或者会话级别加载 dbmspgdb=# load 'pg_hint_plan'; LOAD ####强制索引 dbmspgdb=# /+ IndexScan(sgs) / explain select * from sgs where id=1; QUERY PLAN ----------------------------------------------------------------- Index Scan using ixsgs on sgs (cost=0.14..3.15 rows=1 width=4) Index Cond: (id = 1) (2 rows) dbmspgdb=# /+ seqScan(sgs) / explain select * from sgs where id=1; QUERY PLAN --------------------------------------------------- Seq Scan on sgs (cost=0.00..1.12 rows=1 width=4) Filter: (id = 1) (2 rows) ####强制走某个索引 dbmspgdb=# \d sgs Table "public.sgs" Column \| Type \| Collation \| Nullable \| Default --------+-----------------------+-----------+----------+--------- id \| integer \| \| \| name \| character varying(20) \| \| \| Indexes: "ix1" btree (id) "ix2" btree (id, name) dbmspgdb=# explain select * from sgs where id=1; QUERY PLAN ---------------------------------------------------- Seq Scan on sgs (cost=0.00..1.12 rows=1 width=62) Filter: (id = 1) (2 rows) dbmspgdb=# /+ IndexScan(sgs) / explain select * from sgs where id=1; QUERY PLAN ---------------------------------------------------------------- Index Scan using ix2 on sgs (cost=0.14..3.15 rows=1 width=62) Index Cond: (id = 1) (2 rows) dbmspgdb=# /+ IndexScan(sgs ix1) / explain select * from sgs where id=1; QUERY PLAN ---------------------------------------------------------------- Index Scan using ix1 on sgs (cost=0.14..3.15 rows=1 width=62) Index Cond: (id = 1) (2 rows) dbmspgdb=# /+ IndexScan(sgs ix2) / explain select * from sgs where id=1; QUERY PLAN ---------------------------------------------------------------- Index Scan using ix2 on sgs (cost=0.14..3.15 rows=1 width=62) Index Cond: (id = 1) (2 rows) ####表关联查询场景 #####创建测试表： dbmspgdb=# create table a(id int,name varchar(20)); CREATE TABLE dbmspgdb=# insert into a select i,(i+1)::text from generate_series(1,1000000) t(i); INSERT 0 1000000 dbmspgdb=# create table b(id int,name varchar(20)); CREATE TABLE dbmspgdb=# insert into a select i,(i+1)::text from generate_series(1,10000) t(i); INSERT 0 10000 dbmspgdb=# create index ixa on a(id); CREATE INDEX dbmspgdb=# create index ixb on b(id); CREATE INDEX dbmspgdb=# analyze a; ANALYZE dbmspgdb=# analyze b; ANALYZE #####默认执行计划 dbmspgdb=# explain select * from a,b where a.id=b.id ; QUERY PLAN -------------------------------------------------------------------- Nested Loop (cost=0.42..2836.75 rows=920 width=72) -> Seq Scan on b (cost=0.00..19.00 rows=900 width=62) -> Index Scan using ixa on a (cost=0.42..3.12 rows=1 width=10) Index Cond: (id = b.id) (4 rows) #####控制表关联方式 dbmspgdb=# /+ hashjoin(a b) / explain select * from a,b where a.id=b.id ; QUERY PLAN ------------------------------------------------------------------------ Hash Join (cost=33115.00..38172.82 rows=920 width=72) Hash Cond: (b.id = a.id) -> Seq Scan on b (cost=0.00..19.00 rows=900 width=62) -> Hash (cost=15558.00..15558.00 rows=1010000 width=10) -> Seq Scan on a (cost=0.00..15558.00 rows=1010000 width=10) (5 rows) dbmspgdb=# /+ mergejoin(a b) / explain select * from a,b where a.id=b.id ; QUERY PLAN -------------------------------------------------------------------------------------------- Gather (cost=1000.58..21268.27 rows=920 width=72) Workers Planned: 2 -> Merge Join (cost=0.57..20268.27 rows=383 width=72) Merge Cond: (a.id = b.id) -> Parallel Index Scan using ixa on a (cost=0.42..19179.96 rows=420833 width=10) -> Index Scan using ixb on b (cost=0.15..30.15 rows=900 width=62) (6 rows) ####索引提示关键词 #####扫描方式： SeqScan(table) TidScan(table) IndexScan(table[index]) IndexOnlyScan(table[index]) BitmapScan(table[index]) #####连接方式： NestLoop(table table [table...]) HashJoin(table table [table...]) MergeJoin(table table [table...]) ####缺陷 #####在某些场景下会导致pg进程crash，生产禁止使用 dbmspgdb=# /+ IndexScan(sgs ix2) / explain select * from sgs where id=1; QUERY PLAN ---------------------------------------------------------------- Index Scan using ix2 on sgs (cost=0.14..3.15 rows=1 width=62) Index Cond: (id = 1) (2 rows) dbmspgdb=# /+ seqscan(a) / explain select * from a,b where a.id=b.id ; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !>
	我的签名：代码不规范同事泪两行

您还没有登录,请您登录后再发表回复