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	5Mil  (50 cluster * 100k)	Expected execution plan
--- INDEXER QUERIES ---
EXPLAIN ANALYZE SELECT uid, data FROM search.resources WHERE cluster='locust-2' AND uid!='cluster__locust-2'	Resync: Get existing cluster resources.	38 ms	Bitmap Index Scan on data_cluster_idx
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.	51 ms	Bitmap Index Scan on data_cluster_idx
EXPLAIN ANALYZE SELECT sourceid, edgetype, destid FROM search.edges WHERE edgetype!='interCluster' AND cluster='locust-2';	Resync: Get existing edges	5 ms	Bitmap Index Scan on edges_cluster_idx
EXPLAIN DELETE from search.resources WHERE uid IN ('locust-2/130b8376-0496-4fa5-8bc2-897d8bbdaf26');	Resync: Delete resources	N/A	Index Scan using resources_pkey on resources
--- API QUERIES ---
EXPLAIN ANALYZE SELECT DISTINCT data->'name' from search.resources;	Autocomplete: Unsorted values for name .	2539 ms	Should not use Disk
EXPLAIN ANALYZE SELECT DISTINCT data->'name' as d from search.resources ORDER BY d ASC;	Autocomplete: Sorted values for name.	3258 ms	Parallel Seq Scan on resources
EXPLAIN ANALYZE SELECT DISTINCT data->'status' from search.resources;	Autocomplete: Unsorted values of 'status';	1395 ms	Should not use Disk
EXPLAIN ANALYZE SELECT DISTINCT data->'status' as d from search.resources ORDER BY d ASC;	Autocomplete: Sorted values of 'status'	1160 ms	Parallel Seq Scan on resources
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'kind' ? 'Secret';	Search: by kind, no RBAC. Current syntax.	1800ms	Bitmap Index Scan on data_kind_idx
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->>'kind' = 'Secret';	Search: by kind, no RBAC. Same as above but faster, should we use this?	400ms	Workers Planned: 6
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->>'kind' ILIKE 'secret';	Search: by kind, lowercased	850ms	Workers Planned: 6
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'kind' ?| ARRAY['Secret','Pod'];	Search: by multiple kind values. No RBAC.	2000 ms	Bitmap Index Scan on data_kind_idx
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'status' ?| ARRAY['Running','Failed'];	Search: by multiple status values. No RBAC.	234 ms	Workers Planned: 6
EXPLAIN ANALYZE SELECT * from search.resources WHERE data->'label' @> '{"app":"search"}';	Search: by label, no RBAC.	300ms	Workers Planned: 6
TODO: query for restarts > 5