Getting Started

Please consider optimizing your query if it is running slow or timing out. Also consider opening an issue so we can improve the speed of that type of query.

Consider getting the data in smaller parts. For example, it is not a good idea to download all the operations (+ 1TB) in a single query.

HafSQL only handles the irreversible data. This doesn't have any meaningful impacts in practice as Hive blocks become irreversible within a second.

Sometimes joining can make a query very slow. Experimenting and checking other ways of running the same query is usually the best approach.

Be aware of using OR in the conditions of a query. The same goes for =ANY() or IN (). PostgreSQL will sometimes skip using indexes and make the query very slow. The solution here is to use UNION [ALL] instead.

Bad:

SELECT * FROM x WHERE a = 1 OR a = 2

SELECT * FROM x WHERE a IN (1, 2)

Good:

SELECT * FROM x WHERE a = 1
UNION ALL
SELECT * FROM x WHERE a = 2

Sometimes using a LIMIT or a very low LIMIT in your queries can slow down the query. This is a limitaion of the PostgreSQL query planner. For more information check out "LIMIT considered harmful" by CEO of PostgreSQL.
This doesn't mean you shouldn't use LIMIT. Use LIMIT but when your query is slow, make sure it is not because of the LIMIT.