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

This article mainly explains "what is the function of Btree index in PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the function of Btree index in PostgreSQL"?

structure

Btree is a common data structure with the following characteristics:

1.Btree is a balanced tree, bounded by root nodes, and the number of intermediate nodes on the left and right sides is the same, that is to say, the time of querying any value is the same.

2.Btree has multiple branches, and each page (8KB) can have hundreds of TIDs, which means that Btree needs only a few levels to support tables with a large number of rows.

3. The data in the index is ordered between Page and inside Page, and Page of the same level is connected to each other through two-way linked list.

NULLs

PostgreSQL stores NULLs when creating an index, so index scanning can be supported when the conditions are IS NULL and IS NOT NULL.

Testdb=# insert into t_null select xjournal c1' | x from generate_series (1q10 000) as xposition insert 0 10000testdb=# insert into t_null values (null,null); INSERT 0 1testdb=# testdb=# create index idx_t_null_id on t_null (id); CREATE INDEXtestdb=# analyze tactile nullability ANALYZEtestdbkeeper # 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 TABLEtestdb=# insert into t_null select null,null from generate_series (1 1testdb=# analyze 10000); INSERT 0 10000testdb=# insert into t_null values (1 camera 1'); ANALYZETestDB # 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 can be saved at the front of the Index or at the end, and can be specified by the FIRST/LAST keyword, which affects sorting.

Testdb=# create table t_null_sort (id int,c1 varchar (20)); CREATE TABLEtestdb=# testdb=# insert into t_null_sort select XMagneC1' | | x from generate_series (110000) as xbot insert 0 10000testdb=# insert into t_null_sort values (null,null); INSERT 0 1testdb=# testdb=# create index idx_t_null_id_first on t_null_sort (id nulls first); CREATE INDEXtestdb=# create index idx_t_null_id_last on t_null_sort (id nulls last) CREATE INDEXtestdb=# testdb=# analyze tincture nullary sort.ANALYZEtestdbacter # 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

When creating an index, non-index fields can be added to the index by using INCLUDE. If the projected column contains only index columns and INCLUDE columns, then Fetch data can be scanned by INDEX ONLY SCAN.

Testdb=# create table t_include (id int,c1 varchar (20), c2 varchar (20), c3 varchar (20)); CREATE TABLEtestdb=# testdb=# insert into t_include (id,c1,c2) select x training C1' | | x from generate_series (110000) as x terse insert 0 10000testdb=# testdb=# create index idx_t_include_id on t_include (id) include (C1); CREATE INDEXtestdb=# testdb=# analyze tincture deviceANALYZEtestDB # 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

Create a type complex and a data table

Testdb=# create type complex as (re float, im float); CREATE TYPEtestdb=# create table numbers (x complex); CREATE TABLEtestdb=# insert into numbers values ((0.0,10.0)), ((1.0,3.0)), ((1.0,1.0)); INSERT 0 3testdb=# select * from numbers order by x; x-(0 rows 10) (1 rows 1) (1 rows 3)

Create a comparison function

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 FUNCTIONtestdb=# testdb=# create function complex_lt (a complex, b complex) returns boolean as $$testdb$# select modulus (a)

< modulus(b);testdb$# $$ immutable language sql;CREATE FUNCTIONtestdb=# testdb=# create function complex_le(a complex, b complex) returns boolean as $$testdb$# select modulus(a) = modulus(b);testdb$# $$ immutable language sql;CREATE FUNCTIONtestdb=# testdb=# create function complex_gt(a complex, b complex) returns boolean as $$testdb$# select modulus(a) >

Modulus (b); testdb$# $$immutable language sql;CREATE FUNCTION

Create operator

Testdb=# create operator (leftarg=complex, rightarg=complex, procedure=complex_gt); CREATE OPERATORtestdb=#

You can now compare complex:

Testdb=# select (1.0 du 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 -1testdb$# when modulus(a) >

Modulus (b) then 1 testdb$# else 0testdb$# end;testdb$# $$language sql;CREATE FUNCTIONtestdb=# create operator class complex_opstestdb-# default for type complextestdb-# using btree astestdb-# operator 1, testdb-# function 1 complex_cmp (complex,complex); CREATE OPERATOR CLASStestdb=# select * from pg_opfamily where opfname = 'complex_ops' Oid | opfmethod | opfname | opfnamespace | opfowner-+-106585 | 403 | complex_ops | 2200 | 10 (1 row)

You can now create a Btree index with the data type complex

Testdb=# select amp.amprocnum,testdb-# amp.amproc,testdb-# amp.amproclefttype::regtype,testdb-# amp.amprocrighttype::regtypetestdb-# from pg_opfamily opf,testdb-# pg_am am,testdb-# pg_amproc amptestdb-# where opf.opfname = 'complex_ops'testdb-# and opf.opfmethod = am.oidtestdb-# 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 INDEXtestdb=# analyze numbers;ANALYZEtestdb=# 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 SETtestdb=# 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) I believe that you have a deeper understanding of "what is the role of Btree indexes in PostgreSQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.