Message-ID: <52398D5B.5010508@gmail.com>
Date: Wed, 18 Sep 2013 14:24:11 +0300
From: =?UTF-8?B?0JDQu9C10LrRgdCw0L3QtNGAINCR0LXQu9C40L3RgdC60LjQuQ==?=
 <avinfo79@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: function execute on v.9.2 slow down
References: <5208D355.3030301@gmail.com>
 <CA+TgmoYAjrWO2pEcZF9qXwWEA_s-mux8J_X3YsGDgS_NjwEhBQ@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoYAjrWO2pEcZF9qXwWEA_s-mux8J_X3YsGDgS_NjwEhBQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

17.09.2013 02:40, Robert Haas пишет:
> On Mon, Aug 12, 2013 at 8:21 AM, Александр Белинский <avinfo79@gmail.com> wrote:
>> Hi!
>> I can't explain why function is slow down on same data.
>> Postgresql.conf the same, hardware is more powerful.
>> Diffrents is postgresql version
> Hmm.  PostgreSQL 9.2 will sometimes replan queries a number of times
> where older releases, looking to see whether the choice of bind
> variables affects the optimal plan choice, where older versions would
> create a generic plan on first execution and use it forever.  I'm not
> sure whether that behavior applies in this situation, though.  If you
> run it say 15 times does it eventually start running faster?
If i run function 1000 times it eventually have same execution time 
forever in 9.2 and 9.3
But 9.1 version have performance benefit at second run and forever

I made test and found that in 9.2 and 9.3 versions if i use variable in 
query pg replan it forever.

Here is my tests
Postgresql 9.3

EXPLAIN ANALYZE  SELECT DISTINCT s.allocation, s.city, s.operator FROM 
webclient.ti_cache_alloc_price_dbl s
         JOIN ti.ti_offer_price o ON s.city = o.city AND s.operator = 
o.operator
         JOIN ti.ti_offer_allocation2 al ON al.alloc_id = o.alloc_id AND 
s.allocation = al.allocation
         WHERE o.allspo = 21600254
             AND NOT EXISTS(SELECT id FROM ti.ti_offer_price WHERE 
id=s.offer)
             AND o.departure>=current_date+10
             AND o.duration BETWEEN 7 AND 14
             AND o.ticket>0
             AND o.room_size=14;

'Total runtime: 5.371 ms'

But if i use this query inside the fumction i have big performance problem
Why?

CREATE OR REPLACE FUNCTION sql_test(v_allspo integer)
   RETURNS integer AS
$BODY$
BEGIN

     PERFORM DISTINCT s.allocation, s.city, s.operator FROM 
webclient.ti_cache_alloc_price_dbl s
         JOIN ti.ti_offer_price o ON s.city = o.city AND s.operator = 
o.operator
         JOIN ti.ti_offer_allocation2 al ON al.alloc_id = o.alloc_id AND 
s.allocation = al.allocation
         WHERE o.allspo = v_allspo
             AND NOT EXISTS(SELECT id FROM ti.ti_offer_price WHERE 
id=s.offer)
             AND o.departure>=current_date+10
             AND o.duration BETWEEN 7 AND 14
             AND o.ticket>0
             AND o.room_size=14;

    RETURN 1;
END;$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;


EXPLAIN ANALYZE  SELECT sql_test(
     21600254
);

'Result  (cost=0.00..0.26 rows=1 width=0) (actual time=607.557..607.558 
rows=1 loops=1)'
'  Buffers: shared hit=2059'
'Total runtime: 607.570 ms'

And forever .....

In 9.1 same function, same query works well!

First run
EXPLAIN (ANALYZE,BUFFERS) SELECT sql_test(
     21600254
);


'Result  (cost=0.00..0.26 rows=1 width=0) (actual time=486.003..486.004 
rows=1 loops=1)'
'  Buffers: shared hit=5645 read=68 written=4'
'Total runtime: 486.028 ms'

Second run
EXPLAIN (ANALYZE,BUFFERS) SELECT sql_test(
     21600254
);

'Result  (cost=0.00..0.26 rows=1 width=0) (actual time=4.561..4.562 
rows=1 loops=1)'
'  Buffers: shared hit=2852'
'Total runtime: 4.576 ms'


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance