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 of 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.