Skip to content

Parameterised queries run much more slowly than un-parameterised queries, even when there are no parameters #238

New issue
New issue
Closed
Closed
Parameterised queries run much more slowly than un-parameterised queries, even when there are no parameters#238
Labels
wontfixThis will not be worked on

Description

@inglesp
inglesp
opened on Dec 26, 2025

What happens?

Some parameterised queries run much slower than the equivalent non-parameterised queries, even when parameterised with an empty list.

For the examples in the repro script below, the parameterised queries take 4.6s to run, and the un-parameterised query takes 1.2s.

python version: 3.14.0 (main, Oct 28 2025, 12:13:17) [Clang 20.1.4 ]
duckdb version: v1.4.3
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE ?
params: ['03%']
elapsed:  4.578065395355225
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: []
elapsed:  4.612151384353638
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: None
elapsed:  1.2341203689575195

(With production data and queries, we see parameterised queries take ~40x longer than the equivalent non-parameterised ones.)

EXPLAIN ANALYZE shows the same query plan for each query:

`EXPLAIN ANALYZE` 
python version: 3.14.0 (main, Oct 28 2025, 12:13:17) [Clang 20.1.4 ]
duckdb version: v1.4.3
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE ?
params: ['03%']
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYSE SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE ?
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0632s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           value           │
│                           │
│      10,000,000 rows      │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│         t2_id = id        │              │
│                           │              │
│      10,000,000 rows      │              │
│          (0.03s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Table: t1         ││         Table: t2         │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││      Projections: id      │
│        Projections:       ││                           │
│           t2_id           ││          Filters:         │
│           value           ││  code>='03' AND code<'04' │
│                           ││                           │
│      10,000,000 rows      ││        10,000 rows        │
│          (0.67s)          ││          (0.00s)          │
└───────────────────────────┘└───────────────────────────┘

--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: []
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYSE SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0624s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           value           │
│                           │
│      10,000,000 rows      │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│         t2_id = id        │              │
│                           │              │
│      10,000,000 rows      │              │
│          (0.03s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Table: t1         ││         Table: t2         │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││      Projections: id      │
│        Projections:       ││                           │
│           t2_id           ││          Filters:         │
│           value           ││  code>='03' AND code<'04' │
│                           ││                           │
│      10,000,000 rows      ││        10,000 rows        │
│          (0.65s)          ││          (0.00s)          │
└───────────────────────────┘└───────────────────────────┘

--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: None
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYSE SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.108s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           value           │
│                           │
│      10,000,000 rows      │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│         t2_id = id        │              │
│                           │              │
│      10,000,000 rows      │              │
│          (0.05s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Table: t1         ││         Table: t2         │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││      Projections: id      │
│        Projections:       ││                           │
│           t2_id           ││          Filters:         │
│           value           ││  code>='03' AND code<'04' │
│                           ││                           │
│      10,000,000 rows      ││        10,000 rows        │
│          (1.17s)          ││          (0.00s)          │
└───────────────────────────┘└───────────────────────────┘

To Reproduce

Here's a script that demonstrates the problem. Note that EXPLAIN ANALYSE shows the same plan for each query.

Run with uv run demo.py demo.db.

# /// script
# dependencies = [
#   "duckdb",
# ]
# ///

import sys
import time

import duckdb


T1_ROWS = 1_000_000_000
T2_ROWS = 1_000_000


def print_versions():
    print(f"python version: {sys.version}")
    conn = duckdb.connect(sys.argv[1])
    print("duckdb version:", conn.execute("SELECT version()").fetchall()[0][0])


def setup():
    conn = duckdb.connect(sys.argv[1])
    conn.execute(f"""
        CREATE TABLE IF NOT EXISTS t1 AS
        SELECT i AS value, i % {T2_ROWS} AS t2_id
        FROM range(0, {T1_ROWS}) AS r(i)
    """)
    conn.execute(f"""
        CREATE TABLE IF NOT EXISTS t2 AS
        SELECT i AS id, lpad(CAST(i AS VARCHAR), 6, '0') AS code
        FROM range(0, {T2_ROWS}) AS r(i)
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_t2_code ON t2(code)")


def time_query(sql, params):
    conn = duckdb.connect(sys.argv[1])
    print("-" * 80)
    print("sql:", sql)
    print("params:", params)
    t0 = time.time()
    conn.sql(sql, params=params).fetchall()
    print("elapsed: ", time.time() - t0)

    results = conn.sql(f"EXPLAIN ANALYSE {sql}", params=params).fetchall()
    print(results[0][1])


print_versions()

setup()

time_query(
    "SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE ?",
    params=["03%"],
)
time_query(
    "SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'",
    params=None,
)
time_query(
    "SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'",
    params=[],
)

OS:

Ubuntu

DuckDB Package Version:

1.4.3

Python Version:

3.14

Full Name:

Peter Inglesby

Affiliation:

Bennett Institute of Applied Data Science, Oxford University

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

  • Yes, I have

Did you include all relevant configuration to reproduce the issue?

  • Yes, I have

Activity

  1. inglesp
    added
    needs triage
    on Dec 26, 2025

  2. paultiq commented on Dec 29, 2025

    @paultiq
    paultiq
    on Dec 29, 2025
    Contributor

    Reproduced

    Two notes:

    • EXPLAIN ANALYZE runs faster than the parameterized query, which shouldn't happen AFAIK. It may be returning an incorrect plan.
    • As is often the case, you'll also see differences in the same test cases, if you choose .sql() vs .execute()

  3. inglesp commented on Dec 30, 2025

    @inglesp
    inglesp
    on Dec 30, 2025
    Author

    I've reproduced after building my own version.

    However, the timings are much much slower -- about 500x slower. Could this be an artifact of running a debug build?

    python version: 3.12.3 (main, Nov  6 2025, 13:44:16) [GCC 13.3.0]
duckdb version: v1.5.0-dev5021
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE ?
params: ['03%']
elapsed:  2006.5492804050446
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: []
elapsed:  1997.6098101139069
--------------------------------------------------------------------------------
sql: SELECT t1.value FROM t1 JOIN t2 ON t1.t2_id = t2.id WHERE t2.code LIKE '03%'
params: None
elapsed:  1108.362841129303

  4. inglesp commented on Dec 30, 2025

    @inglesp
    inglesp
    on Dec 30, 2025
    Author

    Looking at the code, it seems like the we take a totally different code path depending on whether there are params or not. If there aren't params, we create a Relation for lazy execution; if there are params, we create a MaterializedRelation.

    duckdb-python/src/duckdb_py/pyconnection.cpp

    Lines 1544 to 1583 in 3f1f615

    // Attempt to create a Relation for lazy execution if possible
    shared_ptr<Relation> relation;
    if (py::none().is(params)) {
    // FIXME: currently we can't create relations with prepared parameters
    {
    D_ASSERT(py::gil_check());
    py::gil_scoped_release gil;
    auto statement_type = last_statement->type;
    switch (statement_type) {
    case StatementType::SELECT_STATEMENT: {
    auto select_statement = unique_ptr_cast<SQLStatement, SelectStatement>(std::move(last_statement));
    relation = connection.RelationFromQuery(std::move(select_statement), alias);
    break;
    }
    default:
    break;
    }
    }
    }
    if (!relation) {
    // Could not create a relation, resort to direct execution
    unique_ptr<QueryResult> res;
    res = PrepareAndExecuteInternal(std::move(last_statement), std::move(params));
    if (!res) {
    return nullptr;
    }
    if (res->properties.return_type != StatementReturnType::QUERY_RESULT) {
    return nullptr;
    }
    if (res->type == QueryResultType::STREAM_RESULT) {
    auto &stream_result = res->Cast<StreamQueryResult>();
    res = stream_result.Materialize();
    }
    auto &materialized_result = res->Cast<MaterializedQueryResult>();
    relation = make_shared_ptr<MaterializedRelation>(connection.context, materialized_result.TakeCollection(),
    res->names, alias);
    }

    In the comment // FIXME: currently we can't create relations with prepared parameters, it's not clear to me how hard this current limitation is.

  5. evertlammerts commented on Feb 11, 2026

    @evertlammerts
    evertlammerts
    on Feb 11, 2026
    Member

    Alright, I managed to spend some time on this.

    Relation.sql(query, params) -> Relation is problematic. There is currently no Relation type that expresses a parameterized query. The way this is currently worked around is by creating a MaterializedRelation. This means the query is immediately planned, executed, materialized and the stored in a ColumnDataCollection. When the relation is fetched, the query re-runs (planned and executed, but this time on the ColumnDataCollection). Afaics, what happens is that every item in the ColumnDataCollection is ToString'ed to build the query. This is where the huge overhead comes from.

    Without parameters a QueryRelation is created, which is lazy. This relation will only run during materialization, and through the regular execution path.

    There are three ways to deal with this:

    1. Add a Relation type in core that knows how to deal with prepared statements.
    2. Remove the ability to add parameters to .sql(...). Given that there is not corresponding relation type in core there is an argument to be made that it should not have supported params to begin with. But now that we have it, removing it is a breaking change that will likely get in the way.
    3. Change the return type to a Relation that wraps the result. This constructor already exists, but a relation that wraps a result cannot be chained (which makes sense if the model is that any relation must be lazy). This is a breaking change as well.

    Options 2 and 3 are breaking changes, and right now I don't think it's worth making them. We're not currently planning to work on option 1.

    In summary: there is currently no performant way to use prepared statements in the relational API. If you want to use them anyway, the better pattern is feeding them in through a replacement scan:

    # use .execute() for the parameterized query
df = conn.execute("SELECT * FROM mytable WHERE x = ?", [42]).df()
# then use a replacement scan to read the data
conn.sql("SELECT * FROM df WHERE ...").order(...).fetchall()

    This still materializes the resultset, of course. That's just a fundamental limitation at this moment.

    Some follow ups:

    • I'll put up a PR to treat empty lists, sets, dicts as None
    • I'll put up a PR to document the behavior of .sql()
  6. evertlammerts
    closed this as completedon Feb 11, 2026
  7. evertlammerts
    marked Prepared statements on DuckDB's Python Client increases execution time almost 10X #300 as a duplicate of this issue on Feb 11, 2026
  8. evertlammerts
    added
    wontfixThis will not be worked on
    and removed
    needs triage
    on Feb 11, 2026

  9. inglesp commented on Feb 11, 2026

    @inglesp
    inglesp
    on Feb 11, 2026
    Author

    Thanks @evertlammerts.

    What's the expected performance of your example:

    # use .execute() for the parameterized query
df = conn.execute("SELECT * FROM mytable WHERE x = ?", [42]).df()
# then use a replacement scan to read the data
conn.sql("SELECT * FROM df WHERE ...").order(...).fetchall()

    ?

  10. evertlammerts commented on Feb 11, 2026

    @evertlammerts
    evertlammerts
    on Feb 11, 2026
    Member

    That's not easy to answer. It depends on the case.

    As a baseline and from what I know now, if we want to use the relational API and a parameterized query then we must materialize the result of the parameterized query before feeding it into a relation that can be chained. That's the fundamental limitation and that's what happens both on the original con.sql(q, params).filter(...)....fetchall() path, and on the alternative I give above.

    For the first part of that, as a rule of thumb, .sql(q, p).fetchall() alone is significantly slower than .execute(q, p).fetchall(). You should assume at least 5X in terms of processing time for small to medium sized in-memory datasets, and up to much bigger numbers on e.g. remote parquet files. The additional memory consumption is also non-trivial, due to the ToString() path.

    After that we have to compare scanning, projecting, filtering, etc on a MaterializedRelation vs e.g. an Arrow table. This is also likely to be faster on the latter, since we can't push down on the MaterializedRelation.

  11. evertlammerts commented on Feb 11, 2026

    @evertlammerts
    evertlammerts
    on Feb 11, 2026
    Member

    Processing time differences:

      Results (DuckDB 1.5.0.dev257, Apple M4 Pro):
  ┌───────────┬─────────┬─────────────┬───────────┬──────────────┬──────────────┬────────────────┐
  │   Rows    │  sql()  │ sql(params) │ execute() │ exec(params) │ sql(p)/sql() │ sql(p)/exec(p) │
  ├───────────┼─────────┼─────────────┼───────────┼──────────────┼──────────────┼────────────────┤
  │       100 │ 0.002 s │     0.003 s │   0.002 s │      0.002 s │         1.6x │           1.7x │
  ├───────────┼─────────┼─────────────┼───────────┼──────────────┼──────────────┼────────────────┤
  │     1,000 │ 0.002 s │     0.006 s │   0.002 s │      0.002 s │         2.8x │           2.9x │
  ├───────────┼─────────┼─────────────┼───────────┼──────────────┼──────────────┼────────────────┤
  │    10,000 │ 0.009 s │     0.039 s │   0.008 s │      0.009 s │         4.5x │           4.6x │
  ├───────────┼─────────┼─────────────┼───────────┼──────────────┼──────────────┼────────────────┤
  │   100,000 │ 0.078 s │     0.381 s │   0.076 s │      0.077 s │         4.9x │           5.0x │
  ├───────────┼─────────┼─────────────┼───────────┼──────────────┼──────────────┼────────────────┤
  │ 1,000,000 │ 0.739 s │     3.799 s │   0.718 s │      0.730 s │         5.1x │           5.2x │
  └───────────┴─────────┴─────────────┴───────────┴──────────────┴──────────────┴────────────────┘

    RSS differences on 1M rows (poor benchmark, might not be super indicative):

      Results (DuckDB 1.5.0.dev257, Apple M4 Pro)
  ┌─────────────────┬─────────┬──────────┐
  │     Method      │  Time   │ Peak RSS │
  ├─────────────────┼─────────┼──────────┤
  │ sql()           │ 0.748 s │ 216 MB   │
  ├─────────────────┼─────────┼──────────┤
  │ sql(params)     │ 3.868 s │ 405 MB   │
  ├─────────────────┼─────────┼──────────┤
  │ execute()       │ 0.738 s │ 199 MB   │
  ├─────────────────┼─────────┼──────────┤
  │ execute(params) │ 0.721 s │ 282 MB   │
  └─────────────────┴─────────┴──────────┘
  12. evertlammerts
    added a commit that references this issue on Feb 11, 2026

    Treat empty params the same as None for .sql (#310)

    b6efa5c
  13. inglesp
    added 7 commits that reference this issue on Mar 17, 2026

    Remove unused parameters parameter

    cedc49c

    Remove unused parameters parameter

    b55e34c

    Remove unused parameters parameter

    adf913f

    Remove unused parameters parameter

    d4fe55d

    Remove unused parameters parameter

    b4d789d

    Remove unused parameters parameter

    f0f950d

    Remove unused parameters parameter

    7a4a5fe
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    wontfixThis will not be worked on

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions