Asyncpg: Performance issue when fetch big query statement directly from pool

[yenbao1340]

• asyncpg version: 0.18.3
• PostgreSQL version: PostgreSQL 10.12
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: No, I only met issue in production (dev, local was still OK)
• Python version: 3.7
• Platform: Ubuntu 16.04
• 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. I work on Sanic with uvloop

I have a big query statement (about 120 lines and access over 10 tables and using CTE). I created a Connection pool with default value config and using directly fetch method of Pool object. However, It take over 30 seconds to executed (sometimes over 1 minutes). But, when I tried to execute this query by SQLClient IDE and psycopg2, everything was OK (execution time was only some miliseconds). I sure this problem didn't come from the query perfomance

_pool = await asyncpg.create_pool(host=config.ERP_DB_HOST,
                                        port=config.ERP_DB_PORT,
                                        database=config.ERP_DB_NAME,
                                        user=config.ERP_DB_USER,
                                        password=config.ERP_DB_PASSWORD,
                                        max_size=int(config.ERP_DB_MAXCONN))
rows = await _pool.fetch(sql)

[elprans]

Please provide more information: your schema and the query you are running.

[elprans]

Also, the abnormality possibly hints at a bad plan being generated for the cached prepared statement. Try this:

async with pool.acquire() as conn:
    stmt = await conn.prepare(sql)
    rows = await stmt.fetch()

[yenbao1340]

Also, the abnormality possibly hints at a bad plan being generated for the cached prepared statement. Try this:

async with pool.acquire() as conn:
    stmt = await conn.prepare(sql)
    rows = await stmt.fetch()

I tried it, but still not effective :(
This is my query sql_statement.txt
I will draw schema asap.
One more stupid question: I think query planning is server-side. Why connector library can interfere with that process and generated bad plan for this query? :(

[elprans]

Why connector library can interfere with that process and generated bad plan for this query?

Because asyncpg uses prepared statements by default and other drivers usually do not. The use of prepared statements generally improves performance, but in certain cases the chosen generic plan might be very inefficient for a particular set of arguments. This is especially likely if the distribution of data in the tables is extremely uneven.

I tried it, but still not effective :(

Weird. Can you try substituting query arguments with constant values in the text of the query?

[yenbao1340]

I tried to modify my query without CTE and add parameter statement_cache_size=0 when init pool. It's worked.
I think some bad plans was cached from this :-?

[elprans]

I'm surprised that the workaround with an explicit prepared statement didn't work for you. When you do connection.prepare(sql) you avoid the use of the shared statement cache and guarantee that there will be a freshly built plan for your query. statement_cache_size=0 achieves the same result only for all queries.