Geek Answers Handbook

Indexing array of row column type in PostgreSql

Is it possible to create an index in a column whose type is an array of rows . Tried to use GIN indices . But queries don't seem to use these indexes.

Example

CREATE TABLE users (
 name VARCHAR(100),
 groups text[],
);

Query: SELECT name FROM users WHERE ANY(groups) = 'Engineering'.

Also, in the best way, you can efficiently execute GROUP BY on the "groups" column so that it can give "groups" and count.

+5
source share
2 answers

You can use the gin index:

CREATE TABLE users (
 name VARCHAR(100),
 groups text[]
);

CREATE INDEX idx_users ON users USING GIN(groups);

-- disable sequential scan in this test:
SET enable_seqscan TO off;

EXPLAIN ANALYZE
SELECT name FROM users WHERE  groups @> (ARRAY['Engineering']);

Result:

"Bitmap Heap Scan on users  (cost=4.26..8.27 rows=1 width=218) (actual time=0.021..0.021 rows=0 loops=1)"
"  Recheck Cond: (groups @> '{Engineering}'::text[])"
"  ->  Bitmap Index Scan on idx_users  (cost=0.00..4.26 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)"
"        Index Cond: (groups @> '{Engineering}'::text[])"
"Total runtime: 0.074 ms"

Using aggregate functions in an array, this will be another problem. The disest () function may help.

? , , .

+2

, - . , , , , :

CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE groups (id INTEGER PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE user_group (
    user INTEGER NOT NULL REFERENCES users,
    group INTEGER NOT NULL REFERENCES groups);
CREATE UNIQUE INDEX user_group_unique ON user_group (user, group);

SELECT users.name
    FROM user_group
    INNER JOIN users ON user_group.user = users.id
    INNER JOIN groups ON user_group.group = groups.id
    WHERE groups.name = 'Engineering';

; ON user_group (group), index_scan, sequential_scan .

0

More articles:


All Articles