I have a Django-Postgres setup with this table -
class User(models.Model):
id = models.CharField(max_length=255, primary_key=True)
Running the migration creates two indexes on the field (which is what Django automatically does as I checked from running sqlmigrate) - one index for pkey and one for varchar_pattern_ops -
\d+ "user";
Column| Type | Modifiers | Storage | Stats target | Description
------+--------------------------+-----------+----------+--------------+-------------
id | character varying(255) | not null | extended | |
Indexes:
"user_pkey" PRIMARY KEY, btree (id)
"user_id_90845346_like" btree (id varchar_pattern_ops)
As I understand it, if I run this query
select * from "user" where id='id1234';
it should use user_pkey. Instead it uses user_id_90845346_like.
explain analyze select * from "user" where id='id1234';
Index Scan using "user_id_90845346_like" on "user" (cost=0.41..8.43 rows=1 width=770) (actual time=0.033..0.0
33 rows=0 loops=1)
Index Cond: ((id)::text = 'id1234'::text)
Planning time: 1.335 ms
Execution time: 0.072 ms
(4 rows)
I also don't see any option to force Postgres to use an index, but what I actually want to know is why an = search doesn't use the primary key. Shouldn't like text% searches use the varchar_pattern_ops index?
The postgres driver is always going to opt for the varchar_pattern_ops index if it exists in cases where the column you're indexing on is a varchar column or some variant thereof. In simple terms, because your column that you're indexing on holds strings, the driver will opt for the index that works best with strings when it's available. If you stored the primary keys as integers instead, the driver would use the btree index.