pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction execute on v.9.2 slow down
3+ messages / 2 participants
[nested] [flat]
* function execute on v.9.2 slow down
@ 2013-08-12 12:21 Александр Белинский <avinfo79@gmail.com>
2013-09-16 23:40 ` Re: function execute on v.9.2 slow down Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Александр Белинский @ 2013-08-12 12:21 UTC (permalink / raw)
To: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: function execute on v.9.2 slow down
2013-08-12 12:21 function execute on v.9.2 slow down Александр Белинский <avinfo79@gmail.com>
@ 2013-09-16 23:40 ` Robert Haas <robertmhaas@gmail.com>
2013-09-18 11:24 ` Re: function execute on v.9.2 slow down Александр Белинский <avinfo79@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Haas @ 2013-09-16 23:40 UTC (permalink / raw)
To: Александр Белинский <avinfo79@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: function execute on v.9.2 slow down
2013-08-12 12:21 function execute on v.9.2 slow down Александр Белинский <avinfo79@gmail.com>
2013-09-16 23:40 ` Re: function execute on v.9.2 slow down Robert Haas <robertmhaas@gmail.com>
@ 2013-09-18 11:24 ` Александр Белинский <avinfo79@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Александр Белинский @ 2013-09-18 11:24 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: 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'
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-09-18 11:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-12 12:21 function execute on v.9.2 slow down Александр Белинский <avinfo79@gmail.com>
2013-09-16 23:40 ` Robert Haas <robertmhaas@gmail.com>
2013-09-18 11:24 ` Александр Белинский <avinfo79@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox