Inconsistent performance with LIKE and bind variable on long-lived connection

From: Steven Grimm <sgrimm(at)thesegovia(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Inconsistent performance with LIKE and bind variable on long-lived connection
Date: 2017-06-10 03:37:09
Message-ID: CAOFXwWWhOYzhxAkQZzfZYPg2r-p_QvAaO31x7RpeBzJiEtEKpg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PostgreSQL 9.6.3 on OS X Sierra, JDBC driver version 42.1.1.

I noticed that one of my queries was slowing down after a few invocations.
Narrowed it down to an issue with bind variables and LIKE conditions. Very
consistently, on a given connection, the first 9 times a SELECT containing
a LIKE whose value is a bind variable is executed, the query returns
results quickly. But starting with the tenth execution, it slows down by a
factor of 20 and stays slow until the connection is closed.

The slowdown doesn't happen with bind variables on equality conditions, and
it doesn't happen if a constant is used in the LIKE condition rather than a
bind variable.

The below Java code reproduces the problem 100% of the time on my system.
It populates a test table with 10000 rows if the table doesn't already
exist, then runs a simple two-condition SELECT with different combinations
of bind variables and constants. Each query is run 20 times and its
execution times in milliseconds are displayed.

On my system I get output like this:

Two bind variables 57 22 8 5 9 9 10 13 8 144 151 236 198
204 197 197 152 126 108 102
Equality bind variable 5 5 5 5 5 5 7 5 4 6 5 4
5 5 4 4 5 5 5 5
LIKE bind variable 5 5 5 5 9 5 5 12 6 111 106 107 108
121 110 101 107 108 113 108
No bind variables 5 5 4 5 5 5 4 4 5 5 4 5
5 4 6 5 4 5 7 4

In other words, when a bind variable is used in the LIKE condition, the
query suddenly goes from taking 5-15 milliseconds to taking 100+
milliseconds. When the query is run in psql, it takes 5-10 milliseconds.

I'm not sure if this is a problem with the JDBC driver or the server, but
it's certainly unexpected!

---

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BindTest {
static String url = "jdbc:postgresql:test";

private static void init() {
try (Connection conn = DriverManager.getConnection(url)) {
try (PreparedStatement stmt = conn.prepareStatement(
"CREATE TABLE test (col1 TEXT, col2 TEXT, PRIMARY KEY (col1,
col2))")) {
stmt.execute();
}

try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO test
VALUES (?,?)")) {
stmt.setString(1, "xyz");

for (int i = 0; i< 10000; i++) {
stmt.setInt(2, i);
stmt.addBatch();
}

stmt.executeBatch();
}

try (PreparedStatement stmt = conn.prepareStatement("VACUUM
ANALYZE")) {
stmt.execute();
}
} catch (SQLException e) {
// Table exists, so don't populate it
}
}

private static void logTime(String name, PreparedStatement stmt) throws
SQLException {
StringBuilder out = new StringBuilder(String.format("%-22s", name));

for (int i = 0; i< 20; i++) {
long startTime = System.currentTimeMillis();
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
rs.getString(1);
}
long endTime = System.currentTimeMillis();
rs.close();

out.append(String.format(" %3d", endTime - startTime));
}

stmt.close();

System.out.println(out);
}

public static void main(String[] args) throws Exception {
init();

try (Connection conn = DriverManager.getConnection(url)) {
PreparedStatement stmt = conn.prepareStatement(
"SELECT col2 FROM test WHERE col1 = ? AND col2 LIKE ? ORDER BY
col2");
stmt.setString(1, "xyz");
stmt.setString(2, "%");
logTime("Two bind variables", stmt);

stmt = conn.prepareStatement(
"SELECT col2 FROM test WHERE col1 = ? AND col2 LIKE '%' ORDER BY
col2");
stmt.setString(1, "xyz");
logTime("Equality bind variable", stmt);

stmt = conn.prepareStatement(
"SELECT col2 FROM test WHERE col1 = 'xyz' AND col2 LIKE ? ORDER
BY col2");
stmt.setString(1, "%");
logTime("LIKE bind variable", stmt);

stmt = conn.prepareStatement(
"SELECT col2 FROM test WHERE col1 = 'xyz' AND col2 LIKE '%' ORDER
BY col2");
logTime("No bind variables", stmt);
}
}
}

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Tanzer 2017-06-10 04:14:15 Re: Limiting DB access by role after initial connection?
Previous Message Bruno Wolff III 2017-06-10 00:38:43 Re: Limiting DB access by role after initial connection?