From: Александр Белинский <avinfo79@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: function execute on v.9.2 slow down
Date: Wed, 18 Sep 2013 14:24:11 +0300
Message-ID: <52398D5B.5010508@gmail.com> (raw)
In-Reply-To: <CA+TgmoYAjrWO2pEcZF9qXwWEA_s-mux8J_X3YsGDgS_NjwEhBQ@mail.gmail.com>
References: <5208D355.3030301@gmail.com>
<CA+TgmoYAjrWO2pEcZF9qXwWEA_s-mux8J_X3YsGDgS_NjwEhBQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: avinfo79@gmail.com, robertmhaas@gmail.com
Subject: Re: function execute on v.9.2 slow down
In-Reply-To: <52398D5B.5010508@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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