17.09.2013 02:40, Robert Haas пишет:
On Mon, Aug 12, 2013 at 8:21 AM, Александр Белинский <avinf...@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