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
22666	GenericIPAddressField index never used on PostgreSQL	Marti Raudsepp	nobody	"This isn't a problem for us currently, but something I noticed in the queries generated by Django, which I find to be an antifeature: fields that have GenericIPAddressField(db_index=True) are currently incapable of actually using the index in filters on PostgreSQL. The indexing does work on other databases because it's just a char/varchar field.

For example, given model:
{{{#!python
class Ip(models.Model):
    ip = models.GenericIPAddressField(db_index=True)
}}}
The index is created as:
{{{#!sql
CREATE INDEX ""asd_ip_ip"" ON ""asd_ip"" (""ip"");
}}}

And query filter clauses are generated using the `host()` database function. Since the index was created without the function, it cannot be used:
{{{
Ip.objects.filter(ip='1.2.3.4')
# SELECT ... FROM ""asd_ip"" WHERE HOST(""asd_ip"".""ip"") = '1.2.3.4'
Ip.objects.filter(ip__gte='1.2.3.4')
# SELECT ... FROM ""asd_ip"" WHERE HOST(""asd_ip"".""ip"") >= '1.2.3.4'
}}}

Worse, since host() converts the IP address to text, the `__gte` filter stops making much sense, it will consider the IP address '255.0.0.0' to be less than '3.0.0.0'

The index ''can'' be used for natural ordering, but the ordering will be inconsistent with the above `__gte` example and other greater/less than operators.
{{{
Ip.objects.order_by('ip')
# SELECT ... FROM ""asd_ip"" ORDER BY ""asd_ip"".""ip"" ASC
}}}

I understand that this was done to fix #708 -- the ability to use `__contains=` for IP addresses, but I think the cure is worse than the disease. In order to support an operation that doesn't make much sense for IP addresses, the change sacrifices the advantages that PostgreSQL's native `inet` type provides and makes ordering inconsistent with filter comparsisons.

----

I think the ""correct"" way to address this is to revert that fix (a9b4efc82b23383038fed6da6ba97242aece27c1) and implement it the same way how `__contains` works for integers. The `::text` cast is universal and works with all data types in PostgreSQL:
{{{
Ip.objects.filter(pk__contains=123)
# SELECT ... FROM ""asd_ip"" WHERE ""asd_ip"".""id""::text LIKE '%123%'
}}}

But this will break for users who are depending on the current `__gte` etc behavior in PostgreSQL, or expecting it to behave the same way in all databases.

Another approach is to simply use `char`/`varchar` type (like other databases) instead of the PostgreSQL-specific `inet`, so it always uses text-ordering behavior and behaves the same in all databases without any hackery.
"	Bug	new	Database layer (models, ORM)	dev	Normal		db-indexes		Accepted	0	0	0	0	0	0