[v10.2.x] Search: Modify query for better performance #77713

papagian · 2023-11-06T13:58:34Z

Backport f999fe3 from #77576

What is this feature?

A customer has reported that they managed to improve search performance in their instance by introducing an index on dashboard.uid.
After investigation we realised that the UQE_dashboard_org_id_uid was not used.
This fix modifies the query to add org_id in the condition so that the above index is used.
It re-introduces also benchmarking accessControlDashboardPermissionFilter permissions filter (in addition to the experimental accessControlDashboardPermissionFilterNoFolderSubquery one)

Why do we need this feature?

Execution plan for the query before the fix

mysql> EXPLAIN SELECT
    ->             dashboard.id,
    ->             dashboard.uid,
    ->             dashboard.title,
    ->             dashboard.slug,
    ->             dashboard_tag.term,
    ->             dashboard.is_folder,
    ->             dashboard.folder_id,
    ->             folder.uid AS folder_uid,
    ->             folder.slug AS folder_slug,
    ->             folder.title AS folder_title  FROM ( SELECT dashboard.id FROM dashboard WHERE ((dashboard.uid IN (SELECT substr(scope, 16) FROM permission WHERE scope LIKE 'dashboards:uid:%' AND role_id IN(SELECT id FROM role INNER JOIN (
    ->             SELECT ur.role_id
    ->             FROM user_role AS ur
    ->             WHERE ur.user_id = 4816
    ->             AND (ur.org_id = 1 OR ur.org_id = 0)
    ->         UNION
    ->             SELECT br.role_id FROM builtin_role AS br
    ->             WHERE br.role IN ("Editor")
    ->             AND (br.org_id = 1 OR br.org_id = 0)
    ->         ) as all_role ON role.id = all_role.role_id)  AND action = "dashboards:read") AND NOT dashboard.is_folder) OR (dashboard.folder_id IN (SELECT id FROM dashboard as d WHERE d.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%'  AND role_id IN(SELECT id FROM role INNER JOIN (
    ->             SELECT ur.role_id
    ->             FROM user_role AS ur
    ->             WHERE ur.user_id = 4816
    ->             AND (ur.org_id = 1 OR ur.org_id = 0)
    ->         UNION
    ->             SELECT br.role_id FROM builtin_role AS br
    ->             WHERE br.role IN ("Editor")
    ->             AND (br.org_id = 1 OR br.org_id = 0)
    ->         ) as all_role ON role.id = all_role.role_id)  AND action = "dashboards:read")) AND NOT dashboard.is_folder) OR (dashboard.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%' AND role_id IN(SELECT id FROM role INNER JOIN (
    ->             SELECT ur.role_id
    ->             FROM user_role AS ur
    ->             WHERE ur.user_id = 4816
    ->             AND (ur.org_id = 1 OR ur.org_id = 0)
    ->         UNION
    ->             SELECT br.role_id FROM builtin_role AS br
    ->             WHERE br.role IN ("Editor")
    ->             AND (br.org_id = 1 OR br.org_id = 0)
    ->         ) as all_role ON role.id = all_role.role_id)  AND action = "folders:read") AND dashboard.is_folder)) AND dashboard.org_id=1 AND dashboard.title LIKE "%genius%" ORDER BY dashboard.title ASC LIMIT 1000 OFFSET 0) AS ids
    ->         INNER JOIN dashboard ON ids.id = dashboard.id
    -> LEFT OUTER JOIN dashboard AS folder ON folder.id = dashboard.folder_id
    ->         LEFT OUTER JOIN dashboard_tag ON dashboard.id = dashboard_tag.dashboard_id
    -> ORDER BY dashboard.title ASC;
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+----------+---------------------------------------------------------+
| id | select_type  | table         | partitions | type   | possible_keys                                                                                                                                                                   | key                                  | key_len | ref                         | rows | filtered | Extra                                                   |
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+----------+---------------------------------------------------------+
|  1 | PRIMARY      | <derived2>    | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                        |   15 |   100.00 | Using temporary; Using filesort                         |
|  1 | PRIMARY      | dashboard     | NULL       | eq_ref | PRIMARY                                                                                                                                                                         | PRIMARY                              | 8       | ids.id                      |    1 |   100.00 | NULL                                                    |
|  1 | PRIMARY      | folder        | NULL       | eq_ref | PRIMARY                                                                                                                                                                         | PRIMARY                              | 8       | grafana.dashboard.folder_id |    1 |   100.00 | NULL                                                    |
|  1 | PRIMARY      | dashboard_tag | NULL       | ref    | IDX_dashboard_tag_dashboard_id                                                                                                                                                  | IDX_dashboard_tag_dashboard_id       | 8       | ids.id                      |    2 |   100.00 | NULL                                                    |
|  2 | DERIVED      | dashboard     | NULL       | ref    | UQE_dashboard_org_id_folder_id_title,UQE_dashboard_org_id_uid,UQE_dashboard_org_id_folder_uid_title,IDX_dashboard_org_id,IDX_dashboard_org_id_plugin_id,IDX_dashboard_is_folder | UQE_dashboard_org_id_folder_id_title | 8       | const                       |  141 |    10.83 | Using index condition; Using where; Using filesort      |
| 14 | SUBQUERY     | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                        |    3 |   100.00 | Using index                                             |
| 14 | SUBQUERY     | <derived16>   | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id             |    1 |   100.00 | Using index                                             |
| 14 | SUBQUERY     | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const       |   12 |    11.11 | Using where; Using index                                |
| 16 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                       |    1 |   100.00 | Using where                                             |
| 17 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                       |    1 |   100.00 | Using where                                             |
| 18 | UNION RESULT | <union16,17>  | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                        | NULL |     NULL | Using temporary                                         |
|  8 | SUBQUERY     | <subquery9>   | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                        | NULL |   100.00 | NULL                                                    |
|  8 | SUBQUERY     | d             | NULL       | index  | PRIMARY                                                                                                                                                                         | UQE_dashboard_org_id_uid             | 171     | NULL                        |  113 |    10.00 | Using where; Using index; Using join buffer (hash join) |
|  9 | MATERIALIZED | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                        |    3 |   100.00 | Using index                                             |
|  9 | MATERIALIZED | <derived11>   | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id             |    1 |   100.00 | Using index                                             |
|  9 | MATERIALIZED | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const       |   12 |    11.11 | Using where; Using index                                |
| 11 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                       |    1 |   100.00 | Using where                                             |
| 12 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                       |    1 |   100.00 | Using where                                             |
| 13 | UNION RESULT | <union11,12>  | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                        | NULL |     NULL | Using temporary                                         |
|  3 | SUBQUERY     | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                        |    3 |   100.00 | Using index                                             |
|  3 | SUBQUERY     | <derived5>    | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id             |    1 |   100.00 | Using index                                             |
|  3 | SUBQUERY     | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const       |   12 |    11.11 | Using where; Using index                                |
|  5 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                       |    1 |   100.00 | Using where                                             |
|  6 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                       |    1 |   100.00 | Using where                                             |
|  7 | UNION RESULT | <union5,6>    | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                        | NULL |     NULL | Using temporary                                         |
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+----------+---------------------------------------------------------+
25 rows in set, 1 warning (0.02 sec)

