# Solution 1: Wide Table Solution

`create table t_tags_1(appid int, userid int8, tag1 boolean, tag2 boolean, ...... tag1000 boolean);  .....create table t_tags_10(appid int, userid int8, tag9001 boolean, tag9002 boolean, ...... tag10000 boolean);`

# Solution 2: Array Solution

`APPID, USERID, TAG[] array`
`create table t_arr(appid int, userid int8, tags int2[]) with(parallel_workers=128);create index idx_t_array_tags on t_arr using gin (tags) with (fastupdate=on, gin_pending_list_limit= 1024000000);create index idx_t_arr_uid on t_arr(userid);`
`insert into t_arr select 1, 2000000000*random(),(select array_agg(10000*random()) from generate_series(1,10000));nohup pgbench -M prepared -n -r -f ./test.sql -P 1 -c 50 -j 50 -t 2000000 > ./arr.log 2>&1 &`

# Solution 3: Bit Solution 1

`APPID, USERID, TAG bit stream`
`create table t_bit(appid int, userid int8, tags varbit) ; create index idx_t_bit_uid on t_bit(userid);`
`date;for ((i=1;i<=50;i++)); do psql -c "insert into t_bit select 1, 2000000000*random(), \(select (string_agg(mod((2*random())::int,2)::text,''))::varbit from generate_series(1,10000)) tags \from generate_series(1,2000000)" ; done; date`
`create or replace function randbit(int) returns varbit as \$\$  select (string_agg(mod((2*random())::int,2)::text,''))::varbit from generate_series(1,\$1);\$\$ language sql strict volatile;create or replace function zerobit(int) returns varbit as \$\$  select (string_agg('0',''))::varbit from generate_series(1,\$1);\$\$ language sql strict immutable;update t_bit set tags=randbit(10000) where userid=:id;`
`do language plpgsql \$\$declare  sql text;  bit1 varbit := randbit(10000);  bit2 varbit := randbit(10000);  bit3 varbit := randbit(10000);  zbit varbit := zerobit(10000);begin  set max_parallel_workers_per_gather =27;  sql := 'select * from t_bit where bitand(tags,'''||bit1::text||''')='''||bit1::text||''' and bitand(tags,'''||bit2::text||''')>bit''0'' and bitand(tags,'''||bit3::text||''')='''||zbit::text||'''';  raise notice '%', sql;  -- execute sql;end;\$\$;`

# Solution 4: Bit Solution 2

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

## Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com