A customer's mysterious performance problem turned out to have a simple cause: a badly chosen data type for a query parameter. The meaning of a parameter's data type is not universally understood, and getting it wrong can cost you an index scan.
Placeholders and prepared statements
Queries that differ only in constant values can be treated as instances of the same statement:
|
1 |
SELECT val FROM large WHERE id = $1; |
Here $1 is a query parameter, also called a bind variable; the value supplied for it at execution time is the parameter value. Two advantages come with the placeholder approach: the database can parse and optimize the statement once and reuse the plan with different values, and separating query from parameters eliminates SQL injection.
PostgreSQL's frontend/backend protocol offers two ways to send a statement. The simple query protocol sends the complete statement as one string. The extended query protocol splits it up: a “Prepare” message carries the statement with placeholders such as $1 and $2, a “Bind” message carries the parameter values, and an “Execute” message runs the query.
Query parameters can only stand in for constants — not for identifiers such as table, column or schema names — and are supported only in SELECT, INSERT, UPDATE and DELETE. A named prepared statement can be executed repeatedly with different values, and every client API supports this. In JDBC the placeholder is always ?, and the first argument of the set* methods gives the parameter number:
|
1 2 3 4 |
java.sql.PreparedStatement stmt = conn.prepareStatement('SELECT * FROM large WHERE id = ?'); stmt.setLong(1, 42L); java.sql.ResultSet rs = stmt.executeQuery(); |
In SQL, PREPARE creates a prepared statement and EXECUTE runs it:
|
1 2 3 4 |
PREPARE stmt(bigint) AS SELECT val FROM large WHERE id = $1; EXECUTE stmt(42); |
Typed, untyped and converted parameters
Both examples above associate a data type with the parameter: long in Java, bigint in SQL. Specifying a type is optional — if you don't, PostgreSQL infers one. An untyped value is represented by the data type unknown.
PostgreSQL also considers conversions for types other than unknown. There is no equality operator comparing bigint and numeric:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT o.oprname FROM pg_operator AS o JOIN pg_amop AS ao ON o.oid = ao.amopopr JOIN pg_am AS a ON a.oid = ao.amopmethod WHERE /* an equality operator as used in B-tree indexes */ ao.amopstrategy = 3 AND a.amname = 'btree' AND (o.oprleft, o.oprright) = ('bigint'::regtype, 'numeric'::regtype); oprname ═════════ (0 rows) |
Yet the comparison still works, because PostgreSQL implicitly converts the bigint to numeric, the preferred data type for numbers. The full rules are in the documentation and are complicated, since PostgreSQL supports overloading for functions and operators.
Three ways to bind one parameter
Take a simple table and index:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE large ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, val double precision NOT NULL ); INSERT INTO large (val) SELECT random() FROM generate_series(1, 100000); VACUUM (ANALYZE) large; |
A Java program queries it with the JDBC snippet above, setting the parameter three different ways while the auto_explain extension captures the server-side execution plan each time.
integer
The parameter is set with
|
1 |
stmt.setInt(1, 42); |
giving this plan:
|
1 2 3 4 5 |
Query Text: SELECT * FROM large WHERE id = $1 Index Scan using large_pkey on large (...) (actual time=0.013..0.013 rows=1 loops=1) Index Cond: (id = 42) Buffers: shared hit=3 |
This works fine: setInt marks the parameter as integer, and an equality operator exists for comparing integer with bigint.
unknown
Setting the parameter as type java.sql.Types.OTHER with
|
1 |
stmt.setObject(1, 42); |
gives this plan:
|
1 2 3 4 5 |
Query Text: SELECT * FROM large WHERE id = $1 Index Scan using large_pkey on large (...) (actual time=0.005..0.006 rows=1 loops=1) Index Cond: (id = 42) Buffers: shared hit=3 |
Equally good, because a parameter of type unknown is inferred to have the same type as id, namely bigint.
numeric
Setting the parameter with
|
1 |
stmt.setBigDecimal(1, java.math.BigDecimal.valueOf(42)); |
gives this plan:
|
1 2 3 4 5 6 |
Query Text: SELECT * FROM large WHERE id = $1 Seq Scan on large (...) (actual time=0.012..11.123 rows=1 loops=1) Filter: ((id)::numeric = '42'::numeric) Rows Removed by Filter: 99999 Buffers: shared hit=935 |
Surprisingly, PostgreSQL chose a sequential scan, and performance is much worse. The JDBC driver maps java.math.BigDecimal to numeric, so the parameter has data type numeric. Since no equality operator exists for bigint and numeric, PostgreSQL converts both to numeric, as visible in the plan. The primary key index is defined on id, not id::numeric, so it cannot be used.
Choosing the parameter type safely
- Ideally, choose the right data type. Above, that would have meant
setLong, since the JDBC driver mapslongtobigint. - In case of doubt, choose an untyped parameter (
unknown) and let PostgreSQL infer the type.
Sometimes neither strategy works, because the host language has no data type matching the PostgreSQL one and PostgreSQL guesses wrong:
|
1 2 |
SELECT id, name FROM person WHERE birthday <= current_timestamp - $1; |
To supply an interval such as 1 year or 1-0 we would have to send an unknown parameter, since Java has no interval type. But PostgreSQL infers the operator
|
1 |
timestamp with time zone - timestamp with time zone |
rather than
|
1 |
timestamp with time zone - interval |
so we get error messages complaining that 1 year is not a valid timestamp. The fix is an explicit type cast in the statement:
|
1 2 |
SELECT id, name FROM person WHERE birthday <= current_timestamp - CAST ($1 AS interval); |
This adds a third point to the list:
- If automatic type resolution fails, add an explicit type cast to the statement.
The data type of a query parameter being relevant to performance is easy to overlook. Either specify no type and let PostgreSQL do the right thing, or specify the correct type explicitly.
