巧用 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_array
add 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     | 1
vid_number | MEYD-844
vid_tags   | {1,2,3,4,5,6,7,8}
txt_tags   | {熟女,人妻,巨乳,泳裝,單體作品,高清,獨家,4K}
-[ RECORD 2 ]-------------------------------------------
vid_id     | 2
vid_number | WAAA-293
vid_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     | 1
vid_number | MEYD-844
vid_tags   | {1,2,3,4,5,6,7,8}
txt_tags   | {熟女,人妻,巨乳,泳裝,單體作品,高清,獨家,4K}


select *
  from videos_array
 where txt_tags @@> array['%教師%'];

-[ RECORD 1 ]-------------------------------------------
vid_id     | 2
vid_number | WAAA-293
vid_tags   | {1,3,7,8,9,10,11,12}
txt_tags   | {熟女,巨乳,獨家,4K,女教師,痴女,大屁股,中出}

可以看到使用了 @>, @@> 兩個運算子, 還能做 wildcard search!

修正資訊:

感謝葉賢大大提供回饋!
@@> 部分是安裝 parray_gin 之後才有的.

人生就是會遇到那個但是

上面的例子看起來不錯,但是資料量大時…
使用資料庫,大家都知道可以建立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, 所以我們需要強制使用
-- 這裡主要是功能性的測試,而不花費時間去取得大量的影片tags

SET 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貢獻的人,雖然不見得有很好聽的頭銜,但是默默的付出,讓世界更美好.