From: | Александр Белинский <avinfo79(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: function execute on v.9.2 slow down |
Date: | 2013-09-18 11:24:11 |
Message-ID: | 52398D5B.5010508@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
17.09.2013 02:40, Robert Haas пишет:
> On Mon, Aug 12, 2013 at 8:21 AM, Александр Белинский <avinfo79(at)gmail(dot)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'
From | Date | Subject | |
---|---|---|---|
Next Message | fburgess | 2013-09-18 15:42:42 | autovacuum and dead tuples |
Previous Message | Andres Freund | 2013-09-17 23:10:50 | Re: [PERFORM] encouraging index-only scans |