3

I'm doing a full-text search with a GIN index in Postgres (all localhost), and when I write my own query and run it in psql, I am getting great response times and EXPLAIN ANALYZE reports an index hit (woohoo), however when querying via the Django Admin search box with the same search terms, the index is not scanned and the query takes upwards of forever to complete.

My index is created thusly via the fully-awesome pg_trgm Postgres extension:

CREATE INDEX name_gin ON entity USING gin (name gin_trgm_ops);

This query hits the index and takes a blazing 84ms to search through 900k full-text records:

SELECT COUNT(*) 
FROM entity 
WHERE name LIKE UPPER('%dubteeeff%') 
 AND name LIKE UPPER('%django%');

The exact same query, as created by the Django Admin interface, takes 938ms:

SELECT COUNT(*) 
FROM entity 
WHERE UPPER("entity"."name"::text) LIKE UPPER('%dubteeeff%') 
  and UPPER("entity"."name"::text) LIKE UPPER('%django%');`

The only difference appears to be the way the columns are referenced--I'm fairly new with Django and Postgres--is there some Postgres config setting or Django admin QuerySet-something, or a RawQuery that I could override or modify to speed this up? I don't want my Admin page to at best be slow and at worst, drag the rest of the site down with it.

Thanks in advance.

1
  • The difference is that in your query the column is referenced. In the djange Query an expression upper(name) is used, which is not indexed. Araqnid is right that you probably need an index on upper(name) rather than "just" name. Or teach django to not apply the upper function (maybe tell it to not make the query case-insensitive) Commented Jan 11, 2013 at 22:25

2 Answers 2

4

Look like you need to add an index on entity(upper(name)) rather than entity(name) for the case-insensitive search that Django is generating.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks--I can't believe I didn't notice that. Generated the uppercase index and it works like a charm.
0

Is this perhaps an issue with COUNT(*) being slow in Postgres? You might want to check into modifying your queries to return estimates instead of exact counts, as that is a known issue with Postgres.

3 Comments

Thanks but I've already done that-- the COUNT(*) by itself is quite fast. Plus, if that were the issue, why would the query ever be fast?
Ah--yes. Looks like the issue has to do with Postgres not interpreting UPPER("entity"."name"::text) the same way as name. Not sure why that would be, or how to stop Django from doing that. Good luck!
Django will use UPPER() in the SQL if you filter case insensitive by using the i* keywords: .filter(name__istartswith=...), or icontains

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.