Re: 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 <haramrae(at)gmail(dot)com>
> 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 was around the Java 5 era), so you might be
> seeing the effects of memory churn in your client instead of in the
> database.
>
> Sadly, no, it doesn't help. I'd be pretty surprised if that was it,
> though, given that it consistently happens with the bind variable and
> never happens without; surely the result set's GC behavior would be
> the same in either case?
>
> For grins, I tried running with the -verbosegc option to log GC
> activity:
>
> Two bind variables      48  16  13   8   6   5   6   7   5 115 110
> 109 132 108 110 113 109 113 108 108
> Equality bind variable   5   6   6   5   6   6   6   6   7   6   8  
> 8   5   6   4   5   5   5   5   5
> [GC (Allocation Failure)  65536K->2293K(251392K), 0.0035075 secs]
> LIKE bind variable       5   5   6   5   6   5   5   6   6 110 107
> 112 116 118 107 112 115 105 104 104
> No bind variables        5   5   4   5   5   4   5   5   4   6   5  
> 6   5   8   4   4   4   4   5   4
>
> So there's only one GC run and it takes about 3 milliseconds. That
> result is the same whether the ResultSet is declared inside or
> outside the loop.
>
> -Steve

I ran your test and here are my results:-

:~/softdev/java_apps$ java BindTest
Two bind
variables     402  15  13  14  13  13  15  14   9  39  46  45  41  39  
41  38  39  38  40  38
Equality bind
variable   9  15   9  10  12  13  10  13  10   9   9   9   7   8   7   
8   8  10   8   7
LIKE bind
variable       9   9   9   8  10   8   8   8   8  40  40  41  37  38  3
9  39  39  42  38  38
No bind
variables       13   9   9   9   9  11  10   8   9   8   9  10   8   9 
  9   8   9   9   8  12
:~/softdev/java_apps$ java BindTest
Two bind
variables      57  17  17  15  15  14  13  14  13  38  47  42  40  39  
39  41  37  39  38  37
Equality bind
variable   8   9   9  10  14  15  10  13  10  10  10   8   8   9  13   
8   9   8   9   8
LIKE bind
variable      10   9  11  10   9   8   9   8   8  39  38  43  43  39  3
9  37  38  38  43  39
No bind
variables        9   9  11   9   9   9   8   9   9   8   9  10   9  15 
 10   9   9   9   9  13

Using Java version 1.8.0_131

postgresql-9.4.1212.jar

PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-
18) 6.3.0 20170516, 64-bit

You'll notice that the first time it ran the database was "cold" and it
took 402, whereas the second time it dropped to 57.

If I have time today I might alter it to used named variables and see
if that makes a difference.

Cheers,
Rob