Re: function execute on v.9.2 slow down - Mailing list pgsql-performance

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'