Parameterised queries run much more slowly than un-parameterised queries, even when there are no parameters #238
Description
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