Missing Indexes in Google BigQuery for Transactions

Question

As the title says, there are Indexes missing for the Google Cloud BigQuery Dataset. This makes the Transaction database insanely expensive to query.

Could you pls add indexes (at least one for Block_Slots, that would be enough for the beginning) to reduce the costs significantly when querying the Transactions table.

As you can see in the Top Right Corner, it costs 471 TB to run this simple query (1TB costs 6$, so it would cost >2800$)

It would be perfect if you could add indexes to every field, if not, those are in my eyes the most important:

1. block_slot
2. accounts.pubkey
3. signer
4. signature

Thanks!

Answer 1

This is the wrong place for your comment, since you're asking for a feature, rather than describing a Solana programming problem.

With that said, where are you seeing this data source exactly? The tables in the validator are blocks, entries, tx-by-addr, and tx, which you can see in the source code at https://github.com/anza-xyz/agave/blob/d3bce9f02414e47b7f498d71503f8061a75d72b0/storage-bigtable/src/lib.rs#L1007

Those tables actually cover everything you want:

• block_slot -> blocks
• accounts.pubkey -> tx-by-addr
• signer -> tx-by-addr
• signature -> tx

If you use the public Solana RPC nodes, you can query for all of these, but you'll have to go block by block rather than get everything all at once, ie getBlock: https://solana.com/docs/rpc/http/getblock