Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sun, Jun 11, 2017 at 1:10 AM, Steven Grimm wrote: > (first five iterations) > > Index Only Scan using test_pkey on test (cost=0.29..476.29 rows= > width=4) (actual time=0.058..2.439 rows=1 loops=1) > Index Cond: (col1 = 'xyz'::text) > Filter: (col2 ~~

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

2017-06-11 18:34 GMT+02:00 Steven Grimm : > On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane wrote: > >> Yeah, I've been watching this thread and trying to figure out how to >> explain that part; I suspected a cause of this form but couldn't >> make that

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane wrote: > Yeah, I've been watching this thread and trying to figure out how to > explain that part; I suspected a cause of this form but couldn't > make that theory match the 9-iterations observation. (I still can't.) > I walked

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

I wrote: > Right. The plancache code is designed to switch to a generic plan if > that doesn't seem to save anything compared to a custom plan that's > built for the specific parameter value(s). Er, -ENOCAFFEINE. That's backwards of course. I think the rest of what I wrote is okay.

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

Steven Grimm writes: > That seems to fit the behavior. Thanks; I wasn't aware of that feature of > prepared statements. I changed the Python code to do EXPLAIN ANALYZE > EXECUTE rather than EXECUTE, and I do indeed see a change in plan after the > fifth iteration: > (first

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sat, Jun 10, 2017 at 11:10 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > https://www.postgresql.org/docs/current/static/sql-prepare.html > > Specifically, the notes section. That seems to fit the behavior. Thanks; I wasn't aware of that feature of prepared statements. I

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Saturday, June 10, 2017, Steven Grimm wrote: > 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 >

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

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 =

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

Hi On Sat, 2017-06-10 at 09:17 -0700, Steven Grimm wrote: > On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys > wrote: > > I notice that you're declaring your ResultSet variable inside the > loop, which means that you create and destroy it frequently. I've > been told that this

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys wrote: > I notice that you're declaring your ResultSet variable inside the loop, which means that you create and destroy it frequently. I've been told that this is a pattern that the GC has trouble keeping up with (although that

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

> On 10 Jun 2017, at 5:37, Steven Grimm wrote: […] I notice that you're declaring your ResultSet variable inside the loop, which means that you create and destroy it frequently. I've been told that this is a pattern that the GC has trouble keeping up with (although

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

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