I have a simple table that stores Ethereum addresses:
CREATE TABLE address (
id SERIAL PRIMARY KEY,
address bytea NOT NULL,
attributes text[] NOT NULL DEFAULT '{}'::text[]
);
CREATE UNIQUE INDEX address_address_idx ON address(address bytea_ops);
The addresses are stored in a column using bytea type, e.g.
INSERT INTO address (address) VALUES (decode('0ef6F5fc97b1DC5601F858159e1C410Ae2306A47', 'hex'));
and they are looked up using:
EXPLAIN ANALYZE
SELECT encode(address, 'hex')
FROM address
WHERE address = decode('78f294e4a5c73b0b6b230c2e4104e8852f16a3a4', 'hex');
which uses index as expected:
Index Only Scan using address_address_idx on address (cost=0.42..2.65 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (address = '\x78f294e4a5c73b0b6b230c2e4104e8852f16a3a4'::bytea)
Heap Fetches: 0
Planning Time: 0.103 ms
Execution Time: 0.026 ms
However, because of how often this query is called, it is among the top 3 most exec time taking queries (according pg_stat_statements).
What can I do to increase efficiency of lookup?
