Sample Header Ad - 728x90

Optimal index for storing crypto addresses using bytea

1 vote
0 answers
2265 views
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 [tag: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?
Asked by Gajus (1334 rep)
May 31, 2021, 11:57 PM
Last activity: May 9, 2023, 09:34 AM