Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I may be asking the wrong question in the title. Here are the facts:

My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site.

We're using Postgres 8.4.6. I started logging slow queries, and discovered this culprit:

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

This query is taking upwards of 32 seconds to run. Here's the query plan provided by EXPLAIN:

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

Because this is a query generated by the Django ORM from a Django QuerySet generated by the Django Admin application, I don't have any control over the query itself. An index seems like the logical solution. I tried creating an index to speed this up, but it hasn't made a difference:

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

What am I doing wrong? How can I speed up this query?

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

That index isn't going to help because of the '%' at the start of your match - a BTREE index can only match prefixes and the wildcard at the start of your query means there is no fixed prefix to look for.

That's why it is doing a table scan and matching every record in turn against the query string.

You probably need to look at using a full text index and the text matching operators rather than doing the substring search with LIKE that you are at the moment. You can find more on full text searching in the documentation:

http://www.postgresql.org/docs/8.4/static/textsearch-intro.html

In fact I notice from that page that LIKE apparently never uses indexes, which seems odd to me as it ought to be able to resolve non-wildcard prefixes using a BTREE index. A few quick tests suggests that the documentation is probably correct however, in which case no amount of indexing is going to help while you are using LIKE to resolve the query.

share|improve this answer
    
That's what I was afraid of. Is there another kind of index that will help? As I said, I'm a bit constrained in my ability to affect the query itself. –  David Eyk Aug 9 '11 at 23:16
    
Also, the leading % is a necessary feature: the customer service reps need it for finding customer accounts, especially when there's a typo in the email address. –  David Eyk Aug 9 '11 at 23:17
    
Well, after a little bit of research on LIKE and full-text indexing, and I'm beginning to see your point. –  David Eyk Aug 9 '11 at 23:36
    
For now, I've found a way to suppress the leading wildcard. It turns out you can use an index with LIKE, if you create the index with an appropriate operator class. Docs are here: postgresql.org/docs/8.4/static/indexes-opclass.html –  David Eyk Aug 10 '11 at 15:24
    
Also, check your db for bloat. If you've got a lot of bloat in that table, it's gonna take a long time to seq scan it. If you've got some down time, just cluster it on the primary key and see if it gets faster. If you want to check for bloat, you can run analyze then run the query here: wiki.postgresql.org/wiki/Show_database_bloat. For more accurate values, see the bottom of that page. –  Scott Marlowe Sep 26 '11 at 20:07
show 1 more comment

There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms.

PostgreSQL 9.1 or later provides new features in the extension pg_trgm that enable index support for LIKE / ILIKE expressions of any form with a GIN or GiST index using the provided operator classes.

Install the extension once per database:

CREATE EXTENSION pg_trgm;

Example GIN index:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

More info and links in this related answer:
How is LIKE implemented?
Overview on pattern matching and appropriate indices:
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

share|improve this answer
    
+1 for mentioning trigrams –  dezso Oct 12 '12 at 8:31
1  
This is actually the correct answer. –  vonPetrushev Jul 13 '13 at 2:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.