--hstore — indexed key/value semi-structured data 
--hstore is one of the contrib modules listed as providing GiST operator classes.

CREATE EXTENSION IF NOT EXISTS hstore;

DROP TABLE IF EXISTS device_config;
CREATE TABLE device_config
(
    device_id     bigserial PRIMARY KEY,
    model_name    text NOT NULL,
    attrs         hstore NOT NULL
);

INSERT INTO device_config (model_name, attrs)
VALUES
('router_x1', 'vendor=>Cisco,ram=>16,ports=>24,region=>EU'),
('router_x2', 'vendor=>Juniper,ram=>32,ports=>48,region=>US'),
('switch_a1', 'vendor=>Cisco,ram=>8,ports=>48,region=>EU'),
('firewall_f1', 'vendor=>PaloAlto,ram=>64,ports=>8,region=>ME');

CREATE INDEX ix_device_config_gist
    ON device_config
 USING gist (attrs);
--Find configs containing required key/value subset
SELECT device_id, model_name, attrs
FROM device_config
WHERE attrs @> 'vendor=>Cisco,region=>EU'::hstore;
--Find rows having overlap with requested keys
SELECT device_id, model_name
FROM device_config
WHERE attrs ?& ARRAY['vendor', 'ram'];
--------------------------------------------------------
--------------------------------------------------------
--intarray — integer-array overlap / contains
--The intarray extension provides specialized GiST operator classes for integer arrays. PostgreSQL documents both gist__int_ops and gist__intbig_ops.

CREATE EXTENSION IF NOT EXISTS intarray;

DROP TABLE IF EXISTS fraud_cases;
CREATE TABLE fraud_cases
(
    case_id        bigserial PRIMARY KEY,
    rule_hits      int[] NOT NULL,
    severity       int NOT NULL
);

INSERT INTO fraud_cases (rule_hits, severity)
SELECT
    ARRAY[
        1 + (random() * 50)::int,
        1 + (random() * 50)::int,
        1 + (random() * 50)::int,
        1 + (random() * 50)::int
    ],
    1 + (random() * 5)::int
FROM generate_series(1, 200000);

CREATE INDEX ix_fraud_cases_gist
    ON fraud_cases
 USING gist (rule_hits gist__int_ops);
--Cases hit by overlapping rule set
SELECT case_id, rule_hits, severity
FROM fraud_cases
WHERE rule_hits && ARRAY[7, 11, 25];
--Cases containing all requested rules
SELECT case_id, rule_hits
FROM fraud_cases
WHERE rule_hits @> ARRAY[7, 11];