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

1. 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

2. Filter out failed/success and voting/nonvoting records with:

   • for transactions, use success and is_voting
   • for (inner)instructions, use parent_tx_success and is_voting

3. 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 for success_nonvoting_transactions). For more info, refer to transaction-level columns.