Execution plan the query after the fix

mysql> EXPLAIN SELECT
    ->                         dashboard.id,
    ->                         dashboard.uid,
    ->                         dashboard.title,
    ->                         dashboard.slug,
    ->                         dashboard_tag.term,
    ->                         dashboard.is_folder,
    ->                         dashboard.folder_id,
    ->                         folder.uid AS folder_uid,
    -> 
    ->                         folder.slug AS folder_slug,
    ->                         folder.title AS folder_title  FROM ( SELECT dashboard.id FROM dashboard WHERE ((dashboard.uid IN (SELECT substr(scope, 16) FROM permission WHERE scope LIKE 'dashboards:uid:%' AND role_id IN(SELECT id FROM role INNER JOIN (
    ->                         SELECT ur.role_id
    ->                         FROM user_role AS ur
    ->                         WHERE ur.user_id = 4816
    ->                         AND (ur.org_id = 1 OR ur.org_id = 0)
    ->                 UNION
    ->                         SELECT br.role_id FROM builtin_role AS br
    ->                         WHERE br.role IN ("Editor")
    ->                         AND (br.org_id = 1 OR br.org_id = 0)
    ->                 ) as all_role ON role.id = all_role.role_id)  AND action =  "dashboards:read") AND NOT dashboard.is_folder) OR (dashboard.folder_id IN (SELECT d.id FROM dashboard as d WHERE d.org_id = 1 AND d.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%'  AND role_id IN(SELECT id FROM role INNER JOIN (
    ->                         SELECT ur.role_id
    ->                         FROM user_role AS ur
    ->                         WHERE ur.user_id = 4816
    ->                         AND (ur.org_id = 1 OR ur.org_id = 0)
    ->                 UNION
    ->                         SELECT br.role_id FROM builtin_role AS br
    ->                         WHERE br.role IN ("Editor")
    ->                         AND (br.org_id = 1 OR br.org_id = 0)
    ->                 ) as all_role ON role.id = all_role.role_id)  AND action = "dashboards:read")) AND NOT dashboard.is_folder) OR (dashboard.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%' AND role_id IN(SELECT id FROM role INNER JOIN (
    ->                         SELECT ur.role_id
    ->                         FROM user_role AS ur
    ->                         WHERE ur.user_id = 4816
    ->                         AND (ur.org_id = 1 OR ur.org_id = 0)
    ->                 UNION
    ->                         SELECT br.role_id FROM builtin_role AS br
    ->                         WHERE br.role IN ("Editor")
    ->                         AND (br.org_id = 1 OR br.org_id = 0)
    ->                 ) as all_role ON role.id = all_role.role_id)  AND action = "folders:read") AND dashboard.is_folder)) AND dashboard.org_id=1 AND dashboard.title LIKE "%genius%" ORDER BY dashboard.title ASC LIMIT 1000 OFFSET 0) AS ids
    ->                 INNER JOIN dashboard ON ids.id = dashboard.id
    -> 
    ->                 LEFT OUTER JOIN dashboard AS folder ON folder.id = dashboard.folder_id
    ->         LEFT OUTER JOIN dashboard_tag ON dashboard.id = dashboard_tag.dashboard_id
    ->  ORDER BY dashboard.title ASC;
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-------------------------------------+------+----------+----------------------------------------------------+
| id | select_type  | table         | partitions | type   | possible_keys                                                                                                                                                                   | key                                  | key_len | ref                                 | rows | filtered | Extra                                              |
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-------------------------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY      | <derived2>    | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                                |   15 |   100.00 | Using temporary; Using filesort                    |
|  1 | PRIMARY      | dashboard     | NULL       | eq_ref | PRIMARY                                                                                                                                                                         | PRIMARY                              | 8       | ids.id                              |    1 |   100.00 | NULL                                               |
|  1 | PRIMARY      | folder        | NULL       | eq_ref | PRIMARY                                                                                                                                                                         | PRIMARY                              | 8       | grafana.dashboard.folder_id         |    1 |   100.00 | NULL                                               |
|  1 | PRIMARY      | dashboard_tag | NULL       | ref    | IDX_dashboard_tag_dashboard_id                                                                                                                                                  | IDX_dashboard_tag_dashboard_id       | 8       | ids.id                              |    2 |   100.00 | NULL                                               |
|  2 | DERIVED      | dashboard     | NULL       | ref    | UQE_dashboard_org_id_folder_id_title,UQE_dashboard_org_id_uid,UQE_dashboard_org_id_folder_uid_title,IDX_dashboard_org_id,IDX_dashboard_org_id_plugin_id,IDX_dashboard_is_folder | UQE_dashboard_org_id_folder_id_title | 8       | const                               |  141 |    10.83 | Using index condition; Using where; Using filesort |
| 14 | SUBQUERY     | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                                |    3 |   100.00 | Using index                                        |
| 14 | SUBQUERY     | <derived16>   | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id                     |    1 |   100.00 | Using index                                        |
| 14 | SUBQUERY     | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const               |   12 |    11.11 | Using where; Using index                           |
| 16 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                               |    1 |   100.00 | Using where                                        |
| 17 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                               |    1 |   100.00 | Using where                                        |
| 18 | UNION RESULT | <union16,17>  | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                                | NULL |     NULL | Using temporary                                    |
|  8 | SUBQUERY     | <subquery9>   | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                                | NULL |   100.00 | Using where                                        |
|  8 | SUBQUERY     | d             | NULL       | eq_ref | PRIMARY,UQE_dashboard_org_id_folder_id_title,UQE_dashboard_org_id_uid,UQE_dashboard_org_id_folder_uid_title,IDX_dashboard_org_id,IDX_dashboard_org_id_plugin_id                 | UQE_dashboard_org_id_uid             | 171     | const,<subquery9>.substr(scope, 13) |    1 |   100.00 | Using where; Using index                           |
|  9 | MATERIALIZED | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                                |    3 |   100.00 | Using index                                        |
|  9 | MATERIALIZED | <derived11>   | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id                     |    1 |   100.00 | Using index                                        |
|  9 | MATERIALIZED | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const               |   12 |    11.11 | Using where; Using index                           |
| 11 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                               |    1 |   100.00 | Using where                                        |
| 12 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                               |    1 |   100.00 | Using where                                        |
| 13 | UNION RESULT | <union11,12>  | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                                | NULL |     NULL | Using temporary                                    |
|  3 | SUBQUERY     | role          | NULL       | index  | PRIMARY                                                                                                                                                                         | IDX_role_org_id                      | 8       | NULL                                |    3 |   100.00 | Using index                                        |
|  3 | SUBQUERY     | <derived5>    | NULL       | eq_ref | <auto_distinct_key>                                                                                                                                                             | <auto_distinct_key>                  | 8       | grafana.role.id                     |    1 |   100.00 | Using index                                        |
|  3 | SUBQUERY     | permission    | NULL       | ref    | UQE_permission_role_id_action_scope,IDX_permission_role_id                                                                                                                      | UQE_permission_role_id_action_scope  | 770     | grafana.role.id,const               |   12 |    11.11 | Using where; Using index                           |
|  5 | DERIVED      | ur            | NULL       | ref    | UQE_user_role_org_id_user_id_role_id,IDX_user_role_org_id,IDX_user_role_user_id                                                                                                 | IDX_user_role_user_id                | 8       | const                               |    1 |   100.00 | Using where                                        |
|  6 | UNION        | br            | NULL       | ref    | UQE_builtin_role_org_id_role_id_role,IDX_builtin_role_role,IDX_builtin_role_org_id                                                                                              | IDX_builtin_role_role                | 762     | const                               |    1 |   100.00 | Using where                                        |
|  7 | UNION RESULT | <union5,6>    | NULL       | ALL    | NULL                                                                                                                                                                            | NULL                                 | NULL    | NULL                                | NULL |     NULL | Using temporary                                    |
+----+--------------+---------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+-------------------------------------+------+----------+----------------------------------------------------+
25 rows in set, 1 warning (0.01 sec)

