<p>Fortunately, your query can be written more simply as:</p>
<pre><code>select * from tx where hash = '\x28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758' ;
</code></pre>
<p>Note that I have prepended &quot;\x&quot; to the hash so that Postgres treats the hash as hexadecimal (bytea).</p>
<p>When I run my version of your query on a fully synced <code>mainnet</code> database, the query returns the result (as single row) in under a second while your query takes over 10 seconds.</p>
<p>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.</p>

CC BY-SA 4.0
2023-08-12T21:27:15.117Z
Fortunately indeed! Just ran it now and it is MUCH faster.
I have some queries to update.
Thank you!
RelevantData
CC BY-SA 4.0
2023-08-12T21:44:31.987Z
"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."
RelevantData
CC BY-SA 4.0
2023-08-12T22:46:44.820Z
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.
Erik de Castro Lopo
CC BY-SA 4.0
2023-08-12T22:55:52.860Z
ah - that makes sense. Thank you.
RelevantData
CC BY-SA 4.0
2023-08-13T01:40:23.820Z
Someone else on that channel also suggested that `select * from tx where hash = decode('28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758', 'hex');` is equivalent to mine.
Erik de Castro Lopo
CC BY-SA 4.0
2023-08-12T21:21:40.200Z
2023-08-12T21:34:33.610Z
2023-08-12T21:34:33.610Z
Erik de Castro Lopo
<p>When I want to query a tx by hash I can do this:</p>
<pre><code>select * from tx where encode(hash::bytea, 'hex') = '28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758'
</code></pre>
<p>...but the hash, stored as bytea then makes the query expensive.</p>
<p>If given a hash string, is there a more efficient way to retrieve the record from the tx table?</p>

<cardano-db-sync>
Querying a transaction by hash vs id
# Querying a transaction by hash vs id

(Asked by: username_0 on 2023-08-12)

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?

**Tags:** cardano-db-sync

---

## 1 answers

### Answer by username_1 on 2023-08-12. Score: 3 | Accepted answer

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.

> **Comments:**
> **username_0**: Fortunately indeed! Just ran it now and it is MUCH faster. I have some queries to update. Thank you!  
> **username_0**: "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."  
> **username_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.  
> **username_0**: ah - that makes sense. Thank you.  
> **username_1**: Someone else on that channel also suggested that `select * from tx where hash = decode('28b492be2f733201ba3441d5d7d0fefc097c3deba3f299628680fae484293758', 'hex');` is equivalent to mine.  

---

