Skip to content

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

Closed
@crocodilered

Description

@crocodilered
  • 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())

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions