On Monday, September 23, 2013 5:36:56 PM UTC+3, Zev Benjamin wrote:
>
> Hi,
>
> I'd like to be able to specify what kind of index the database should use
> for a particular field. I have a proof of concept branch that works with
> PostgreSQL at https://github.com/zbenjamin/django/compare/index-types,
> but I'd like to solicit opinions on whether there's a better way of doing
> it. The way it works in my branch is that you specify the index type by
> making the Field db_index argument a string containing the name of the
> index type you'd like to use. Specifying db_index=True uses the default
> index type.
>
> Example usage:
>
> class IndexTest(models.Model):
> unindexed = models.IntegerField(db_index=False)
> default_indexed = models.IntegerField(db_index=True)
> btree_indexed = models.IntegerField(db_index="btree")
> hash_indexed = models.IntegerField(db_index="hash")
>
I have been trying to make contrib.gis to use less private APIs. While
doing that it became evident that a better way for creating custom indexes
is needed. Django's contrib.gis needs to override backend's private methods
so that indexes can be created. This approach doesn't work if you need
multiple different custom index types (and overriding private methods
itself is a bad habit). For example, a 3rd party PostgreSQL field (say
trigram field) would need custom indexes to work efficiently, but there is
no easy way to do that currently (unless migrations have added something to
allow this?).
I don't want to add a couple more index types to core and leave it to that.
The solution must allow for 3rd party apps to create any index they need.
One possible solution idea is to add a get_index_definitions(self,
connection) method to Field. By default the Field's
get_index_definitions(self, connection) method will return
[StandardIndex(self)] if any index is needed by the field, else []. For
contrib.gis it would be something like super(GeometryField,
self).get_index_definitions(connection).append(GeometryIndex(self)) (Of
course, only if self.spatial_index == True).
Index class instances have an as_sql(self, qn, connection) method. A core
index definition's as_sql() would need to call back to connection (so that
3rd party backends are supported). For example, StandardIndex.as_sql()
would be this:
return connection.creation.standard_index_sql(self)
A 3rd party trigram field's index could use something like this:
if connection.vendor == 'postgresql':
return 'CREATE INDEX trgm_idx ON %s USING gist (%s gist_trgm_ops)'
% (qn(self.field.model._meta.db_table), qn(self.field.column))
elif hasattr(connection.creation, 'create_trgm_index'):
return connection.creation.create_trgm_index(self) # any custom
backend could implement trigram index.
raise NotSupportedException(("The connection %s doesn't support trgm
indexes")
With virtual fields you could have fields that do not do anything else than
create an index. So:
class MyModel(models.Model):
a = models.IntegerField()
b = models.IntegerField()
my_composite_hash_index = HashIndex(a, b) # HashIndex is a virtual
field that implements get_index_definitions() method, but doesn't do
anything else for the model.
This same concept might be extended to other custom SQL (comments, check
constraints, storage parameters, ...).
I don't know how this idea combines with migrations framework. Anybody have
any idea of how to make this idea migrations friendly?
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/e82f538b-6a8b-4691-ad8f-3912d87599ed%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
