Skip to content

Instantly share code, notes, and snippets.

@chochkov
Last active April 30, 2016 11:47
Show Gist options
  • Save chochkov/4cea09a04314b29a7bf2a1b06614b70f to your computer and use it in GitHub Desktop.
Save chochkov/4cea09a04314b29a7bf2a1b06614b70f to your computer and use it in GitHub Desktop.
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'"
@chochkov
Copy link
Author

                                                             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)

@chochkov
Copy link
Author

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment