Thread: function execute on v.9.2 slow down

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

Here it;s my tests

Server 1 PSQL 9.1

FIRST RUN
EXPLAIN (ANALYZE, BUFFERS)  SELECT webclient.prc_ti_cache_alloc_dbl_update(
     21325134
);

'Result  (cost=0.00..0.26 rows=1 width=0) (actual
time=1399.586..1399.587 rows=1 loops=1)'
'  Buffers: shared hit=40343 read=621'
'Total runtime: 1399.613 ms'

SECOND RUN  SAME QUERY
'Result  (cost=0.00..0.26 rows=1 width=0) (actual time=42.540..42.541
rows=1 loops=1)'
'  Buffers: shared hit=37069'
'Total runtime: 42.558 ms'

THIRD RUN SAME QUERY
'Result  (cost=0.00..0.26 rows=1 width=0) (actual time=198.893..198.894
rows=1 loops=1)'
'  Buffers: shared hit=37069'
'Total runtime: 198.908 ms'


Server 2 PSQL 9.2

FIRST RUN
EXPLAIN (ANALYZE, BUFFERS)  SELECT webclient.prc_ti_cache_alloc_dbl_update(
     21325134
);

'Result  (cost=0.00..0.26 rows=1 width=0) (actual
time=1328.103..1328.104 rows=1 loops=1)'
'  Buffers: shared hit=43081 read=233 written=36'
'Total runtime: 1328.129 ms'

SECOND RUN SAME QUERY
'Result  (cost=0.00..0.26 rows=1 width=0) (actual
time=1699.711..1699.712 rows=1 loops=1)'
'  Buffers: shared hit=42919'
'Total runtime: 1699.737 ms'

THIRD RUN SAME QUERY
'Result  (cost=0.00..0.26 rows=1 width=0) (actual
time=1907.947..1907.948 rows=1 loops=1)'
'  Buffers: shared hit=42869'
'Total runtime: 1907.965 ms'



Can some one explaine this?
The data and indexes the same.
I have made vacuumdb on both srvers.




Hereis the function BODY

-- Function: webclient.prc_ti_cache_alloc_dbl_update(integer)

-- DROP FUNCTION webclient.prc_ti_cache_alloc_dbl_update(integer);

CREATE OR REPLACE FUNCTION
webclient.prc_ti_cache_alloc_dbl_update(v_allspo integer)
   RETURNS integer AS
$BODY$DECLARE
   spo_list RECORD;
   v_insert_cnt integer;
   v_delete_cnt integer;
   v_counter_sorting integer;

BEGIN

     IF NOT webclient.fn_wc_condition() THEN
         RETURN 1;
     END IF;


     UPDATE webclient.ti_cache_alloc_price_dbl s SET
        offer=q.id, country=q.country, resort=q.resort,
resort_place=q.resort_place, alloccat=q.alloccat, price=q.price,
        real_price=q.real_price, cash_type=q.cash_type, allspo=q.allspo,
duration=q.duration, departure=q.departure,
        "operator"=q."operator"
        FROM (SELECT DISTINCT ON
         (al.allocation, o.city, o.operator)
             o.id,
             al.allocation,
             o.city,
             al.alloccat,
             o.country,
             al.resort,
             al.resort_place,
             o.price,
             o.real_price,
             o.cash_type,
             o.allspo,
             o.duration,
             o.departure,
             o.OPERATOR
         FROM ti.ti_offer_price o
         JOIN ti.ti_offer_allocation2 al ON al.alloc_id = o.alloc_id
         LEFT JOIN ti.vw_ti_stop_allocation sa on sa.alloc_id=o.alloc_id
AND sa.departure=o.departure AND sa.operator=o.operator AND
(sa.room_size=o.room_size OR sa.room_size=0)
         LEFT JOIN ti.vw_ti_stop_flight sf on sf.back=false and
sf.date_flight=o.departure and sf.operator=o.operator and sf.city=o.city
and sf.resort=al.resort and sf.stop=true
         LEFT JOIN ti.vw_ti_stop_flight sfb on sfb.back=true and
sfb.date_flight=o.arrival and sfb.operator=o.operator and
sfb.city=o.city and sfb.resort=al.resort and sfb.stop=true
         WHERE o.allspo<>0 AND o.allspo = v_allspo
             AND o.departure>=current_date+10
             AND o.duration BETWEEN 7 AND 14
             AND o.ticket>0
             AND o.room_size=14
             AND sa.id is null
             AND coalesce(sf.stop,false)=false
             AND coalesce(sfb.stop,false)=false
          ORDER BY al.allocation, o.city, o.operator, o.real_price ASC,
o.departure ASC, o.allspo DESC) q
         WHERE s.allocation = q.allocation
         AND s.city = q.city
         AND s.operator = q.operator
         AND s.real_price < q.real_price;



     v_delete_cnt := 0; --будем использовать для проверки необходимости
