I am working on a full text search application in django with postgreSQL where the database has about 50k rows currently. I am using SearchRank and TrigramSimilarity and have already implemented SearchVectorField and GIN indexes, but still the search results takes around 5-10 seconds to display on the screen depending on the search complexity.
I am using jquery ajax call to send the search query from UI to django views.py and after performing the search, filter and order operations, I am rendering a different HTML along with result query set using DTL. The execution time taken inside the views.py function is around 0.5 seconds but the total time taken during the ajax call and response is about 5-6 seconds.
Please see my code below:
VIEWS.PY
start_time = time.time()
search_query = SearchQuery(query)
rank = SearchRank('search_vector', search_query, weights=[0.2, 0.4, 0.6, 0.8])
trigram = (TrigramSimilarity('report_name', query_trigram) + TrigramSimilarity('tool', query_trigram) + TrigramSimilarity('owner', query_trigram)
result_set = BI_Report.objects.annotate(rank=rank, trigram=trigram, total=rank+trigram).order_by('-total').filter(Q(trigram__gt=0.2) | Q(rank__gt=0.1))[:50]
time_taken = time.time() - start_time
return render(request, 'display.html', {"result_set": result_set, 'query':query})
time_taken = 0.45 seconds
GIN indexes are present on both SearchVectorField and other database fields and it's showing Bitmap heap scan when running SQL inside postgres. In my code, on using .explain() I could see that SearchRank is doing an index scan on the search vector field but TrigramSimilarity is doing a parallel seq scan.
display HTML
{% for i in result_set %}
<tr>
<td>{{ i.report_name }}</td>
<td>{{ i.tool }}</td>
<td>{{ i.owner }}</td>
</tr>
{% endfor %}
homepage & AJAX
<div id='result'></div>
<script>
var starttime = new Date();
$.ajax({
type: "GET",
url: "display/",
headers: {"key" : query},
success: function(responseData) {
document.getElementById('result').innerHTML = responseData;
total_time = new Date() - starttime;
console.log(total_time)
}
},
});
</script>
total_time = 5800 milli-seconds
I am passing the search query in the request headers to the views.py function and displaying the rendered html with all results using document.getElementById.innerhtml.
I am not sure what is causing the delay - is it the search operation itself or render/ajax call too? I want to display the search results on the screen within 2 seconds. Kindly please suggest in what way I can improve the search operation speed and increase the ajax call-response speed.
I will be grateful for any help. Thank you!
