Query Optimizations
This page describes some frequently used tips and tricks to improve query performance when querying historical data.
General tips and tricks
Most time-based tables are clustered by block_timestamp::date
, so it’s useful to have block_timestamp
in join conditions
Use CTEs to filter source data before the joins in the SQL.
Ecosystem specific tips
Solana
-
When querying raw entities such as transactions, instructions, inner_instructions, if you do not need voting data, you can use one of the corresponding optimized views:
-
transactions
success_nonvoting_transactions
nonvoting_transactions
-
instructions
success_nonvoting_instructions
nonvoting_instructions
-
inner_instructions
success_nonvoting_inner_instructions
nonvoting_inner_instructions
-
inner_outer_instructions
success_nonvoting_inner_outer_instructions
nonvoting_inner_outer_instructions
-
-
Filter out failed/success and voting/nonvoting records with:
- for transactions, use
success
andis_voting
- for (inner)instructions, use
parent_tx_success
andis_voting
- for transactions, use
-
For transaction meta columns, the rpc returns pre/post token/native balances. we have reformatted these cols to be more user-friendly in the columns
sol_amounts
,mint_to_decimals
,token_accounts
for your convenience. (only available forsuccess_nonvoting_transactions
). For more info, refer to transaction-level columns.