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:

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

  4. For transaction fees, you can find them at solana.raw.fees, they are also available in transaction-level columns for convenience.