Is it possible to create an index on a column with type of text array. Tried using GIN indexes, but queries do not seem to be using those indexes.
-- Example:
CREATE TABLE users (
name VARCHAR(100),
groups TEXT[],
);
-- Query:
SELECT name FROM users WHERE ANY(groups) = 'Engineering';
Also what is the best way to perform GROUP BY
on groups
column efficiently so that it can give groups
and count.
PostgreSQL Supports the concept of Arrays. All data type has a companion array associated with it irrespective of the properties of the data type.It is available even for user-defined data types.
A gin index can be used:
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 on an array, that will be another problem. The function unnest() might help.
Why don't you normalize your data? That will fix all problems, including many problems you didn't encouter yet.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With