Repository navigation
Slow query with ARRAY in SELECT and in WHERE .. ANY #875
Description
- asyncpg version: 0.25.0
- PostgreSQL version: PostgreSQL 12.9 (Debian 12.9-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: PostgreSQL is local, I can reproduce the issue - Python version: 3.9
- Platform: Debian 10
- Do you use pgbouncer?: NO
- Did you install asyncpg with pip?: YES
- If you built asyncpg locally, which version of Cython did you use?: -
- Can the issue be reproduced under both asyncio and
uvloop?: YES
Two essentially identical requests are executed at different times and it depends on:
- The presence of the ARRAY-field in the SELECT section
- The presence of the ANY expression in the WHERE section and the parameter of List type
The code below reproduces the described problem by showing:
(venv) root@hq77-01-dev01:/opt/scapsule-back# python t.py
Time 1 is 0:00:00.001613
Time 2 is 0:00:02.937298
Thank you.
import asyncio
from datetime import datetime
import asyncpg
from app.config import DB_URL
import uvloop
async def main():
conn = await asyncpg.connect(DB_URL)
await conn.execute('''
CREATE TABLE IF NOT EXISTS public.records
(
pk bigint NOT NULL,
errors integer[] DEFAULT ARRAY[]::integer[],
CONSTRAINT records_pkey PRIMARY KEY (pk)
) TABLESPACE pg_default;
''')
await conn.execute('''
INSERT INTO records (pk, errors) VALUES
(1, ARRAY[1,2]), (2, ARRAY[1,2]), (3, ARRAY[1,2]), (4, ARRAY[1,2]),
(5, ARRAY[1,2]), (6, ARRAY[1,2]), (7, ARRAY[1,2]), (8, ARRAY[1,2]),
(9, ARRAY[1,2]), (10, ARRAY[1,2]), (11, ARRAY[1,2]), (12, ARRAY[1,2]);
''')
a = datetime.now()
await conn.execute('SELECT pk, errors FROM records WHERE pk=ANY(ARRAY[1,2,3,4]);')
b = datetime.now()
print(f'Time 1 is {b - a}')
await conn.execute('SELECT pk, errors FROM records WHERE pk=ANY($1);', [1,2,3,4])
c = datetime.now()
print(f'Time 2 is {c - b}')
await conn.execute('DROP TABLE records;')
await conn.close()
if __name__ == '__main__':
uvloop.install()
asyncio.run(main())
Activity
elprans commentedon Jan 11, 2022
This is due to a bad interaction of Postgres JIT and the type introspection query. Pass
{'jit':'off'}inserver_settingswhen connecting or turn it off server-wide.crocodilered commentedon Jan 11, 2022
It works! Thank you!
Disable JIT while doing type introspection
Disable JIT while doing type introspection (#1082)