обновить counter_sorting

     FOR spo_list IN 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<>0 AND  o.allspo = v_allspo
             AND NOT EXISTS(SELECT id FROM ti.ti_offer_price WHERE
allspo<>0 AND 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
     LOOP
         SELECT counter_sorting INTO v_counter_sorting FROM
webclient.ti_cache_alloc_price_dbl WHERE allocation =
spo_list.allocation AND city = spo_list.city AND "operator" =
spo_list.operator;

         DELETE FROM webclient.ti_cache_alloc_price_dbl WHERE allocation
= spo_list.allocation AND city = spo_list.city AND "operator" =
spo_list.operator;

         v_delete_cnt := v_delete_cnt + 1;

         INSERT INTO webclient.ti_cache_alloc_price_dbl (offer,
allocation, city, alloccat, country, resort, resort_place, price,
real_price, cash_type, allspo, duration, departure, operator,
counter_sorting)
         SELECT --DISTINCT ON (al.allocation, o.city)
             o.id,
             al.allocation,
             o.city,
             al.alloccat,
             o.country,
             al.resort,
             al.resort_place,
             o.price,
             o.real_price,
             o.cash_type,
             o.allspo,
             o.duration,
             o.departure,
             o.OPERATOR,
             v_counter_sorting
         FROM ti.ti_offer_price o
         JOIN ti.ti_offer_allocation2 al ON al.alloc_id = o.alloc_id
         LEFT JOIN ti.vw_ti_stop_allocation sa on sa.alloc_id=o.alloc_id
AND sa.departure=o.departure AND sa.operator=o.operator AND
(sa.room_size=o.room_size OR sa.room_size=0)
         LEFT JOIN ti.vw_ti_stop_flight sf on sf.back=false and
sf.date_flight=o.departure and sf.operator=o.operator and sf.city=o.city
and sf.resort=al.resort and sf.stop=true
         LEFT JOIN ti.vw_ti_stop_flight sfb on sfb.back=true and
sfb.date_flight=o.arrival and sfb.operator=o.operator and
sfb.city=o.city and sfb.resort=al.resort and sfb.stop=true
         WHERE o.allspo<>0 AND  al.allocation = spo_list.allocation AND
o.country = al.country
             AND o.city = spo_list.city
             AND o.operator = spo_list.operator
             AND o.departure>=current_date+10
             AND o.duration BETWEEN 7 AND 14
             AND o.ticket>0
             AND o.room_size=14
             AND sa.id is null
             AND coalesce(sf.stop,false)=false
             AND coalesce(sfb.stop,false)=false
          ORDER BY o.real_price ASC, o.departure ASC, o.allspo DESC
          LIMIT 1;

         GET DIAGNOSTICS v_insert_cnt = ROW_COUNT;

         v_delete_cnt := v_delete_cnt - v_insert_cnt;
     END LOOP;

--
     IF v_delete_cnt > 0 THEN
     --пересчитаем counter_sorting
         SELECT setval('webclient.seq_prc_pregen_ti_cache_alloc_dbl', 1,
false) INTO v_delete_cnt;
         UPDATE webclient.ti_cache_alloc_price_dbl SET counter_sorting =
nextval('webclient.seq_prc_pregen_ti_cache_alloc_dbl');
     END IF;



         INSERT INTO webclient.ti_cache_alloc_price_dbl (offer,
allocation, city, alloccat, country, resort, resort_place, price,
real_price, cash_type, allspo, duration, departure, operator,
counter_sorting)
         SELECT DISTINCT ON
         (al.allocation, o.city)
             o.id,
             al.allocation,
             o.city,
             al.alloccat,
             o.country,
             al.resort,
             al.resort_place,
             o.price,
             o.real_price,
             o.cash_type,
             o.allspo,
             o.duration,
             o.departure,
             o.OPERATOR,
             nextval('webclient.seq_prc_pregen_ti_cache_alloc_dbl')
         FROM ti.ti_offer_price o
         JOIN ti.ti_offer_allocation2 al ON al.alloc_id = o.alloc_id
         LEFT JOIN ti.vw_ti_stop_allocation sa on sa.alloc_id=o.alloc_id
AND sa.departure=o.departure AND sa.operator=o.operator AND
(sa.room_size=o.room_size OR sa.room_size=0)
         LEFT JOIN ti.vw_ti_stop_flight sf on sf.back=false and
sf.date_flight=o.departure and sf.operator=o.operator and sf.city=o.city
and sf.resort=al.resort and sf.stop=true
         LEFT JOIN ti.vw_ti_stop_flight sfb on sfb.back=true and
sfb.date_flight=o.arrival and sfb.operator=o.operator and
sfb.city=o.city and sfb.resort=al.resort and sfb.stop=true
         WHERE o.allspo<>0 AND o.allspo = v_allspo
             AND NOT EXISTS (SELECT 1 FROM
webclient.ti_cache_alloc_price_dbl WHERE allocation = al.allocation AND
city = o.city AND "operator"=o.operator)
             AND o.departure>=current_date+10
             AND o.duration BETWEEN 7 AND 14
             AND o.ticket>0
             AND o.room_size=14
             AND sa.id is null
             AND coalesce(sf.stop,false)=false
             AND coalesce(sfb.stop,false)=false
          ORDER BY al.allocation, o.city, o.real_price ASC, o.departure
ASC, o.allspo DESC;


    RETURN 1;
END;$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
ALTER FUNCTION webclient.prc_ti_cache_alloc_dbl_update(integer)
   OWNER TO tix;
GRANT EXECUTE ON FUNCTION
webclient.prc_ti_cache_alloc_dbl_update(integer) TO tix;
GRANT EXECUTE ON FUNCTION
webclient.prc_ti_cache_alloc_dbl_update(integer) TO public;
GRANT EXECUTE ON FUNCTION
webclient.prc_ti_cache_alloc_dbl_update(integer) TO lst_web;

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?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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'