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

Reply via email to