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'"

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on hstore_table  (cost=709.50..28265.58 rows=10000 width=28) (actual time=387.284..3708.824 rows=1000000 loops=1)
   Recheck Cond: (settings ? 'ivan'::text)
   Rows Removed by Index Recheck: 3233899
   Heap Blocks: exact=47110 lossy=26420
   ->  Bitmap Index Scan on hstore_index  (cost=0.00..707.00 rows=10000 width=0) (actual time=359.181..359.181 rows=1000000 loops=1)
         Index Cond: (settings ? 'ivan'::text)
 Planning time: 2.165 ms
 Execution time: 3865.153 ms
(8 rows)

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonb_table  (cost=641.50..28197.58 rows=10000 width=28) (actual time=391.666..3348.357 rows=1000000 loops=1)
   Recheck Cond: (settings ? 'ivan'::text)
   Rows Removed by Index Recheck: 3233899
   Heap Blocks: exact=47110 lossy=26420
   ->  Bitmap Index Scan on jsonb_index  (cost=0.00..639.00 rows=10000 width=0) (actual time=367.258..367.258 rows=1000000 loops=1)
         Index Cond: (settings ? 'ivan'::text)
 Planning time: 2.527 ms
 Execution time: 3500.575 ms
(8 rows)

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)