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;
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
