溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“PostgreSQL中的Btree索引有什么作用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“PostgreSQL中的Btree索引有什么作用”吧!
結構
Btree是常見的數據結構,有以下特性:
1.Btree是平衡樹,以root節點為分界,左右兩邊的中間節點數目一樣,也就是說查詢任意一個值,時間都是一樣的
2.Btree有多個分支,每個page(8KB)可以有數百個TIDs,也就是說Btree只需要不多的幾個層次就可以支持行數巨大的表
3.索引中的數據Page之間和Page內部都是有序的,相同層次的Page通過雙向鏈表彼此連接
NULLs
PostgreSQL在創建索引時會存儲NULLs,因此條件為IS NULL和IS NOT NULL時可以支持索引掃描.
testdb=# insert into t_null select x,'c1'||x from generate_series(1,10000) as x; INSERT 0 10000 testdb=# insert into t_null values(null,null); INSERT 0 1 testdb=# testdb=# create index idx_t_null_id on t_null(id); CREATE INDEX testdb=# analyze t_null; ANALYZE testdb=# testdb=# explain verbose select * from t_null where id is null; QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using idx_t_null_id on public.t_null (cost=0.29..8.30 rows=1 width=10) Output: id, c1 Index Cond: (t_null.id IS NULL) (3 rows) testdb=# explain verbose select * from t_null where id is not null; QUERY PLAN -------------------------------------------------------------------- Seq Scan on public.t_null (cost=0.00..155.01 rows=10000 width=10) Output: id, c1 Filter: (t_null.id IS NOT NULL) (3 rows) testdb=# testdb=# truncate t_null; TRUNCATE TABLE testdb=# insert into t_null select null,null from generate_series(1,10000); INSERT 0 10000 testdb=# insert into t_null values(1,'1'); INSERT 0 1 testdb=# analyze t_null; ANALYZE testdb=# testdb=# explain verbose select * from t_null where id is null; QUERY PLAN ------------------------------------------------------------------- Seq Scan on public.t_null (cost=0.00..135.01 rows=10000 width=6) Output: id, c1 Filter: (t_null.id IS NULL) (3 rows) testdb=# explain verbose select * from t_null where id is not null; QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using idx_t_null_id on public.t_null (cost=0.29..8.30 rows=1 width=6) Output: id, c1 Index Cond: (t_null.id IS NOT NULL) (3 rows) testdb=#
NULLs可以保存在Index的最前面,也可以保存在最后面,可通過FIRST/LAST關鍵字指定,這對排序會有所影響.
testdb=# create table t_null_sort(id int,c1 varchar(20)); CREATE TABLE testdb=# testdb=# insert into t_null_sort select x,'c1'||x from generate_series(1,10000) as x; INSERT 0 10000 testdb=# insert into t_null_sort values(null,null); INSERT 0 1 testdb=# testdb=# create index idx_t_null_id_first on t_null_sort(id nulls first); CREATE INDEX testdb=# create index idx_t_null_id_last on t_null_sort(id nulls last); CREATE INDEX testdb=# testdb=# analyze t_null_sort; ANALYZE testdb=# testdb=# explain verbose select * from t_null_sort order by id nulls first; QUERY PLAN ----------------------------------------------------------------------------------------------------- Index Scan using idx_t_null_id_first on public.t_null_sort (cost=0.29..328.30 rows=10001 width=10) Output: id, c1 (2 rows) testdb=# explain verbose select * from t_null_sort order by id nulls last; QUERY PLAN ---------------------------------------------------------------------------------------------------- Index Scan using idx_t_null_id_last on public.t_null_sort (cost=0.29..328.30 rows=10001 width=10) Output: id, c1 (2 rows) testdb=# testdb=#
INCLUDE
創建索引時,通過使用INCLUDE可以把非索引字段加入到該索引中,在通過索引掃描時如投影列只包含索引列和INCLUDE列,那么可以通過INDEX ONLY SCAN掃描Fetch數據.
testdb=# create table t_include(id int,c1 varchar(20),c2 varchar(20),c3 varchar(20)); CREATE TABLE testdb=# testdb=# insert into t_include(id,c1,c2) select x,'c1'||x,'c2'||x from generate_series(1,10000) as x; INSERT 0 10000 testdb=# testdb=# create index idx_t_include_id on t_include(id) include (c1); CREATE INDEX testdb=# testdb=# analyze t_include; ANALYZE testdb=# explain verbose select id,c1 from t_include; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on public.t_include (cost=0.00..163.00 rows=10000 width=10) Output: id, c1 (2 rows) testdb=# testdb=# explain verbose select id,c1 from t_include where id = 1; QUERY PLAN ----------------------------------------------------------------------------------------------- Index Only Scan using idx_t_include_id on public.t_include (cost=0.29..8.30 rows=1 width=10) Output: id, c1 Index Cond: (t_include.id = 1) (3 rows) testdb=#
New Data Type
創建類型complex以及數據表
testdb=# create type complex as (re float, im float); CREATE TYPE testdb=# create table numbers(x complex); CREATE TABLE testdb=# insert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0)); INSERT 0 3 testdb=# select * from numbers order by x; x -------- (0,10) (1,1) (1,3) (3 rows)
創建比較函數
testdb=# testdb=# create function modulus(a complex) returns float as $$ testdb$# select sqrt(a.re*a.re + a.im*a.im); testdb$# $$ immutable language sql; CREATE FUNCTION testdb=# testdb=# create function complex_lt(a complex, b complex) returns boolean as $$ testdb$# select modulus(a) < modulus(b); testdb$# $$ immutable language sql; CREATE FUNCTION testdb=# testdb=# create function complex_le(a complex, b complex) returns boolean as $$ testdb$# select modulus(a) <= modulus(b); testdb$# $$ immutable language sql; CREATE FUNCTION testdb=# testdb=# create function complex_eq(a complex, b complex) returns boolean as $$ testdb$# select modulus(a) = modulus(b); testdb$# $$ immutable language sql; CREATE FUNCTION testdb=# testdb=# create function complex_ge(a complex, b complex) returns boolean as $$ testdb$# select modulus(a) >= modulus(b); testdb$# $$ immutable language sql; CREATE FUNCTION testdb=# testdb=# create function complex_gt(a complex, b complex) returns boolean as $$ testdb$# select modulus(a) > modulus(b); testdb$# $$ immutable language sql; CREATE FUNCTION
創建operator
testdb=# create operator <(leftarg=complex, rightarg=complex, procedure=complex_lt); CREATE OPERATOR testdb=# testdb=# create operator <=(leftarg=complex, rightarg=complex, procedure=complex_le); arg=complex, rightarg=complex, procedure=complex_gt); CREATE OPERATOR testdb=# testdb=# create operator =(leftarg=complex, rightarg=complex, procedure=complex_eq); CREATE OPERATOR testdb=# testdb=# create operator >=(leftarg=complex, rightarg=complex, procedure=complex_ge); CREATE OPERATOR testdb=# testdb=# create operator >(leftarg=complex, rightarg=complex, procedure=complex_gt); CREATE OPERATOR testdb=#
現在可以對complex進行比較了:
testdb=# select (1.0,1.0)::complex < (1.0,3.0)::complex; ?column? ---------- t (1 row)
創建比較函數和opc,在創建opc的時候,pg會自動創建同名的opf
testdb=# create function complex_cmp(a complex, b complex) returns integer as $$ testdb$# select case when modulus(a) < modulus(b) then -1 testdb$# when modulus(a) > modulus(b) then 1 testdb$# else 0 testdb$# end; testdb$# $$ language sql; CREATE FUNCTION testdb=# create operator class complex_ops testdb-# default for type complex testdb-# using btree as testdb-# operator 1 <, testdb-# operator 2 <=, testdb-# operator 3 =, testdb-# operator 4 >=, testdb-# operator 5 >, testdb-# function 1 complex_cmp(complex,complex); CREATE OPERATOR CLASS testdb=# select * from pg_opfamily where opfname = 'complex_ops'; oid | opfmethod | opfname | opfnamespace | opfowner --------+-----------+-------------+--------------+---------- 106585 | 403 | complex_ops | 2200 | 10 (1 row)
現在可以創建數據類型為complex的Btree索引
testdb=# select amp.amprocnum, testdb-# amp.amproc, testdb-# amp.amproclefttype::regtype, testdb-# amp.amprocrighttype::regtype testdb-# from pg_opfamily opf, testdb-# pg_am am, testdb-# pg_amproc amp testdb-# where opf.opfname = 'complex_ops' testdb-# and opf.opfmethod = am.oid testdb-# and am.amname = 'btree' testdb-# and amp.amprocfamily = opf.oid; amprocnum | amproc | amproclefttype | amprocrighttype -----------+-------------+----------------+----------------- 1 | complex_cmp | complex | complex (1 row) testdb=# create index idx_numbers_x on numbers(x); CREATE INDEX testdb=# analyze numbers; ANALYZE testdb=# explain select * from numbers order by x; QUERY PLAN -------------------------------------------------------------- Sort (cost=1.14..1.15 rows=6 width=37) Sort Key: x -> Seq Scan on numbers (cost=0.00..1.06 rows=6 width=37) (3 rows) testdb=# set enable_seqscan=off; SET testdb=# explain select * from numbers order by x; QUERY PLAN ------------------------------------------------------------------------------------ Index Only Scan using idx_numbers_x on numbers (cost=0.13..12.22 rows=6 width=37) (1 row)
到此，相信大家對“PostgreSQL中的Btree索引有什么作用”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
