I use mptt in the model to control the tag system (each tag has an optional TreeForeignKey for the parent tag)
When I need to save the tag model, the following query is extremely slow (over 45 seconds)
UPDATE "taxonomy_taxonomy" SET "tree_id" = ("taxonomy_taxonomy"."tree_id" + %s) WHERE "taxonomy_taxonomy"."tree_id" > %s
I submit article content through an automatic tag system that can generate more than 20 tags. Obviously this will not fly :)
I added db_index = False, hoping to change the write time (reading does not seem to be a problem), but the problem persists.
Here is the model in question:
class Taxonomy(MPTTModel): parent = TreeForeignKey('self',blank=True,null=True,related_name='children',verbose_name='Parent', db_index=False) parent_name = models.CharField(max_length=64, blank=True, null=True, editable=False) name = models.CharField(verbose_name='Title', max_length=100, db_index=True) slug = models.SlugField(verbose_name='Slug', blank=True) primary = models.BooleanField( verbose_name='Is Primary', default=False, db_index=True, ) type = models.CharField(max_length=30, db_index=True) created_date = models.DateTimeField(auto_now_add=True, null=True) updated_date = models.DateTimeField(auto_now=True, null=True) publication_date = models.DateTimeField(null=True, blank=True) scheduled_date = models.DateTimeField(null=True, blank=True) workflowstate = models.CharField(max_length=30, default='draft') created_by = models.ForeignKey(User, null=True) paid_content = models.BooleanField(verbose_name='Is Behind the Paywall', default=False, blank=True) publish_now = True show_preview = False temporary = models.BooleanField(default=False) def save(self, *args, **kwargs): if self.slug is None: self.slug = self.name if not self.slug: self.slug = slugify(self.name)[:50] if self.parent: self.parent_name = self.parent.name self.slug = slugify(self.slug) self.workflowstate = "published" super(Taxonomy, self).save(*args, **kwargs) store_to_backend_mongo(self) publish_to_frontend(self)
And the query plan (as reported by New Relic):
1) Update on taxonomy_taxonomy (cost=0.00..133833.19 rows=90515 width=139) 2) -> Seq Scan on taxonomy_taxonomy (cost=0.00..133833.19 rows=90515 width=139) 3) Filter: ?
Finally, a trace from such a query:
Traceback (most recent call last): File "/data/www/nj-cms/venv/lib/python3.4/site-packages/newrelic-2.54.0.41/newrelic/api/web_transaction.py", line 711, in __iter__ File "/data/www/nj-cms/venv/lib/python3.4/site-packages/newrelic-2.54.0.41/newrelic/api/web_transaction.py", line 1087, in __call__ File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/core/handlers/wsgi.py", line 189, in __call__ File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/core/handlers/base.py", line 132, in get_response File "/data/www/nj-cms/venv/lib/python3.4/site-packages/newrelic-2.54.0.41/newrelic/hooks/framework_django.py", line 499, in wrapper File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/contrib/auth/decorators.py", line 22, in _wrapped_view File "./editorial/views.py", line 242, in calculate_queryly File "./editorial/views.py", line 292, in queryly_function File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/models/query.py", line 348, in create File "./taxonomy/models.py", line 179, in save File "./taxonomy/models.py", line 58, in save File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/models.py", line 946, in save File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/models.py", line 702, in insert_at File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/managers.py", line 467, in insert_node File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/managers.py", line 491, in insert_node File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/managers.py", line 726, in _create_tree_space File "/data/www/nj-cms/venv/lib/python3.4/site-packages/mptt/managers.py", line 364, in _mptt_update File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/models/query.py", line 563, in update File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 1062, in execute_sql File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute File "/data/www/nj-cms/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute File "/data/www/nj-cms/venv/lib/python3.4/site-packages/newrelic-2.54.0.41/newrelic/hooks/database_dbapi2.py", line 22, in execute
Any idea how I can make this model save faster?
EDIT for more information: This is in Postgres, with the psycopg2 “ENGINE” engine: 'django.db.backends.postgresql_psycopg2',
SECOND EDIT: As requested, I executed the query using EXPLAIN ANALYZE. The result is as follows:
nj=# EXPLAIN ANALYZE UPDATE "taxonomy_taxonomy" SET "tree_id" = ("taxonomy_taxonomy"."tree_id" + 1) WHERE "taxonomy_taxonomy"."tree_id" > 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Update on taxonomy_taxonomy (cost=0.00..9588.75 rows=24582 width=132) (actual time=258718.550..258718.550 rows=0 loops=1) -> Seq Scan on taxonomy_taxonomy (cost=0.00..9588.75 rows=24582 width=132) (actual time=59.956..8271.209 rows=24582 loops=1) Filter: (tree_id > 1) Rows Removed by Filter: 2 Planning time: 28.763 ms Execution time: 258718.661 ms (6 rows)