1

Django search / filter in views.py using icontains and gte, simplified:

def BootstrapFilterView(request):
    qs = Table.objects.all()
    food_contains_query = request.GET.get('food')
    country_contains_query = request.GET.get('country')
    price_min_query = request.GET.get('pmin')

    qs = qs.filter(food__icontains= food_contains_query)
    qs = qs.filter(country__icontains= country_contains_query)
    qs = qs.filter(price__gte=price_min_query)

gte works fine but upon testing icontains is unsuitably inefficient. Indexing doesn't help for icontains and I think iexact might work, but users can't be expected to provide exact matches. I'm bewildered by more advanced options for a task well beyond my understanding and am reluctant to attempt potentially obsolete approaches.

Using Postgres 11.6 with millions of rows, and need multiple optional search criteria. There are thousands of unique food and all are children of one of dozens of country.

Django Postgres has full text search but all search criteria are nouns so don't need lexeme conversion but spelling correction (eg. "burito" → "burrito") and synonym matching (eg. "hamburger" → "cheeseburger") are desirable. Is Postgres full text suitable and can this be used with other search criteria of gte and similar numeric operations?

What's a modern and practical way to improve performance?

for_all_intensive_purposes
  • 906
  • 10
  • 16

2 Answers2

1

Thousands is not that many, and thousands divided by dozens is even less many. You can probably load this all in the frontend and do type-a-head suggestions and typo spotting in the frontend, then deal with only exact string matches in the database. That would make a btree index on (food, price) very efficient. (Presumably country is unneeded as the food implies the country, or does it not work that way?)

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • `food` and `country` were chosen for example simplicity. In retrospect `cuisine` is better. They're child : parent like Paris : France. There are also grandchildren. I don't know B-Trees but don't they need a value for all fields? I'd like the search criteria to be optional. – for_all_intensive_purposes Jun 28 '21 at 13:47
1

Figured it out. For icontains enable pg_tgrm extension and use a GIN index with UPPER():

CREATE INDEX gin_index_name on table_name USING GIN (UPPER(col_name) gin_trgm_ops);

for_all_intensive_purposes
  • 906
  • 10
  • 16