PostgreSQL query inventory

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