[ysql] Secondary index support in PostgreSQL by following the CQL approach #559
Assignees
Comments
yugabyte-ci
pushed a commit
that referenced
this issue
Jan 23, 2019
Summary: Support CREATE INDEX with default options. The index created is not populated nor used to serve DML statements yet.
Test Plan:
```
postgres=# \d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |
relfilenode | oid | | not null |
reltablespace | oid | | not null |
relpages | integer | | not null |
reltuples | real | | not null |
relallvisible | integer | | not null |
reltoastrelid | oid | | not null |
relhasindex | boolean | | not null |
relisshared | boolean | | not null |
relpersistence | "char" | | not null |
relkind | "char" | | not null |
relnatts | smallint | | not null |
relchecks | smallint | | not null |
relhasoids | boolean | | not null |
relhaspkey | boolean | | not null |
relhasrules | boolean | | not null |
relhastriggers | boolean | | not null |
relhassubclass | boolean | | not null |
relrowsecurity | boolean | | not null |
relforcerowsecurity | boolean | | not null |
relispopulated | boolean | | not null |
relreplident | "char" | | not null |
relispartition | boolean | | not null |
relfrozenxid | xid | | not null |
relminmxid | xid | | not null |
relacl | aclitem[] | | |
reloptions | text[] | | |
relpartbound | pg_node_tree | | |
Indexes:
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
"pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
postgres=# create table t (a int);
CREATE TABLE
postgres=# create index on t (a);
CREATE INDEX
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Indexes:
"t_a_idx" btree (a)
```
Reviewers: neil, mikhail, mihnea
Reviewed By: mihnea
Subscribers: hector, bogdan, yql, bharat
Differential Revision: https://phabricator.dev.yugabyte.com/D5955
yugabyte-ci
pushed a commit
that referenced
this issue
Feb 9, 2019
Summary: Support Postgres secondary index in DML. The index is now updated in-sync with respect to the base table and can be used in queries. - Currently, only btree index is supported. Index read/write is done through Postgres index access method API. - When a row is inserted into a table with indexes, the ybctid is returned. All associated indexes are then populated. - When a row is deleted from the table, the associated index entries are deleted. - When a row is updated, the original index entries are first deleted. Then index entries for the updated row are inserted. - To update / delete index entries, the whole row is now read in the subplan under UPDATE / DELETE. This revision also adds the support to scan a table or index with a partial range key. Test Plan: Jenkins Reviewers: neil, mikhail, mihnea Reviewed By: mihnea Subscribers: bogdan, bharat Differential Revision: https://phabricator.dev.yugabyte.com/D6049
|
Basic support for secondary index has been completed in commit 954a64b. |
jasonyb
pushed a commit
that referenced
this issue
Apr 26, 2023
Summary: Do some trivial refactoring related to syscache: 1. Remove function YBSysTablePrimaryKey. It was introduced in commit 954a64b, titled [YSQL] (#559) Support secondary index in DML Usage of that function was removed in commit 1baa47a, titled [YSQL][#1590] Support bulk inserts for COPY FROM command Since it is an old commit before YSQL's first release, it is likely irrelevant now. 2. Remove a useless syscache.h include. Close: #17055 Test Plan: Jenkins: compile only Reviewers: myang Reviewed By: myang Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D24666
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Indexes should be owned by YugaByte and not PostgreSQL.
We should generate alternative query plans using the indexes.
The text was updated successfully, but these errors were encountered: