Re: [PERFORM] SELECT slows down on sixth execution

2015-10-20 Thread Pavel Stehule
2015-10-20 16:48 GMT+02:00 Jonathan Rogers : > On 10/20/2015 03:45 AM, Pavel Stehule wrote: > > > > > > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer > >: > > > > Jonathan Rogers schrieb am 17.10.2015 um 04:14: > > >>> Yes, I have been looking at both plans and can

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-20 Thread Jonathan Rogers
On 10/20/2015 03:45 AM, Pavel Stehule wrote: > > > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer >: > > Jonathan Rogers schrieb am 17.10.2015 um 04:14: > >>> Yes, I have been looking at both plans and can see where they > diverge. > >>> How could I go abou

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-20 Thread Pavel Stehule
2015-10-20 8:55 GMT+02:00 Thomas Kellerer : > Jonathan Rogers schrieb am 17.10.2015 um 04:14: > >>> Yes, I have been looking at both plans and can see where they diverge. > >>> How could I go about figuring out why Postgres fails to see the large > >>> difference in plan execution time? I use exac

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-19 Thread Thomas Kellerer
Jonathan Rogers schrieb am 17.10.2015 um 04:14: >>> Yes, I have been looking at both plans and can see where they diverge. >>> How could I go about figuring out why Postgres fails to see the large >>> difference in plan execution time? I use exactly the same parameters >>> every time I execute the

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-19 Thread Merlin Moncure
On Fri, Oct 16, 2015 at 9:14 PM, Jonathan Rogers wrote: > On 10/16/2015 08:37 AM, Albe Laurenz wrote: >> Jonathan Rogers wrote: Look at the EXPLAIN ANALYZE output for both the custom plan (one of the first five executions) and the generic plan (the one used from the sixth time on) a

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-17 Thread Pavel Stehule
2015-10-17 15:29 GMT+02:00 Yves Dorfsman : > On 2015-10-14 03:00, Albe Laurenz wrote: > > > > You are encountering "custom plans", introduced in 9.2. > > > > When a statement with parameters is executed, PostgreSQL will not only > generate > > a generic plan, but for the first 5 executions it will

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-17 Thread Yves Dorfsman
On 2015-10-14 03:00, Albe Laurenz wrote: > > You are encountering "custom plans", introduced in 9.2. > > When a statement with parameters is executed, PostgreSQL will not only > generate > a generic plan, but for the first 5 executions it will substitute the > arguments > and generate and execu

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-16 Thread Pavel Stehule
2015-10-17 4:29 GMT+02:00 Jonathan Rogers : > On 10/14/2015 05:01 AM, Pavel Stehule wrote: > > Hi > > > > 2015-10-14 9:38 GMT+02:00 Jonathan Rogers > >: > > > > I have a very complex SELECT for which I use PREPARE and then > EXECUTE. > > The first five time

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-16 Thread Jonathan Rogers
On 10/14/2015 05:01 AM, Pavel Stehule wrote: > Hi > > 2015-10-14 9:38 GMT+02:00 Jonathan Rogers >: > > I have a very complex SELECT for which I use PREPARE and then EXECUTE. > The first five times I run "explain (analyze, buffers) execute ..." in > psq

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-16 Thread Jonathan Rogers
On 10/16/2015 08:37 AM, Albe Laurenz wrote: > Jonathan Rogers wrote: >>> Look at the EXPLAIN ANALYZE output for both the custom plan (one of the >>> first five executions) and the generic plan (the one used from the sixth >>> time on) and see if you can find and fix the cause for the misestimate. >

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-16 Thread Albe Laurenz
Jonathan Rogers wrote: >> Look at the EXPLAIN ANALYZE output for both the custom plan (one of the >> first five executions) and the generic plan (the one used from the sixth >> time on) and see if you can find and fix the cause for the misestimate. > > Yes, I have been looking at both plans and ca

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-14 Thread Jonathan Rogers
On 10/14/2015 05:00 AM, Albe Laurenz wrote: > Jonathan Rogers wrote: >> I have a very complex SELECT for which I use PREPARE and then EXECUTE. >> The first five times I run "explain (analyze, buffers) execute ..." in >> psql, it takes about 1s. Starting with the sixth execution, the plan >> changes

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-14 Thread Pavel Stehule
Hi 2015-10-14 9:38 GMT+02:00 Jonathan Rogers : > I have a very complex SELECT for which I use PREPARE and then EXECUTE. > The first five times I run "explain (analyze, buffers) execute ..." in > psql, it takes about 1s. Starting with the sixth execution, the plan > changes and execution time doub

Re: [PERFORM] SELECT slows down on sixth execution

2015-10-14 Thread Albe Laurenz
Jonathan Rogers wrote: > I have a very complex SELECT for which I use PREPARE and then EXECUTE. > The first five times I run "explain (analyze, buffers) execute ..." in > psql, it takes about 1s. Starting with the sixth execution, the plan > changes and execution time doubles or more. The slower pl

[PERFORM] SELECT slows down on sixth execution

2015-10-14 Thread Jonathan Rogers
I have a very complex SELECT for which I use PREPARE and then EXECUTE. The first five times I run "explain (analyze, buffers) execute ..." in psql, it takes about 1s. Starting with the sixth execution, the plan changes and execution time doubles or more. The slower plan is used from then on. If I D