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

[inglesp]

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

[paultiq]

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()

[inglesp]

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

[inglesp]

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.

[evertlammerts]

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()

[inglesp]

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()

?

[evertlammerts]

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.

[evertlammerts]

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   │
  └─────────────────┴─────────┴──────────┘