On Thu, Jul 19, 2012 at 2:19 AM, Eric Floehr <[email protected]> wrote: > I'd like to open up a discussion on the possibilities of having a way to > optionally specifying not to create operator indexes on CharField's when > db_index=True. Based on the consensus from this discussion, I'll open up a > ticket and if it is within my abilities, generate a patch. > > For background, ticket #12234 (https://code.djangoproject.com/ticket/12234) > resulted in the creation of a second index for all CharField's and > TextField's when db_index=True to enable LIKE queries to work as they > should. > > However, there are many use cases where a CharField index is needed but > adding a varchar_pattern_ops index (in PostgreSQL) results in a performance > and storage space hit. In my case, The storage space difference was 550GB > with varchar_pattern_ops indices and 300GB without. I don't have an exact > statistic on the drop in insert speed, but it was noticeable. > > In my case, these varchar fields are of small width, generally 1 to 4 > characters, and indexing is important on the complete field. However, it > will never be the case that LIKE will be used to query for partial matches, > so LIKE query speed isn't an issue, and an operator index is a > performance/storage hit that isn't justified. > > I am working around the problem now with a custom Field class, but it seems > to me that this is a feature that others may benefit from and wanted to > solicit feedback and ideas for if it should be an option, and if so, what > form it should take.
Broadly speaking -- yes, sounds interesting; the trick will be coming up with an API that *isn't* PostgreSQL specific. Off the top of my head, I would look at this problem as the problem of configuring the types of index that are to be created. db_index is currently treated as a boolean yes/no; if you say yes, it creates an index (or, in the case of PostgresSQL, indices); if you say no, it doesn't. To me, what you're talking about is turning a True/False option into something that can be explicitly configured (with some sort of fallback so that "True" is the default index collection). This would need to be functionally driven -- e.g., "I want to add an index that allows for partial matches", or "I want to add a case-insensitive index" -- not exposing literal database syntax or options. On some databases, some of these "functions" would be no-ops, or subsumed by other index functions (so MySQL, for example, will only create one index on CharFields). There is an analogous ticket around dealing with adding composite/multicolumn indices: https://code.djangoproject.com/ticket/5805 I don't know if there's any potential for overlap when it comes to the API here, but it might be worth exploring. Yours, Russ Magee %-) -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
