Extremely slow performance of insert

[rob0rt]

I have a struct, roughly:

#[derive(Queryable, Identifiable, Insertable, AsChangeset, Debug)]
#[changeset_options(treat_none_as_null = "true")]
#[table_name = "people"]
pub struct Person {
    pub id: Uuid,
    pub hash: String,
    pub first_name: Option<String>,
    pub middle_name: Option<String>,
    pub last_name: Option<String>,
    pub email: Option<String>,
    pub roles: Option<Vec<Role>>,
    pub graduation_year: Option<i32>,
    pub grade_levels: Option<Vec<String>>,
    pub display_name: Option<String>,
    pub picture_url: Option<String>,
    pub phone: Option<String>,
    pub locale: Option<String>,
    pub time_zone: Option<String>,
    pub country_of_birth: Option<String>,
    pub state_of_birth: Option<String>,
    pub city_of_birth: Option<String>,
    pub ethnicity: Option<String>,
    pub races: Option<Vec<Race>>,
    pub birthday: Option<DateTime<Utc>>,
    pub gender: Option<Gender>,
    pub residence_status: Option<String>,
    pub language: Option<String>,
    pub identifiers: EntityIdentifiers,
    pub properties: serde_json::Value,
    pub parent_id: Uuid,
    pub parent_org_id: Uuid,
    pub source_id: Uuid,
    pub destination_id: Uuid,
}

The biggest take-away from this struct is its size, mainly. I am creating anywhere between 5000 and 1m unique structs of this kind, and attempting to insert them:

fn batch_insert(
    conn: &diesel::PgConnection,
    entities: Vec<Person>,
) -> Result<(), diesel::result::Error> {
    diesel::insert_into(entity::people::table)
        .values(entity)
        .execute(conn)?;
    Ok(())
}

This operation takes an absolutely unwieldy amount of time, roughly 100ms for a single entity, or roughly 1.5 minutes per 1k items. From my analysis, almost none of this time is actually spent in postgres: Using a debugger revealed it to be in the generation of the statement, specifically here.

I have tried a variety of things, including adding the QueryID derive macro to the struct, ensuring that the entity being inserted is owned (and inversely explicitly borrowing it), splitting the operation into batches (but the time spent seems to be roughly linear), etc. to no avail. Am I missing something obvious? Is there a way to speed this up? In typescript, doing a similar operation on a similar JSON object using pg-promise takes < 5s for >5000 items, so I'm really surprised it's taking as long as it is in Rust.

This is on both the 2018 and 2021 edition, using diesel 1.4.8.

[weiznich]

This is likely caused by #2071, assuming that one of the not by default supported types is a custom type impl. Its already fixed on the master, so you can try that or you can try to provide a oid known at compile time for your custom type definitions.

[rob0rt]

Binding the OIDs with #[postgres(oid = "[...]", array_oid = "[...]")] did in fact work, and brought the execution time down to basically nothing, so I believe that does confirm your suspicion and if my understanding is correct would be fixed in master. For now I'm going to remain on 1.4.8 and use the static binding of the types (since this is for a production application and using master feels like I'd be flying a bit too close to the sun for my comfort).

I presume this discussion will serve as some level of documentation for this issue, but I will say that it would have been nice to have this called out in some "known issues" page. For now, though, the work around does work and I'm able to progress. Thank you!

[weiznich]

Binding the OIDs with #[postgres(oid = "[...]", array_oid = "[...]")] did in fact work, and brought the execution time down to basically nothing, so I believe that does confirm your suspicion and if my understanding is correct would be fixed in master. 

That means #2071 is the cause here. The underlying issue is that we need to know the type oid to bind values. Those oids are stable for built-in types, but obviously we need some way for lookup the oids for custom types. Diesel 1.4 does this by issuing a query each time the oid is needed. This gives the correct answer, but can get really slow. On master we've implement a caching solution so there is only a single lookup per type and connection. This makes it much faster, but can lead to incorrect results if someone switches the schema at runtime.

To note the obvious thing here: Hard coding the oid for a custom type makes the produced binary only compatible with one specific database instance. Different instances may have different oids for the same type.

but I will say that it would have been nice to have this called out in some "known issues" page.

I would say our issue tracker is a "known issue" page 😉

[rob0rt]

Hard coding the oid for a custom type makes the produced binary only compatible with one specific database instance. Different instances may have different oids for the same type.

Yes, we figured this out quickly :) We had to setup flags for which environment we're targeting to use the correct OIDs. A bit messy, but it totally works.

I would say our issue tracker is a "known issue" page 😉

I don't disagree! To re-position: it too me a very long time (almost two entire days of debugging) to figure out why my query was slow to the extent I did. I searched the github issues a lot during this time and never even came across #2071, since I had no suspicion it was the custom types causing my issues (of note is that I didn't even mention that in my OP; I thought it was the size of my struct that was the issue).

To pose an example of what would have answered my question immediately without as much debugging as I had to do:

• Why are my large insert/ update queries slow?
  • [1.4.* - ...] Custom Postgres types used in insertable/ changeset structs are slow due to repeated round trips to resolve their OIDs. See Extremely slow performance of insert #2961 for more information.