GenericIPAddressField index never used on PostgreSQL
|Reported by:||Marti Raudsepp||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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:
class Ip(models.Model): ip = models.GenericIPAddressField(db_index=True)
The index is created as:
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='184.108.40.206') # SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") = '220.127.116.11' Ip.objects.filter(ip__gte='18.104.22.168') # SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") >= '22.214.171.124'
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 '126.96.36.199'
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
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.
Change History (8)
comment:1 Changed 3 years ago by
|Component:||Uncategorized → Database layer (models, ORM)|
|Triage Stage:||Unreviewed → Accepted|
comment:7 Changed 20 months ago by
|Summary:||(Generic)IPAddressField index never used on PostgreSQL, inconsistent behavior → GenericIPAddressField index never used on PostgreSQL|
|Version:||1.6 → master|