PostgreSQL 13 preview - index - Opclass parameters

作者

digoal

日期

2020-11-28

标签

PostgreSQL , Opclass parameters , index

背景

When creating Bloom index, a total size of the signature ("length") is specified, as well as the number of bits to be set for each individual field included in the index ("col1"—"col32"):

create index on ... using bloom(...) with (length=..., col1=..., col2=..., ...);

The way to specify the number of bits looks odd: these numbers must be parameters of an operator class rather than the index. The thing is that operator classes cannot be parametrized at present, although work on this is in progress.

• The feature (commitfest entry) finally got into PostgreSQL 13.

https://www.postgresql.org/message-id/flat/d22c3a18-31c7-1879-fc11-4c1ce2f5e5af@postgrespro.ru

简单来说本来应该在opc里面体现的参数, 现在只能在index parameter里面输入. 理论上应该写在opc里面.

PG 13 支持了opclass里面填写option语法, 如下, 终于不用写在index的parameter里面了:

https://www.postgresql.org/docs/13/indexes-opclass.html
https://www.postgresql.org/docs/13/xindex.html

CREATE INDEX name ON table (column opclass [ ( opclass_options ) ] [sort options] [, ...]);

```
CREATE INDEX idx ON tab USING am (
{expr {opclass | DEFAULT} ({name=value} [,...])} [,...]
);

Example for contrib/intarray:

CREATE INDEX ON arrays USING gist (
arr gist__intbig_ops (siglen = 32),
arr DEFAULT (numranges = 100)
);

\d arrays
Table "public.arrays"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
arr | integer[] | | |
Indexes:
"arrays_arr_arr1_idx" gist (arr gist__intbig_ops (siglen='32'), arr gist__int_ops (numranges='100'))
```

查询index opc options

postgres=# SELECT ARRAY( SELECT (pg_identify_object('pg_opclass'::regclass, opcid, 0)).name FROM unnest(indclass::int[]) opcid ) indclass, indoption FROM pg_index WHERE indoption IS NOT NULL;

1. Opclass parameters support in GiST indices.

Parametrized GiST opclass specifies optional 10th (GIST_OPCLASSOPT_PROC)
support function with the following signature:
internal (options internal, validate bool)

Returned parsed bytea pointer with parameters will be passed to all support
functions in the last argument.

1. Opclass parameters support in GIN indices.

Everything is the same as for GiST, except for the optional support
function number which is 7 (GIN_OPCLASSOPTIONS_PROC) here.

1. Opclass parameters for GiST tsvector_ops
2. Opclass parameters for contrib/intarray
3. Opclass parameters for contrib/ltree
4. Opclass parameters for contrib/pg_trgm
5. Opclass parameters for contrib/hstore

This 5 patches for GiST opclasses are very similar: added optional 'siglen'
parameter for specifying signature length. Default signature length is left
equal to the hardcoded value that was here before. Also added 'numranges'
parameter for gist__int_ops.

We also have two more complex unfinished patches for GIN opclasses which
should be posted in separate threads:

• tsvector_ops: added parameter 'weights' for specification of indexed
  lexeme's weight groups. This parameter can reduce index size and its
  build/update time and can also eliminate recheck. By default, all weights
  are indexed within the same group.
• jsonb_ops: added jsonpath parameter 'projection' for specification of
  indexed paths in jsonb (this patch depends on SQL/JSON jsonpath patch).
  Analogically to tsvector_ops, this parameter can reduce index size and its
  build/update time, but can not eliminate recheck.

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.