I have a simple table that stores Ethereum addresses:

CREATE TABLE address (
    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 type, e.g.

INSERT INTO address (address) VALUES (decode('0ef6F5fc97b1DC5601F858159e1C410Ae2306A47', 'hex'));

and they are looked up using:

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?

  • Why not simply store them say as strings and not go though the encoding and decoding? I don't see any huge gains there... Even a small query if it's being run many times will use resources...
    – Vérace
    Jun 1, 2021 at 6:02
  • If the concern here is the encoding speed, the encoding is done in the application, not database. I just send the buffer.
    – Gajus
    Jun 1, 2021 at 13:48
  • If you sort a non-empty list, something will always come out on top. This does not indicate that there is a problem. Is there a problem here to solve? 0.026ms does not seem to indicate one.
    – jjanes
    Jun 1, 2021 at 14:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.