Fulltext indexes vs pattern_ops indexes

I am using django, and all of my queries are created by django, so i have no handwritten queries...

I have a table of BillRecords, which has a field subscriberno . In my django filters, i use a filtering query like:

BillRecords.objects.filter(subscriberno__icontains='123456')

Since the subscriberno the customer said might be quite shortened version o开发者_JAVA技巧f the real number...

That filter outputs a query like:

SELECT "subscriberno" FROM "BillRecords" WHERE UPPER("subscriberno"::text) LIKE UPPER(E'%123456%');

subscriberno is a char field because some numbers contains alphas and some special chars.

On my database, i have two indexes for that column, created by my colleagues.

"BillRecords_subscriberno" btree (subscriberno)
"BillRecords_fsubscriberno_like" btree (subscriberno varchar_pattern_ops)

I am wondering using two indexes for a such query is logical. Since all of our django filter uses icontains and that supposed to be create queries like i write above.

Postgres analyse of the query is as follows:

Seq Scan on BillRecords  (cost=0.00..159782.40 rows=370 width=15) (actual time=579.637..3705.079 rows=10 loops=1)
Filter: (upper((subscriberno)::text) ~~ '%123456%'::text)
Total runtime: 3705.106 ms
(3 rows)

So, as far as i see, no index is used. Since index usega have costs in data insertion and update, having two indexes with no usage (as far as i can see from this analyse) seemed me not logical.

Is there any channce for django to output different queries for a similar icontanis filter? Or my indexes are totally useless?

---

You cannot use an index on an unanchored like statement.

upper(foo) like 'bar%' -- index on upper(foo)
upper(foo) like '%bar' -- no index
reverse(upper(foo)) like 'rab%' -- index on reverse(upper(foo))
upper(foo) like '%bar%' -- no index

But you might find the trigram contrib of use, if you want to reduce the search window.

---

Contains (substring) queries do not have access to indexes (unless the operator is linked to a full-text module). Starts-with queries on the other hand can benefit from indexes. Indexing overhead is negligible if the cardinality is not too low and inserts typically are not made in large batches but in an OLTP scenario.

Do I read the stats correctly: almost 4 seconds to scan 370 rows?

P.S. You might consider an alternative approach: using a function-based index, perhaps on the last four characters of subscriberno concatenated to, say, the first three characters of the subscribername, and using starts-with or equals instead of LIKE with the search-term bookended with wildcards.

---

A simple way to check if your indexes are used at all is to look at

SELECT * FROM pg_stat_user_indexes;

If all your queries are like the one you show, then they certainly won't be used, because the pattern is not anchored. If you want to address that, you will have to re-engineer your search a bit by using full-text search, trigrams, or something like that.