enum and array in preparated statement hammer performance #782
Description
- asyncpg version:
asyncpg==0.23.0(also tested withasyncpg==0.21.0 - PostgreSQL version:
PostgreSQL 13.3 (Ubuntu 13.3-0ubuntu0.21.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1) 10.3.0, 64-bit - Python version:
Python 3.8.10 - Platform: Ubuntu 21.04
- Do you use pgbouncer?: no
- Did you install asyncpg with pip?: yes
- Can the issue be reproduced under both asyncio and
uvloop?: yes, makes no different
I have no idea what's going on here, there's a very weird (and big!) performance impact sometimes when you use an enum and a list/array together as prepared arguments.
I had this on a production system, but here is a minimal reproduction of the problem (debug is just here to test performance, it makes not difference:
import asyncio
import asyncpg
from devtools import debug
async def main():
conn = await asyncpg.connect('postgresql://postgres@localhost/test')
try:
await conn.execute(
"""
drop table if exists users;
drop type if exists user_spam;
create type user_spam as enum ('foo', 'bar');
create table users(
id serial primary key,
spam_enum user_spam default 'foo',
spam_varchar varchar(5) default 'foo',
str_list varchar(31)[]
)
"""
)
with debug.timer('spam_enum,variables,two-fields'):
await conn.execute(
'insert into users (spam_enum, str_list) values ($1, $2) returning id',
'bar', ('foo', 'bar')
)
with debug.timer('spam_enum,variables,only-spam_enum'):
await conn.execute('insert into users (spam_enum) values ($1) returning id', 'bar')
with debug.timer('spam_enum,variables,only-str_list'):
await conn.execute('insert into users (str_list) values ($1) returning id', ('foo', 'bar'))
with debug.timer('spam_varchar,variables,two-fields'):
await conn.execute(
'insert into users (spam_varchar, str_list) values ($1, $2) returning id',
'bar', ('foo', 'bar')
)
with debug.timer('spam_enum,static,two-fields'):
await conn.execute(
"insert into users (spam_enum, str_list) values ('bar', '{foo,bar}'::varchar[]) returning id",
)
with debug.timer('spam_varchar,static,two-fields'):
await conn.execute(
"insert into users (spam_varchar, str_list) values ('bar', '{foo,bar}'::varchar[]) returning id",
)
print('number of users created:', await conn.fetchval('select count(*) from users'))
finally:
await conn.close()
if __name__ == '__main__':
asyncio.run(main())Output:
spam_enum,variables,two-fields: 0.679s elapsed
spam_enum,variables,only-spam_enum: 0.000s elapsed
spam_enum,variables,only-str_list: 0.000s elapsed
spam_varchar,variables,two-fields: 0.000s elapsed
spam_enum,static,two-fields: 0.000s elapsed
spam_varchar,static,two-fields: 0.000s elapsed
number of users created: 6
For now my work around is to use simple varchar fields instead of enums, but I'd love to know why this is happening.