hstore-jsonb.sh

DB_NAME='hstore_jsonb_test'
MAX_RECORDS=1e7
DICTIONARY="'ivan', 'dobri', 'pesho', 'genadi', 'smotlyo', 'metodi', 'dimitar', 'stamen', 'petyo', 'gosho'"

psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME'"
psql -U postgres -c "DROP DATABASE IF EXISTS $DB_NAME"
psql -U postgres -c "CREATE DATABASE $DB_NAME"

psql $DB_NAME -U postgres -c "
  CREATE EXTENSION hstore;

  -- create gin index on hstore and equally distributed dataset from $DICTIONARY
  CREATE TABLE hstore_table (id integer, settings hstore);
  CREATE INDEX hstore_index ON hstore_table USING gin(settings);
  INSERT INTO  hstore_table SELECT id, hstore(str, str) FROM (
    SELECT generate_series(1, $MAX_RECORDS) id, unnest(array[$DICTIONARY]) str
  ) t;

  -- copy the hstore to the jsonb table
  CREATE TABLE jsonb_table (id integer, settings jsonb);
  INSERT INTO  jsonb_table SELECT id, settings::jsonb FROM hstore_table;
  CREATE INDEX jsonb_index ON jsonb_table USING gin(settings);
  "

psql $DB_NAME -U postgres -c 'vacuum analyze'
psql $DB_NAME -U postgres -c "EXPLAIN ANALYZE SELECT * FROM hstore_table WHERE settings ? 'ivan'"
psql $DB_NAME -U postgres -c "EXPLAIN ANALYZE SELECT * FROM jsonb_table WHERE settings ? 'ivan'"

nikola@[local] hstore_jsonb_test # select pg_size_pretty(pg_indexes_size('hstore_table'));
 pg_size_pretty
----------------
 441 MB
(1 row)

Time: 2.407 ms
nikola@[local] hstore_jsonb_test # select pg_size_pretty(pg_indexes_size('jsonb_table'));
 pg_size_pretty
----------------
 22 MB
(1 row)