os: ubuntu 16.04
postgresql: 9.6.8
ip 规划
192.168.56.102 node2 postgresql
help create indexpostgres=# \h create indexCommand: CREATE INDEXDescription: define a new indexSyntax:CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ][ USING method ]
method
要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。 默认方法是btree。
btree不加任何指定时，默认使用的就是 bree 类型的索引。
适用大部分的场景。
postgres=# drop table tmp_t0;DROP TABLEpostgres=# create table tmp_t0(c0 varchar(100),c1 varchar(100));CREATE TABLEpostgres=# insert into tmp_t0(c0,c1) select md5(id::varchar),md5((id+id)::varchar) from generate_series(1,100000) as id;INSERT 0 100000postgres=# create index idx_tmp_t0_1 on tmp_t0(c0);CREATE INDEXpostgres=# \d+ tmp_t0 Table "public.tmp_t0" Column | Type | Modifiers | Storage | Stats target | Description --------+------------------------+-----------+----------+--------------+------------- c0 | character varying(100) | | extended | | c1 | character varying(100) | | extended | | Indexes: "idx_tmp_t0_1" btree (c0) postgres=# drop index idx_tmp_t0_1;DROP INDEXpostgres=# create index idx_tmp_t0_1 on tmp_t0 using btree (c0);CREATE INDEXpostgres=# \d+ tmp_t0 Table "public.tmp_t0" Column | Type | Modifiers | Storage | Stats target | Description --------+------------------------+-----------+----------+--------------+------------- c0 | character varying(100) | | extended | | c1 | character varying(100) | | extended | | Indexes: "idx_tmp_t0_1" btree (c0)postgres=# explain select * from tmp_t0 where c0 = 'd3d9446802a44259755d38e6d163e820'; QUERY PLAN ---------------------------------------------------------------------------- Index Scan using idx_tmp_t0_1 on tmp_t0 (cost=0.42..8.44 rows=1 width=66) Index Cond: ((c0)::text = 'd3d9446802a44259755d38e6d163e820'::text)(2 rows) ---------------------作者：peiybpeiyb 来源：CSDN 原文：blog.csdn.net/ctypyb2002 … 版权声明：本文为博主原创文章，转载请附上博文链接！ |
|
