id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26610	Add a citext field for contrib.postgres	Shadow	nobody	"I was analysing some of our queries - and one of the things I noticed was that if you use iexact, then the database needs to do a full table scan because it is unable to use the index I had on the field.

{{{
class Example(models.Model):
    text = models.CharField(index=True)

Example.objects.get(text=""meow"")  # This will hit the index
Example.objects.get(text__iexact=""meow"")  # This does not.
}}}

I am aware that this kind of index can be added manually by RunSQL migrations, but I think this is probably a common enough scenario that the ORM should attempt to handle it natively.

{{{
# PROPOSED CHANGES - THIS DOES NOT ACTUALLY WORK
class Example(models.Model):
    text = models.CharField(insensitive_index=True)

Example.objects.get(text=""meow"")  # This probably won't hit the index (might depend on the db)
Example.objects.get(text__iexact=""meow"")  # This would.
}}}

What are your thoughts?"	New feature	closed	contrib.postgres	1.11	Release blocker	fixed	index charfield textfield case insensitive optimisation db-indexes	aksheshdoshi@…	Ready for checkin	1	0	0	0	0	0