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.

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

[YSQL] Support creating an index on an array #6606

Open
bllewell opened this issue Dec 10, 2020 · 4 comments
Open

[YSQL] Support creating an index on an array #6606

bllewell opened this issue Dec 10, 2020 · 4 comments
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue

Comments

@bllewell
Copy link
Contributor

bllewell commented Dec 10, 2020

Jira Link: DB-4857
Try this

create table t1(k int primary key, arr int[]);
create index i1 on t1(arr);

create table t1(k int primary key, arr text[]);
create index i2 on t1(arr);

The first create index fails thus:

0A000: INDEX on column of type 'INT4ARRAY' not yet supported

And the second create index fails thus:

0A000: INDEX on column of type 'TEXTARRAY' not yet supported

The workaround in each case is to create an index on the text typecast of the array. But the naïve attempt:

create index i1 on t1((arr::text));

fails for a different reason:

ERROR:  42P17: functions in index expression must be marked IMMUTABLE

(This error occurs in vanilla PG 11.2.)

So the workaround needs a nonce jacket function, thus:

create function arr_as_text(arr in int[])
  returns text
  immutable
  language plpgsql
as $body$
begin
  return arr::text;
end;
$body$;

create index i2 on t1(arr_as_text(arr));

This is a huge nuisance because it increases the number of artifacts that an app needs to develop, test, document, and maintain. It also means that queries must use the same jacket in order to take advantage of the index. This is yet another burden for the development shop.

@bllewell
Copy link
Contributor Author

Search the YSQL doc for #6606 when this is fixed and closed. For example, the WITH Clause doc needs such an index and has to use the workaround.

@tedyu
Copy link
Contributor

tedyu commented Dec 10, 2020

diff --git a/src/postgres/src/backend/catalog/ybctype.c b/src/postgres/src/backend/catalog/ybctype.c
index 5a76fb743..4d9d29398 100644
--- a/src/postgres/src/backend/catalog/ybctype.c
+++ b/src/postgres/src/backend/catalog/ybctype.c
@@ -811,7 +811,7 @@ static const YBCPgTypeEntity YBCTypeEntityTable[] = {
         (YBCPgDatumToData)YBCDatumToBinary,
         (YBCPgDatumFromData)YBCBinaryToDatum },

-    { TEXTARRAYOID, YB_YQL_DATA_TYPE_BINARY, false, -1,
+    { TEXTARRAYOID, YB_YQL_DATA_TYPE_BINARY, true, -1,
         (YBCPgDatumToData)YBCDatumToBinary,
         (YBCPgDatumFromData)YBCBinaryToDatum },

With the above change, I seem to be able to:

yugabyte=# insert into t1 values(1, array['foo']);
INSERT 0 1
yugabyte=# explain select * from t1 where arr = array['foo'];
                          QUERY PLAN
---------------------------------------------------------------
 Index Scan using i2 on t1  (cost=0.00..5.22 rows=10 width=36)
   Index Cond: (arr = '{foo}'::text[])
(2 rows)

@tedyu
Copy link
Contributor

tedyu commented Dec 10, 2020

With similar change for INT4ARRAYOID:

yugabyte=# create table int(k int primary key, arr int[]);
CREATE TABLE
yugabyte=# create index i1 on int(arr);
CREATE INDEX
yugabyte=#  insert into int values (2, array[3, 6]);
INSERT 0 1
yugabyte=# select * from int;
 k |  arr
---+-------
 2 | {3,6}
(1 row)

yugabyte=# explain select * from int where arr = array[3, 6];
                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using i1 on "int"  (cost=0.00..5.22 rows=10 width=36)
   Index Cond: (arr = '{3,6}'::integer[])
(2 rows)

@pruiz
Copy link

pruiz commented Jul 3, 2022

This seems to be stuck, any way to help with moving this forward?

@rthallamko3 rthallamko3 added the area/ysql Yugabyte SQL (YSQL) label Jan 3, 2023
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jan 3, 2023
@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 Jan 11, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue
Projects
Status: No status
Development

No branches or pull requests

6 participants