#36088: Avoid unnecessary DEFAULT usage on bulk_create for models with
db_default
fields
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: unnest insert | Triage Stage:
db_default default bulk_create | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* summary: Avoid unnecessary DEFAULT usage on bulk_create. =>
Avoid unnecessary DEFAULT usage on bulk_create for models with
db_default fields
Old description:
> 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.
New description:
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 and SQLite. 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#comment:2>
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/01070194595ed7ee-9c897be9-b6ce-45bc-a543-402510a1cf12-000000%40eu-central-1.amazonses.com.
