Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Slow query with ARRAY in SELECT and in WHERE .. ANY #875

Closed
crocodilered opened this issue Jan 11, 2022 · 2 comments
Closed

Slow query with ARRAY in SELECT and in WHERE .. ANY #875

crocodilered opened this issue Jan 11, 2022 · 2 comments

Comments

@crocodilered
Copy link

  • 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:

  1. The presence of the ARRAY-field in the SELECT section
  2. 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())

@elprans
Copy link
Member

elprans commented Jan 11, 2022

This is due to a bad interaction of Postgres JIT and the type introspection query. Pass {'jit':'off'} in server_settings when connecting or turn it off server-wide.

@crocodilered
Copy link
Author

It works! Thank you!

elprans added a commit that referenced this issue Sep 23, 2023
The misapplication of JIT to asyncpg introspection queries has been a
constant source of user complaints.

Closes: #530
Closes: #1078
Previously: #875, #794, #782, #741, #727 (and probably more).
elprans added a commit that referenced this issue Oct 7, 2023
The misapplication of JIT to asyncpg introspection queries has been a
constant source of user complaints.

Closes: #530
Closes: #1078
Previously: #875, #794, #782, #741, #727 (and probably more).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants