Skip to content

Instantly share code, notes, and snippets.

@abuiles
Last active August 10, 2020 21:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save abuiles/ec64f1728c67a516cbc0b878d6fd09e0 to your computer and use it in GitHub Desktop.
Save abuiles/ec64f1728c67a516cbc0b878d6fd09e0 to your computer and use it in GitHub Desktop.
CREATE TABLE accounts(address text NOT NULL);
INSERT INTO accounts(address) SELECT md5(RANDOM()::TEXT) FROM generate_series(1, 500000);
CREATE TABLE balances(claimants JSONB NOT NULL);
CREATE INDEX balances_gin_idx ON balances USING gin(claimants jsonb_path_ops);
-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;
-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;
-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;
-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;
-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;
-- RUN THE FOLLOWING SQL IN THE QUERY BUILDER ON THE RIGHT
--
-- get a random destination with the following command
SELECT json_build_array(json_build_object('destination',a.address)) FROM accounts as a ORDER BY RANDOM () LIMIT 1
-- replace the query below with the result from above
-- pick on destination from the list above and replace in the following query
SELECT * FROM balances WHERE claimants @> '[{"destination" : "751c2aa04ba42311556fae785fe4a7c9"}]';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment