Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL] Support GIN Indexes #7850

Open
11 of 26 tasks
m-iancu opened this issue Mar 30, 2021 · 0 comments
Open
11 of 26 tasks

[YSQL] Support GIN Indexes #7850

m-iancu opened this issue Mar 30, 2021 · 0 comments
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Projects
YSQL GIN Indexes

Comments

@m-iancu
Copy link
Contributor

m-iancu commented Mar 30, 2021 •
edited by jira bot
Loading

Jira Link: DB-1616
design doc: https://github.com/jaki/ysql-gin-doc/releases.

ybgin is to gin as lsm is to btree.

Stage 1 (Release target: v2.11)

  1. feat: add system catalog entries for new opclass ybgin
  2. refactor: transparently change USING gin to use ybgin on Yugabyte tables (ysql: transparently change gin to ybgin #8402)
  3. feat: implement limited functionality for ybgin
  4. refactor: turn PrepareIndexWriteStmt to callback ([YSQL] Turn PrepareIndexWriteStmt to callback #10406)
  5. feat: support text search (YSQL - full text search  #3446)
  6. feat: support writing null categories (ysql: support writing ybgin null categories #9959)
  7. feat: redirect unsupported scans to sequential scan (cost estimates) (ysql: use sequential scan instead of unsupported ybgin scan #9960)

Stage 2

Future

  • perf: do smarter scans using parallel scan streams
  • perf: don't always recheck tuples
  • feat: support IndexOnlyScan (this is not in upstream PostgreSQL)
@m-iancu m-iancu added the area/ysql Yugabyte SQL (YSQL) label Mar 30, 2021
@m-iancu m-iancu added this to Backlog in YSQL via automation Mar 30, 2021
@m-iancu m-iancu mentioned this issue Apr 22, 2021
Open
@jaki jaki mentioned this issue May 10, 2021
Closed
@nvcnvn nvcnvn mentioned this issue Jul 13, 2021
Open
@m-iancu m-iancu added this to To do in GIN Indexes via automation Jul 23, 2021
@majst01 majst01 mentioned this issue Jul 27, 2021
Closed
@jaki jaki moved this from To do to In progress in GIN Indexes Sep 9, 2021
jaki added a commit that referenced this issue Sep 9, 2021
@jaki
[#7850] ysql: create new access method ybgin
afbfa7f 
Summary:
Create a new index access method ybgin for GIN indexing on Yugabyte
relations.  The gin access method should be reserved for temporary
relations.  This is similar to how the lsm access method is for Yugabyte
relations while btree and hash access methods are for temporary
relations.

**Only implement the system catalog entries and basic handler fields.**
Don't allow this index to be created because nothing is yet implemented.

For system catalog entries, add

- pg_proc: ybginhandler function
- pg_am: ybgin access method, referencing handler ybginhandler
- pg_opclass: four ybgin opclasses copying the four gin opclasses except
  referencing access method ybgin (note: this reuses the gin opfamilies)

This is a system catalog change, so **reinitdb or initdb upgrade is
needed** when upgrading existing clusters if you want to eventually use
ybgin.

For the am handler, fill in the non-function fields, but set all the
function fields to NULL for now.  It would be a problem if anyone tries
to use these functions, but that shouldn't be possible because of checks
in indexcmds.c that prevent ybgin indexes from even being created.

Add yb_ybgin regress test to make sure ybgin indexes can't be created on
temporary and non-temporary tables.

Test Plan: ./yb_build.sh --java-test org.yb.pgsql.TestPgRegressGin

Reviewers: dmitry, neil, mihnea

Reviewed By: mihnea

Subscribers: alex, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D11571
jaki added a commit that referenced this issue Oct 27, 2021
@jaki
[#7850] ysql: implement ybgin am handler functions
b85fb4b 
Summary:
Implement handler functions for the ybgin access method

- Used functions:

  - [x] ambuild
  - [x] amcostestimate
  - [x] amoptions
  - [x] amvalidate
  - [x] ambeginscan
  - [x] amrescan
  - [x] amgettuple
  - [x] amendscan
  - [x] yb_aminsert
  - [x] yb_amdelete
  - [x] yb_ambackfill

- Unused functions:

  - [x] ambuildempty
  - [x] aminsert
  - [x] ambulkdelete
  - [x] amvacuumcleanup
  - [x] amcanreturn
  - [x] amproperty
  - [x] amgetbitmap
  - [x] ammarkpos
  - [x] amrestrpos
  - [x] amestimateparallelscan
  - [x] aminitparallelscan
  - [x] amparallelrescan

Remove the guardrail that restricts using ybgin.  The basic
functionality should work:

- Create works as long as the index is not multicolumn and no gin
  reloptions are set.
- Writes work as long as the key type is able to be a primary key in
  DocDB (only applicable to certain arrays) and there are no nulls (this
  is the biggest restriction).
- Reads work as long as the query produces one required scan entry and
  is default search mode and is not partial match.  This restricts
  OR-type queries, full scans, null scans, and prefix matching.

A ybgin (single column) index creates a single DocDB table whose key is
the key type of the column's opclass (e.g. tsvector_ops has key type
text).  However, the DocDB column name is the same as that of the
container type.

For now, cost estimate should always choose ybgin index.  This should be
improved later.

There is an issue #10406 with the collation type used during writes, but
it should be fine for now since non-C collations aren't allowed, yet.

Test Plan:
    ./yb_build.sh --cxx-test pgwrapper_pg_gin_index-test
    ./yb_build.sh --java-test TestPgRegressContribHstore
    ./yb_build.sh --java-test TestPgRegressContribIntarray
    ./yb_build.sh --java-test TestPgRegressGin
    ./yb_build.sh --java-test TestPgRegressJson
    ./yb_build.sh --java-test TestPgRegressTypesString
    ./yb_build.sh --java-test TestPgRegressTypesUDT

Reviewers: neil, mihnea, myang

Reviewed By: mihnea, myang

Subscribers: myang, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D11274
jaki added a commit that referenced this issue Nov 4, 2021
@jaki
[#7850] test: improve some ybgin regress tests
3436363 
Summary:
- fix syntax error in yb_ybgin_operators test on jsonpath "is unknown"
- change some EXPLAINs in yb_ybgin_misc test to have "costs off"
- change EXPLAINs in yb_ybgin_operators test to have "costs off"

Test Plan: ./yb_build.sh --java-test TestPgRegressGin

Reviewers: dpatra, mtakahara

Reviewed By: mtakahara

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D13767
@m-iancu m-iancu added the roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list. label Nov 4, 2021
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jun 8, 2022
@jasonyb jasonyb mentioned this issue Aug 16, 2022
Open
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature and removed kind/bug This issue is a bug labels Aug 26, 2022
@priyanshi-yb priyanshi-yb mentioned this issue Oct 20, 2022
Merged
@priyanshi-yb priyanshi-yb mentioned this issue Jan 5, 2023
Open
@jasonyb jasonyb mentioned this issue Mar 28, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Projects
GIN Indexes
In progress
YQL-beta
Status: No status
YSQL
  
Backlog
Milestone
No milestone
Development

No branches or pull requests
4 participants
@m-iancu @tverona1 @jaki @yugabyte-ci