#36088: Avoided unnecessary DEFAULT usage on bulk_create.
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: Simon Charette
Charette |
Type: | Status: assigned
Cleanup/optimization |
Component: Database | Version: dev
layer (models, ORM) | Keywords: unnest insert
Severity: Normal | db_default default bulk_create
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When `bulk_create` is used for models composed of fields with `db_default`
the resulting `INSERT` statement use `DEFAULT` placeholders to signify
that a field must use the database defined default.
For example, the following code
{{{#!python
class Author(models):
name = models.CharField(max_length=100)
created_at = models.DateTimeField(db_default=Now())
Author.objects.bulk_create([Author(name="foo"), Author(name="bar")])
}}}
Will result in the following SQL
{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, DEFAULT), (%s, DEFAULT)
}}}
But in cases where no `db_default` is provided for all bulk-created
instances there is no point in specifying `DEFAULT` for each row as that's
what the database will do if the column is not specified at all. In other
words the above SQL is equivalent to
{{{#!sql
INSERT INTO author (name) VALUES (%s), (%s)
}}}
but the latter query simplification provide benefits:
Firstly, it would allow the `UNNEST` optimization introduced in #35936
(a16eedcf9c69d8a11d94cac1811018c5b996d491) to be enabled for models that
define `db_default` fields. Alas since `DEFAULT` is an expression and the
optimization must be disabled in their presence no models making use of
`db_default` can take advantage of it.
In other words, on Postgres, the SQL could be
{{{#!sql
INSERT INTO author (name) SELECT * FROM unnest([%s, %s])
}}}
which has [https://forum.djangoproject.com/t/speeding-up-postgres-bulk-
create-by-using-unnest/36508 demonstrated benefits].
Secondly, pruning the field would avoid having to provide the `db_default`
expression for all model instances on backends that don't support
`DEFAULT` in bulk-inserts such as Oracle. In other words the following SQL
would be avoided
{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, NOW()), (%s, NOW())
}}}
Lastly, it just make the query smaller as no `DEFAULT` has to be provided
for each row for each columns with a defined `db_default` which surely
reduce the parsing time on the backend.
--
Ticket URL: <https://code.djangoproject.com/ticket/36088>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070194592f9263-bf305a29-47f4-46e3-94a9-12a3be4ad864-000000%40eu-central-1.amazonses.com.
