PostgreSQL10.0preview功能增強-兩段式索引(約束欄位+附加欄位)
標籤
PostgreSQL , 10.0 , 約束覆蓋索引
背景
如果我們有這樣的查詢
select * from tbl where c1=? and c2=? and c3=? and c4=?
我們建立了複合索引達到最好的查詢效能
create index idx on tbl(c1,c2,c3,c4);
同時還有這樣的約束
create unique index idx on tbl (c1,c2);
那麼這樣的場景中,我們就有兩個索引。
PostgreSQL 10.0提供了一個新的功能,可以將這兩個索引合併,只有一個索引的體積,同時支援這兩個場景。。
create unique index idx on tbl (c1,c2) including (c3,c4);
這便是唯一約束+附加欄位組合功能索引
詳見
Hi hackers,
I`m working on a patch that allows to combine covering and unique
functionality for btree indexes.
_Previous discussion was here:_
1) Proposal thread
<http://www.postgresql.org/message-id/55F2CCD0.7040608@postgrespro.ru>
2) Message with proposal clarification
<http://www.postgresql.org/message-id/55F84DF4.5030207@postgrespro.ru>
In a nutshell, the feature allows to create index with "key" columns and
"included" columns.
"key" columns can be used as scan keys. Unique constraint relates only
to "key" columns.
"included" columns may be used as scan keys if they have suitable opclass.
Both "key" and "included" columns can be returned from index by
IndexOnlyScan.
Btree is the default index and it`s used everywhere. So it requires
properly testing. Volunteers are welcome)
_Use case:_
- We have a table (c1, c2, c3, c4);
- We need to have an unique index on (c1, c2).
- We would like to have a covering index on all columns to avoid reading
of heap pages.
Old way:
CREATE UNIQUE INDEX olduniqueidx ON oldt USING btree (c1, c2);
CREATE INDEX oldcoveringidx ON oldt USING btree (c1, c2, c3, c4);
What`s wrong?
Two indexes contain repeated data. Overhead to data manipulation
operations and database size.
New way:
CREATE UNIQUE INDEX newidx ON newt USING btree (c1, c2) INCLUDING (c3, c4);
The patch is attached.
In `test.sql` you can find a test with detailed comments on each step,
and comparison of old and new indexes.
New feature has following syntax:
CREATE UNIQUE INDEX newidx ON newt USING btree (c1, c2) INCLUDING (c3, c4);
Keyword INCLUDING defines the "included" columns of index. These columns
aren`t concern to unique constraint.
Also, them are not stored in index inner pages. It allows to decrease
index size.
_Results:_
1) Additional covering index is not required anymore.
2) New index can use IndexOnlyScan on queries, where old index can`t.
For example,
explain analyze select c1, c2 from newt where c1<10000 and c3<20;
*more examples in `test.sql`
_Future work:_
To do opclasses for "included" columns optional.
CREATE TABLE tbl (c1 int, c4 box);
CREATE UNIQUE INDEX idx ON tbl USING btree (c1) INCLUDING (c4);
If we don`t need c4 as an index scankey, we don`t need any btree opclass
on it.
But we still want to have it in covering index for queries like
SELECT c4 FROM tbl WHERE c1=1000;
SELECT * FROM tbl WHERE c1=1000;
--
Anastasia Lubennikova
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
這個patch的討論,詳見郵件組,本文末尾URL。
PostgreSQL社群的作風非常嚴謹,一個patch可能在郵件組中討論幾個月甚至幾年,根據大家的意見反覆的修正,patch合併到master已經非常成熟,所以PostgreSQL的穩定性也是遠近聞名的。
參考
https://commitfest.postgresql.org/13/961/
相關文章
- MySQL 欄位約束MySql
- 修改oracle的約束欄位Oracle
- 查詢oracle表的資訊(表,欄位,約束,索引)Oracle索引
- Django模型之欄位與約束Django模型
- 5_MySQL 表的欄位約束MySql
- ABAP-MB56欄位增強
- 資料庫中欄位資料型別以及約束資料庫資料型別
- pydantic 欄位欄位校驗
- MySQL學習筆記4:完整性約束限制欄位MySql筆記
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- SQL Server 自增欄位重置SQLServer
- 怎麼給字串欄位加索引?字串索引
- oracle複合索引介紹(多欄位索引)Oracle索引
- fastadmin 新增欄位記圖片欄位AST
- 保留兩位小數:資料庫欄位型別NUMBER,Java欄位型別Double型別資料庫型別Java
- 欄位排序排序
- [BUG反饋]模型管理 > 欄位管理看不見任何欄位。這表明顯有欄位、!模型
- SQL Server中根據某個欄位,ID欄位自動增長的實現SQLServer
- 觸發器—一個表插入資料時其他欄位同步自增長欄位觸發器
- oracle中如何指定表欄位自增Oracle
- oracle和mysql設定自增欄位OracleMySql
- 【Mongo】mongo更新欄位為另一欄位的值Go
- sql語句修改欄位型別和增加欄位SQL型別
- oracle刪除表欄位和oracle表增加欄位Oracle
- 資料庫索引欄位請不要為NULL資料庫索引Null
- 模型追加欄位模型
- elasticsearch 特殊欄位Elasticsearch
- MySQL 表的自增欄位 預判功能innodb_autoinc_lock_modeMySql
- 在 SQL Server 中,建立表時可以直接為欄位新增唯一約束(UNIQUE)SQLServer
- 巧用欄位對映實現指定欄位的搜尋
- 快速將下劃線欄位改為駝峰欄位
- PostgreSQL10.0preview功能增強-OLAP增強向量聚集索引(列儲存擴充套件)SQLView索引套件
- Dynamics CRM使用計算欄位自動計算兩個時間欄位的天數差
- arcgis欄位值計算(擷取A欄位前8位+按照順序計算8位)
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- 增加欄位的通用程式碼驗證功能
- SQL字元型欄位按數字型欄位排序實現方法SQL字元排序
- 通用首部欄位詳解-四大首部欄位之一