PostgreSQL query inventory - stolostron/search-v2-operator GitHub Wiki
Inventory of Postgres queries used by search and their expected execution plan.
These can be used to validate the Postgres configuration to avoid regressions or introducing changes that cause a negative impact in certain queries.
| Queries | Description | Expected execution plan | 50k (default settings) | 5Mil (50 cluster * 100k) |
|---|---|---|---|---|
| --- INDEXER QUERIES --- | ||||
| EXPLAIN ANALYZE SELECT uid, data FROM search.resources WHERE cluster='locust-2' AND uid!='cluster__locust-2'; | Resync: Get existing cluster resources. | Bitmap Index Scan on data_cluster_idx | 29 ms | 38 ms |
| EXPLAIN ANALYZE SELECT count(*) FROM search.resources WHERE cluster='locust-3' AND data->'_hubClusterResource' IS NOT NULL; | Sync: Total cluster resources used to validate data. | Bitmap Index Scan on data_cluster_idx | 44 ms | 51 ms |
| EXPLAIN ANALYZE SELECT sourceid, edgetype, destid FROM search.edges WHERE edgetype!='interCluster' AND cluster='locust-2'; | Resync: Get existing edges | Bitmap Index Scan on edges_cluster_idx | 3.5 ms | 5 ms |
| EXPLAIN DELETE from search.resources WHERE uid IN ('locust-2/130b8376-0496-4fa5-8bc2-897d8bbdaf26'); | Resync: Delete resources | Index Scan using resources_pkey on resources | N/A | N/A |
| --- API QUERIES --- | ||||
| EXPLAIN ANALYZE SELECT DISTINCT data->'name' from search.resources; | Autocomplete: Unsorted values for name. | Should not use Disk | 336 ms | 2539 ms |
| EXPLAIN ANALYZE SELECT DISTINCT data->'name' as d from search.resources ORDER BY d ASC; | Autocomplete: Sorted values for name. | Parallel Seq Scan on resources | 735 ms | 3258 ms |
| EXPLAIN ANALYZE SELECT DISTINCT data->'status' from search.resources; | Autocomplete: Unsorted values of 'status'. | Should not use Disk | 1395 ms | |
| EXPLAIN ANALYZE SELECT DISTINCT data->'status' as d from search.resources ORDER BY d ASC; | Autocomplete: Sorted values of 'status' | Parallel Seq Scan on resources | 175 ms | 1160 ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'kind' ? 'Secret'; | Search: by kind, no RBAC. Current syntax. |
Bitmap Index Scan on data_kind_idx | 95 ms | 1800ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->>'kind' = 'Secret'; | Search: by kind, no RBAC. Same as above but faster, should we use this? |
Workers Planned: 6 | 70 ms | 400ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->>'kind' ILIKE 'secret'; | Search: by kind, lowercased | Workers Planned: 6 | 125 ms | 850ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'kind' ?| ARRAY['Secret','Pod']; | Search: by multiple kind values. No RBAC. | Bitmap Index Scan on data_kind_idx | 105 ms | 2000 ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'status' ?| ARRAY['Running','Failed']; | Search: by multiple status values. No RBAC. | Workers Planned: 6 | 42 ms | 234 ms |
| EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'label' @> '{"app":"search"}'; | Search: by label, no RBAC. | Workers Planned: 6 | 50 ms | 300 ms |
TODO: query for restarts > 5 |
Search: numeric condition | |||
| TODO: count queries | Saved searches cards | |||
| TODO: Query used by Application backend | Application | |||
| TODO: Queries with RBAC |