巧用 array 处理 tags 续篇

tag处理的一些情况

查询的情况

常见到使用tag查询是点单一tag,少数是可以选数个tag,然后再点查询.
虽然查询多tag对使用者来说,使用体验较好,但是使用此方式的网站较少.
一来查询方式组SQL可能较为麻烦,二来可能也是效能考量.

tag的管理

在上篇的例子中,我们有建立一个tags的table,并且有做unique index.
这样可以确保tag的唯一性,通常用在较为严谨的系统,例如物料属性,
希望不要过于发散,使用勾选的方式等等.
另一种情境是可以让使用者自行建立,例如一些讨论型态的网站,如ithelp,或是blog等等.
当不需要很严谨的系统,我们可以进一步反正规化,直接将tag的文字型态存到标的table,当然也可以在输入或选择介面上做控制,这时候查询就不需要再跟tag table 做 join, 取得tag 的 tag_name or tag_val.

tag 放到标的table的方式

alter table videos_arrayadd column txt_tags text[];with t1 as (select va.vid_id, t.tag_name  from videos_array va  cross join unnest(va.vid_tags) as tag_id  join tags t using (tag_id)), t2 as (select vid_id, array_agg(tag_name) as txttags  from t1 group by vid_id)update videos_array va2   set txt_tags = t2.txttags  from t2 where va2.vid_id = t2.vid_id;select * from videos_array;-[ RECORD 1 ]-------------------------------------------vid_id     | 1vid_number | MEYD-844vid_tags   | {1,2,3,4,5,6,7,8}txt_tags   | {熟女,人妻,巨乳,泳装,单体作品,高清,独家,4K}-[ RECORD 2 ]-------------------------------------------vid_id     | 2vid_number | WAAA-293vid_tags   | {1,3,7,8,9,10,11,12}txt_tags   | {熟女,巨乳,独家,4K,女教师,痴女,大屁股,中出}

array 直接查询

select *  from videos_array where txt_tags @> array['熟女', '人妻'];-[ RECORD 1 ]-------------------------------------------vid_id     | 1vid_number | MEYD-844vid_tags   | {1,2,3,4,5,6,7,8}txt_tags   | {熟女,人妻,巨乳,泳装,单体作品,高清,独家,4K}select *  from videos_array where txt_tags @@> array['%教师%'];-[ RECORD 1 ]-------------------------------------------vid_id     | 2vid_number | WAAA-293vid_tags   | {1,3,7,8,9,10,11,12}txt_tags   | {熟女,巨乳,独家,4K,女教师,痴女,大屁股,中出}

可以看到使用了 @>, @@> 两个运算子, 还能做 wildcard search!

人生就是会遇到那个但是

上面的例子看起来不错,但是资料量大时....
使用资料库,大家都知道可以建立index,提高查询效能.
当我们有使用tag table时,可以透过它的index.
目前使用array 直接在标的table放资料,我们就需要在建立能够处理array的index.
上一篇中,有建立了gin 型态的index.现在就来使用看看.

GIN Index

create table videos_array2 (  vid_id int not null generated always as identity primary key, vid_number text not null, vid_tags int[], txt_tags text[]);insert into videos_array2(vid_number, vid_tags, txt_tags)select vid_number, vid_tags, txt_tags  from videos_array va     , generate_series(1, 5000);create index on videos_array2 using gin (vid_tags);analyze videos_array2;-- 因为资料是大量重複的,正常情况下,优化器不会使用index-- 会选择Seq Scan, 所以我们需要强制使用-- 这里主要是功能性的测试,而不花费时间去取得大量的影片tagsSET enable_seqscan TO off;explain select *  from videos_array2 where vid_tags @> array[1,3,5];                                         QUERY PLAN                                          --------------------------------------------------------------------------------------------- Bitmap Heap Scan on videos_array2  (cost=70.75..404.25 rows=5000 width=185)   Recheck Cond: (vid_tags @> '{1,3,5}'::integer[])   ->  Bitmap Index Scan on videos_array2_vid_tags_idx  (cost=0.00..69.50 rows=5000 width=0)         Index Cond: (vid_tags @> '{1,3,5}'::integer[])

GIN Index 在text型态的array 使用情况

由上面的例子,看来好像不错喔,那我们接着试试看,text型态的array 建立index.

create index on videos_array2 using gin (txt_tags);explain select *  from videos_array2 where txt_tags @> array['熟女','巨乳','单体作品'];                                      QUERY PLAN                                     ------------------------------------------------------------------------------------ Seq Scan on videos_array2  (cost=10000000000.00..10000000396.00 rows=10 width=185)   Filter: (txt_tags @> '{熟女,巨乳,单体作品}'::text[]) JIT:   Functions: 2   Options: Inlining true, Optimization true, Expressions true, Deforming true

人生何处不但是

看来是无法很好的处理,成本还很大....
GIN Index 要处里text 型态,好像不能直接使用啊.....
我们来看看一篇有名关于GIN的文章
Understanding Postgres GIN Indexes: The Good and the Bad
里面有提到可以使用tsvector的方式,还有GIN的后继者RUM.
但是要使用tsvector还要再加工,而且对我们中文使用者来说,不是很好用.

Open Source 的力量让世界更美好

为了更方便的处理GIN与text array,有人贡献了
parray_gin 这个extension
来看看怎样使用,安装过程我省略了.make, sudo make install搞定.

drop index videos_array2_txt_tags_idx;create extension parray_gin;create index on videos_array2 using gin (txt_tags parray_gin_ops);explain select *  from videos_array2 where txt_tags @> array['熟女','巨乳','单体作品'];                                         QUERY PLAN                                         ------------------------------------------------------------------------------------------- Bitmap Heap Scan on videos_array2  (cost=48.08..82.44 rows=10 width=185)   Recheck Cond: (txt_tags @> '{熟女,巨乳,单体作品}'::text[])   ->  Bitmap Index Scan on videos_array2_txt_tags_idx  (cost=0.00..48.08 rows=10 width=0)         Index Cond: (txt_tags @> '{熟女,巨乳,单体作品}'::text[])explain select *  from videos_array2 where txt_tags @@> array['%女%','巨%'];                                        QUERY PLAN                                         ------------------------------------------------------------------------------------------- Bitmap Heap Scan on videos_array2  (cost=48.08..82.44 rows=10 width=185)   Recheck Cond: (txt_tags @@> '{%女%,巨%}'::text[])   ->  Bitmap Index Scan on videos_array2_txt_tags_idx  (cost=0.00..48.08 rows=10 width=0)         Index Cond: (txt_tags @@> '{%女%,巨%}'::text[])

可以看到这样就很方便的可以直接使用了.

结语

PostgreSQL array 搭配 GIN Index, 加上parray_gin extension,
可以让我们在处理tag时,很方便直观的使用.

感谢

感谢许多在PostgreSQL及Open Source贡献的人,虽然不见得有很好听的头衔,但是默默的付出,让世界更美好.

本文同步发表于 https://hackmd.io/@pgsql-tw/r1zoA8vza