Postgres: array vs hstore vs jsonb (set operations)

bench_set_types.sql

create extension if not exists hstore;

create table if not exists test_array  as
  select id, '{"A","B","C"}'::varchar[] || id::varchar as codes from generate_series(1, 100000) id;
create table if not exists test_hstore as
  select id, '"A"=>t,"B"=>t,"C"=>t'::hstore || hstore(id::varchar, 't') as codes from generate_series(1, 100000) id;
create table if not exists test_jsonb  as
  select id, '{"A":true,"B":true,"C":true}'::jsonb || jsonb_build_object(id::varchar, true) as codes from generate_series(1, 100000) id;

\timing on
\o /dev/null

\echo
\echo remove 1 key
select id, array_remove(codes, 'B') from test_array;
select id, codes - 'B'::text from test_hstore;
select id, codes - 'B'::text from test_jsonb;

\echo
\echo remove many keys
select id, (
  select array_agg(x) from (
    select unnest(codes) x
      except all
    select unnest(array['B','C','10'])
  ) sub
) as codes from test_array;

select id, codes - array['B','C','10']::text[] from test_hstore;
select id, codes - array['B','C','10']::text[] from test_jsonb;

\echo
\echo add 1 key
-- select id, array_append(codes, 'D') from test_array;
-- select id, codes || 'D'::varchar from test_array;
select id, codes || array['D']::varchar[] from test_array;
select id, codes || hstore('D', '5') from test_hstore;
select id, codes || '{"D":5}' from test_jsonb;


\echo
\echo add many keys
select id, codes || array['D', 'E', 'F']::varchar[] from test_array;
select id, codes || hstore(array['D', '5', 'E', '6', 'F', '7']) from test_hstore;
select id, codes || '{"D":5, "E":6, "F":7}'::jsonb from test_jsonb;


\echo
\echo select intersection
select id, (
  select array_agg(x) from (
    select unnest(codes) x
      intersect all
    select unnest(array['B','C','10'])
  ) sub
) as codes from test_array;

select id, slice(codes, array['B','C','10']::varchar[]) from test_hstore;

\echo N/A
-- select id, codes || '{"D":5, "E":6, "F":7}'::jsonb from test_jsonb;

\echo
\echo query intersection
select id, codes && array['10', 'D', 'E', 'F']::varchar[] from test_array;
select id, codes ?| array['10', 'D', 'E', 'F']::varchar[] from test_hstore;
select id, codes ?| array['10', 'D', 'E', 'F']::varchar[] from test_jsonb;

result.txt

remove 1 key
Time: 136.333 ms
Time: 82.180 ms
Time: 174.990 ms

remove many keys
Time: 872.212 ms
Time: 94.713 ms
Time: 168.339 ms

add 1 key
Time: 121.886 ms
Time: 92.411 ms
Time: 246.377 ms

add many keys
Time: 141.514 ms
Time: 105.549 ms
Time: 347.621 ms

select intersection
Time: 837.037 ms
Time: 97.821 ms
N/A

query intersection
Time: 107.745 ms
Time: 87.925 ms
Time: 87.111 ms

Winner: hstore