1

When I want to query a tx by hash I can do this:

select * from tx where encode(hash::bytea, 'hex') = '28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758'

...but the hash, stored as bytea then makes the query expensive.

If given a hash string, is there a more efficient way to retrieve the record from the tx table?

1 Answer 1

3

Fortunately, your query can be written more simply as:

select * from tx where hash = '\x28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758' ;

Note that I have prepended "\x" to the hash so that Postgres treats the hash as hexadecimal (bytea).

When I run my version of your query on a fully synced mainnet database, the query returns the result (as single row) in under a second while your query takes over 10 seconds.

I am pretty sure your version takes the hash of every row of the table, encodes it and then compares it with your provided hash, while my just directly compares it with no conversion.

5
  • 2
    Fortunately indeed! Just ran it now and it is MUCH faster. I have some queries to update. Thank you! Aug 12, 2023 at 21:27
  • 1
    "binary value represented in hexadecimal format using the '\x' notation allows for a direct binary comparison without the need for any additional encoding or conversion." Aug 12, 2023 at 21:44
  • 1
    I posted this in a TG channel I am in and someone suggested that the encode(hash::bytea, 'hex') construct means that Postgres is not able to use any index it has on that field. Aug 12, 2023 at 22:46
  • 1
    ah - that makes sense. Thank you. Aug 12, 2023 at 22:55
  • 1
    Someone else on that channel also suggested that select * from tx where hash = decode('28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758', 'hex'); is equivalent to mine. Aug 13, 2023 at 1:40

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.