Normal Description of problem:
The search bar typeahead becomes too slow as the data in Postgres grows.
Version-Release number of selected component (if applicable): 2.7.7+
How reproducible: Always
Steps to Reproduce:
- From the search-indexer project run `make test-scale-ui` to load mocked data for 50 managed clusters with 100k resources each.
- Go to the Search console and type in the search bar.
Actual results:
Typeahead results take over 10 seconds to show.
Expected results:
Typeahead results show quickly.
Additional info:
There's 2 parts to this problem:
- The postgres query is slow. (Covered by this issue)
- The UI can be optimized by caching the results of previous queries. (
ACM-7822)
The sort portion of the SQL query is making it slow. Need to investigate how to optimize.
With sorting it takes 12.5 seconds.
search=> EXPLAIN ANALYZE SELECT DISTINCT data->>'name' as d from search.resources ORDER BY d ASC; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=974247.54..999281.57 rows=5006807 width=32) (actual time=11737.872..12506.831 rows=65529 loops=1) -> Sort (cost=974247.54..986764.55 rows=5006807 width=32) (actual time=11737.870..12233.932 rows=5011516 loops=1) Sort Key: ((data ->> 'name'::text)) Sort Method: external merge Disk: 129856kB -> Seq Scan on resources (cost=0.00..297309.09 rows=5006807 width=32) (actual time=0.035..1206.771 rows=5011516 loops=1) Planning Time: 0.078 ms Execution Time: 12521.156 ms (7 rows)
Without sorting, same query takes 2.5 seconds.
search=> EXPLAIN ANALYZE SELECT DISTINCT data->>'name' from search.resources; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=683772.00..814809.53 rows=5006807 width=32) (actual time=2445.824..2457.169 rows=65529 loops=1) Group Key: (data ->> 'name'::text) Planned Partitions: 8 Batches: 1 Memory Usage: 32785kB -> Seq Scan on resources (cost=0.00..297309.09 rows=5006807 width=32) (actual time=0.040..1246.174 rows=5011516 loops=1) Planning Time: 0.072 ms Execution Time: 2461.785 ms (6 rows)
- clones
-
ACM-7822 Search typeahead too slow (console portion)
-
- Closed
-