add index for range scans on views

[maryliag]

Add the ability to add/use index for range scans on virtual tables.

We have a feature where we display the list of fingerprints used per index, and to be possible we do a select such as
SELECT * FROM system.statement_statistics WHERE "1@1"::jsonb <@ indexes_usage
but we have to use the system table, since this is not possible on the crdb_internal.statement_statistics virtual table.
We don't let non-admin users to do selects on system tables, so using the crdb_internal table without the index is very inefficient.

Jira issue: CRDB-23726

[cucaroach]

What's the level of urgency of this?

[cucaroach]

Possibly related: #74806

[maryliag]

What's the level of urgency of this?

The feature mentioned on the description (listing fingerprints used by index) will have to be hidden to non-admins until this task is completed, so the sooner the better.

Feel free to close this one in favour of the task you tagged on your message.

[ajwerner]

Another possible approach could be for us to:

1. Implement set-returning functions (we're planning to do this)
2. Implement the SECURITY DEFINER feature on functions so that the function can run as its owner

Then we could have the function do the nice query parameterized as we want it and then grant access to the function somehow via a role?

cc @mgartner and @chengxiong-ruan

[mgartner]

Can we not already do the exact same thing as @ajwerner describes but with views? Or is it impossible to create a role that can access a view but not the tables it references?

[ajwerner]

What is the privilege model here? I assumed it was complex, but if it's simple, views should do the trick.

[michae2]

[sql queries triage meeting] @maryliag would using views work, as mentioned above?

[maryliag]

I don't understand how that works, for example, what I would need to do to make a select as
SELECT * FROM crdb_internal.statement_statistics WHERE "1@1"::jsonb <@ indexes_usage ?

[ajwerner]

Okay, so the summary of the problem as I understand it is:

1. We have a view which today combines the data from the system table and the in-memory data (crdb_internal.statement_statistics).
2. We have a good index on the system table for the query we'd like to write.
3. What we don't have is a way to push the query bounds into the in-memory data, so we end up materializing all of it.
4. Maybe we don't care about the in-memory data, and we only care about the durable data, so we'd like to just scan the system table
5. End users can't just scan the system table because they don't have sufficient privileges.

The proposed solution is:

• Create a virtual view which wraps just querying the system table.
• Query that view.

The advantage of this over the system table is that you can grant privileges to these virtual tables now. An example of such a a wrapping is this:

--- a/pkg/sql/crdb_internal.go
+++ b/pkg/sql/crdb_internal.go
@@ -182,6 +182,7 @@ var crdbInternal = virtualSchema{
                catconstants.CrdbInternalTablesTableID:                      crdbInternalTablesTable,
                catconstants.CrdbInternalClusterTxnStatsTableID:             crdbInternalClusterTxnStatsTable,
                catconstants.CrdbInternalTxnStatsTableID:                    crdbInternalTxnStatsView,
+               catconstants.CrdbInternalStatementStatisticsTableID:         crdbInternalStatementStatisticsView,
                catconstants.CrdbInternalTransactionStatsTableID:            crdbInternalTransactionStatisticsTable,
                catconstants.CrdbInternalZonesTableID:                       crdbInternalZonesTable,
                catconstants.CrdbInternalInvalidDescriptorsTableID:          crdbInternalInvalidDescriptorsTable,
@@ -6297,6 +6298,34 @@ CREATE TABLE crdb_internal.cluster_transaction_statistics (
        },
 }
 
+var crdbInternalStatementStatisticsView = virtualSchemaView{
+       schema: `CREATE VIEW crdb_internal.statement_statistics2 AS SELECT
+                       aggregated_ts,
+                       fingerprint_id,
+                       transaction_fingerprint_id,
+                       plan_hash,
+                       app_name,
+                       node_id,
+                       agg_interval,
+                       metadata,
+                       statistics,
+                       plan,
+                       index_recommendations FROM system.statement_statistics`,
+       resultColumns: colinfo.ResultColumns{
+               {Name: "aggregated_ts", Typ: types.TimestampTZ},
+               {Name: "fingerprint_id", Typ: types.Bytes},
+               {Name: "transaction_fingerprint_id", Typ: types.Bytes},
+               {Name: "plan_hash", Typ: types.Bytes},
+               {Name: "app_name", Typ: types.String},
+               {Name: "node_id", Typ: types.Int},
+               {Name: "agg_interval", Typ: types.Interval},
+               {Name: "metadata", Typ: types.Jsonb},
+               {Name: "statistics", Typ: types.Jsonb},
+               {Name: "plan", Typ: types.Jsonb},
+               {Name: "index_recommendations", Typ: types.StringArray},
+       },
+}
+

[maryliag]

To the point 4: I'm only using the system table because there is no way to do that on the combined view. My goal is to do that select on the combined view, because I do care with the latest data (that only exists in-memory)

The point number 5 can be done as a workaround, until I can make the call to the combined view.

[ajwerner]

Great. This issue does seem fitting as written for point 4. We need to have more advanced predicate push down into virtual tables and virtual indexes to realize the vision.

[yuzefovich]

In a recent escalation, we had a system.statement_statistics table with 2.9TiB of data, and queries that power the SQL Activity page were performing a full scan of that table. Fixing this issue would have (probably) helped there and perhaps made the escalation be resolved faster.

[mgartner]

@maryliag When we spoke IRL I believe you mentioned that you are not longer combining the in-memory data with persisted data because fanning out to each node to retrieve the in-memory data was too slow. I'm going to close this issue, because I don't think there's anything to be done here, but let me know if I'm mistaken.