29

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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
David Eyk
  • 527
  • 1
  • 5
  • 11

2 Answers2

34

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

Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Example GIN index:

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

Related:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
13

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.

TomH
  • 918
  • 8
  • 6
  • 1
    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 09 '11 at 23:17
  • 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: http://www.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: http://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
  • It is true that LIKE cannot use a default btree index at all - if you are not using the standard C locale (most people don't). But the claim that LIKE apparently never uses indexes is just wrong. It can use an index for left-anchored search terms just like you suggested at first. The locale may come in your way. Either use the C locale or an appropriate operator class for the index. Details in this related answer. – Erwin Brandstetter Jul 28 '12 at 18:53