The difference is in subquery d.

Who is this feature for?

[Add information on what kind of user the feature is for.]

Which issue(s) does this PR fix?:

Fixes #

Special notes for your reviewer:

Query diff before and after the fix

diff query-before.sql query-after.sql|more
20c20
<         ) as all_role ON role.id = all_role.role_id)  AND action = "dashboards:read") AND NOT dashboard.is_folder) OR (dashboard.folder_id IN (SELECT id FROM dashboard as d WHERE d.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%'  AND role_id IN(SELECT id FROM role INNER JOIN (
---
>         ) as all_role ON role.id = all_role.role_id)  AND action = "dashboards:read") AND NOT dashboard.is_folder) OR (dashboard.folder_id IN (SELECT d.id FROM dashboard as d WHERE d.org_id = 1 AND d.uid IN (SELECT substr(scope, 13) FROM permission WHERE scope LIKE 'folders:uid:%'  AND role_id IN(SELECT id FROM role INNER JOIN (

I have run benchmarks before and after the fix and you can examine the results below:

benchstat results

WITHOUT_FIX=144482
WITH_FIX=144815
declare -A ci_step_map
ci_step_map['mysql8']=16
ci_step_map['postgres']=14
ci_step_map['sqlite']=13
for key val in "${(@kv)ci_step_map}"; do
    echo "
"
    echo "$key -> $val"
    benchstat logs_grafana_grafana_${WITHOUT_FIX}_1_${val}.log logs_grafana_grafana_${WITH_FIX}_1_${val}.log
done | tee benchmarks.out

'postgres' -> 14
goos: linux
goarch: amd64
pkg: github.com/grafana/grafana/pkg/api
cpu: Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
                                                                                      │ logs_grafana_grafana_144482_1_14.log │ logs_grafana_grafana_144815_1_14.log │
                                                                                      │                sec/op                │    sec/op      vs base               │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                     7.364m ± 6%     6.972m ± 3%        ~ (p=0.195 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                  662.3m ± 1%     392.6m ± 1%  -40.72% (p=0.000 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                            492.2m ± 1%     373.3m ± 0%  -24.15% (p=0.000 n=8)
geomean                                                                                                          133.9m          100.7m       -24.77%

                                                                                      │ logs_grafana_grafana_144482_1_14.log │ logs_grafana_grafana_144815_1_14.log │
                                                                                      │                 B/op                 │      B/op       vs base              │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                    1.035Mi ± 0%     1.036Mi ± 0%       ~ (p=0.798 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                 21.31Mi ± 9%     22.63Mi ± 3%       ~ (p=0.083 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                           75.06Ki ± 2%     74.27Ki ± 3%       ~ (p=0.105 n=8)
geomean                                                                                                         1.174Mi          1.194Mi       +1.69%

                                                                                      │ logs_grafana_grafana_144482_1_14.log │ logs_grafana_grafana_144815_1_14.log │
                                                                                      │              allocs/op               │   allocs/op     vs base              │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                     20.25k ± 0%      20.25k ± 0%       ~ (p=0.170 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                  415.8k ± 0%      415.8k ± 0%       ~ (p=0.818 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                             678.0 ± 1%       679.0 ± 1%       ~ (p=0.781 n=8)
geomean                                                                                                          17.87k           17.88k       +0.05%


'sqlite' -> 13
goos: linux
goarch: amd64
pkg: github.com/grafana/grafana/pkg/api
cpu: Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
                                                                                      │ logs_grafana_grafana_144482_1_13.log │ logs_grafana_grafana_144815_1_13.log │
                                                                                      │                sec/op                │    sec/op      vs base               │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                     56.22m ± 4%     53.06m ± 1%   -5.63% (p=0.001 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                  559.4m ± 4%     410.8m ± 2%  -26.56% (p=0.000 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                            431.9m ± 1%     286.3m ± 0%  -33.71% (p=0.000 n=8)
geomean                                                                                                          238.6m          184.1m       -22.84%

                                                                                      │ logs_grafana_grafana_144482_1_13.log │ logs_grafana_grafana_144815_1_13.log │
                                                                                      │                 B/op                 │      B/op       vs base              │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                    1.086Mi ± 1%     1.086Mi ± 1%       ~ (p=0.798 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                 24.43Mi ± 8%     23.10Mi ± 6%       ~ (p=0.505 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                           66.36Ki ± 4%     65.37Ki ± 2%       ~ (p=0.195 n=8)
geomean                                                                                                         1.198Mi          1.170Mi       -2.34%

                                                                                      │ logs_grafana_grafana_144482_1_13.log │ logs_grafana_grafana_144815_1_13.log │
                                                                                      │              allocs/op               │   allocs/op     vs base              │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                     26.76k ± 0%      26.76k ± 0%       ~ (p=0.632 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                  565.6k ± 0%      565.6k ± 0%       ~ (p=0.593 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                             496.0 ± 2%       493.0 ± 1%       ~ (p=0.089 n=8)
geomean                                                                                                          19.58k           19.54k       -0.20%


'mysql8' -> 16
goos: linux
goarch: amd64
pkg: github.com/grafana/grafana/pkg/api
cpu: Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
                                                                                      │ logs_grafana_grafana_144482_1_16.log │ logs_grafana_grafana_144815_1_16.log │
                                                                                      │                sec/op                │    sec/op      vs base               │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                    9.321m ± 17%     7.988m ± 4%  -14.30% (p=0.021 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                 669.0m ±  9%     354.6m ± 6%  -46.99% (p=0.000 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                          153.925 ±  0%      3.992 ± 1%  -97.41% (p=0.000 n=8)
geomean                                                                                                         986.4m           224.5m       -77.24%

                                                                                      │ logs_grafana_grafana_144482_1_16.log │ logs_grafana_grafana_144815_1_16.log │
                                                                                      │                 B/op                 │     B/op       vs base               │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                    1.101Mi ± 0%    1.100Mi ± 0%        ~ (p=0.959 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                 26.38Mi ± 8%    25.05Mi ± 5%        ~ (p=0.959 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                          413.12Ki ± 5%    75.37Ki ± 2%  -81.76% (p=0.000 n=8)
geomean                                                                                                         2.271Mi         1.266Mi       -44.26%

                                                                                      │ logs_grafana_grafana_144482_1_16.log │ logs_grafana_grafana_144815_1_16.log │
                                                                                      │              allocs/op               │   allocs/op    vs base               │
FolderListAndSearch/get_root_folders_with_nested_folders_feature_disabled-32                                     24.55k ± 0%     24.55k ± 0%        ~ (p=1.000 n=8)
FolderListAndSearch/list_all_dashboards_with_nested_folders_feature_disabled-32                                  590.5k ± 0%     590.5k ± 0%        ~ (p=0.152 n=8)
FolderListAndSearch/search_specific_dashboard_with_nested_folders_feature_disabled-32                            6190.5 ± 6%      494.5 ± 4%  -92.01% (p=0.000 n=8)
geomean                                                                                                          44.77k          19.28k       -56.93%

Please check that:

It works as expected from a user's perspective.
If this is a pre-GA feature, it is behind a feature toggle.
The docs are updated, and if this is a notable improvement, it's added to our What's New doc.

* Add missing `org_id` in query condition * Update benchmarks (cherry picked from commit f999fe3)

Search: Modify query for better performance (#77576)

99747fd

* Add missing `org_id` in query condition * Update benchmarks (cherry picked from commit f999fe3)

papagian added type/bug area/backend add to changelog backport A backport PR product-approved Pull requests that are approved by product/managers and are allowed to be backported labels Nov 6, 2023

papagian added this to the 10.2.x milestone Nov 6, 2023

papagian requested a review from a team as a code owner November 6, 2023 13:58

papagian requested review from undef1nd, suntala and yangkb09 and removed request for a team November 6, 2023 13:58

undef1nd approved these changes Nov 6, 2023

View reviewed changes

papagian merged commit 94bef3c into v10.2.x Nov 6, 2023
24 checks passed

papagian deleted the backport-77576-to-v10.2.x branch November 6, 2023 14:24

aangelisc modified the milestones: 10.2.x, 10.2.1 Nov 13, 2023

BrewTestBot mentioned this pull request Nov 14, 2023

grafana 10.2.1 Homebrew/homebrew-core#154300

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[v10.2.x] Search: Modify query for better performance #77713

[v10.2.x] Search: Modify query for better performance #77713

papagian commented Nov 6, 2023

[v10.2.x] Search: Modify query for better performance #77713

[v10.2.x] Search: Modify query for better performance #77713

Conversation

papagian commented Nov 6, 2023