GenericIPAddressField index never used on PostgreSQL
|Reported by:||intgr||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='220.127.116.11') # SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") = '18.104.22.168' Ip.objects.filter(ip__gte='22.214.171.124') # SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") >= '126.96.36.199'
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 '188.8.131.52'
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.
Change History (8)
comment:1 Changed 2 years ago by erikr
- Component changed from Uncategorized to Database layer (models, ORM)
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
- Triage Stage changed from Unreviewed to Accepted
comment:2 follow-up: ↓ 3 Changed 2 years ago by intgr
comment:7 Changed 13 months ago by timgraham
- Summary changed from (Generic)IPAddressField index never used on PostgreSQL, inconsistent behavior to GenericIPAddressField index never used on PostgreSQL
- Version changed from 1.6 to master