Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Inconsistent performance with LIKE and bind variable onlong-lived connection - Mailing list pgsql-general

From Steven Grimm
Subject Re: [GENERAL] Inconsistent performance with LIKE and bind variable onlong-lived connection
Date
Msg-id CAOFXwWXx0FHeYdKAK3GsMePmvPng4oQtQa-dJArM1+EQqHnCSQ@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Inconsistent performance with LIKE and bind variableon long-lived connection  (rob stone <floriparob@gmail.com>)
Responses Re: [GENERAL] Inconsistent performance with LIKE and bind variable onlong-lived connection
List pgsql-general
Tree view
The problem doesn't appear to be specific to the JDBC driver. Tried a quick version of this in Python for grins with a database that was already populated by the Java code (sadly, the psycopg2 library doesn't directly support prepared statements):


import psycopg2
import time

conn = psycopg2.connect('dbname=test')
cur = conn.cursor()
cur.execute('PREPARE myplan AS '
            'SELECT col2 FROM test WHERE col1 = $1 AND col2 LIKE $2 ORDER BY col2')
times = []

for i in range(0, 20):
    start_time = time.time()
    cur.execute('EXECUTE myplan (%s, %s)', ('xyz', '%'))
    cur.fetchall()
    end_time = time.time()
    times.append(int((end_time - start_time) * 1000))

print(times)


The output looks similar to the pattern in the Java test code, though it gets slow after 5 iterations rather than 9:

[7, 6, 6, 5, 6, 102, 104, 111, 107, 109, 108, 114, 102, 107, 107, 134, 102, 106, 108, 103]


pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) fortoast value 76753264 in pg_toast_10920100
Next
From: "David G. Johnston"
Date:
Subject: Re: [GENERAL] Inconsistent performance with LIKE and bind variable onlong-lived connection