From: Davor J. <DavorJ@live.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow function in queries SELECT clause.
Date: Sun, 20 Jun 2010 13:53:52 +0200
Message-ID: <hvkvht$2dh1$1@news.hub.org> (raw)
References: <hvj6cg$2dna$1@news.hub.org>
<AANLkTimB8-0KZrRbddqgxnZ5TjdgF2t3fFbu2lvx-2V0@mail.gmail.com>
I didn't consider them to be important as they showed the same, only the execution time was different. Also, they are a bit more complex than the ones put in the previous post. But here they are:
Definitions:
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION appfunctions.fnc_unit_conversion_factor(_tree_id integer, _unit_to_id integer)
RETURNS real AS
$BODY$
DECLARE
BEGIN
RETURN (SELECT unit_conv_factor AS factor
FROM vew_unit_conversions AS c
INNER JOIN tbl_sensors AS s ON (s.unit_id = c.unit_id_from)
INNER JOIN tbl_trees USING (sens_id)
WHERE tree_id = _tree_id AND unit_id_to = _unit_to_id)::real;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
--------------------------
CREATE OR REPLACE FUNCTION appfunctions.fnc_unit_conversion_offset(_tree_id integer, _unit_to_id integer)
RETURNS real AS
$BODY$
DECLARE
BEGIN
RETURN (SELECT unit_conv_offset AS offset
FROM vew_unit_conversions AS c
INNER JOIN tbl_sensors AS s ON (s.unit_id = c.unit_id_from)
INNER JOIN tbl_trees USING (sens_id)
WHERE tree_id = _tree_id AND unit_id_to = _unit_to_id)::real;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
--------------------------
CREATE OR REPLACE FUNCTION appfunctions.fnc_unit_convert(_rawdata real, _tree_id integer, _unit_to_id integer)
RETURNS real AS
$BODY$
DECLARE
BEGIN
RETURN _rawdata
* fnc_unit_conversion_factor(_tree_id, _unit_to_id)
+ fnc_unit_conversion_offset(_tree_id, _unit_to_id);
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
Executions:
-----------------------------------------------------------
EXPLAIN ANALYSE SELECT timestamp,
data_from_tree_id_70 AS "flow_11"
FROM
(SELECT sens_chan_data_timestamp AS timestamp, sens_chan_data_data AS data_from_tree_id_70 FROM tbl_sensor_channel_data WHERE tree_id = 70 AND sens_chan_data_timestamp >= '2008-06-11T00:00:00' AND sens_chan_data_timestamp <= '2008-06-18T00:00:00' ) AS "70"
ORDER BY timestamp;
"Sort (cost=175531.00..175794.64 rows=105456 width=12) (actual time=598.454..638.400 rows=150678 loops=1)"
" Sort Key: tbl_sensor_channel_data.sens_chan_data_timestamp"
" Sort Method: external sort Disk: 3240kB"
" -> Bitmap Heap Scan on tbl_sensor_channel_data (cost=3005.29..166732.66 rows=105456 width=12) (actual time=34.810..371.099 rows=150678 loops=1)"
" Recheck Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on tbl_sensor_channel_data_pkey (cost=0.00..2978.92 rows=105456 width=0) (actual time=28.008..28.008 rows=150678 loops=1)"
" Index Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
"Total runtime: 663.478 ms"
-----------------------------------------------------------
EXPLAIN ANALYSE SELECT timestamp,
fnc_unit_convert(data_from_tree_id_70, 70, 7) AS "flow_11"
FROM
(SELECT sens_chan_data_timestamp AS timestamp, sens_chan_data_data AS data_from_tree_id_70 FROM tbl_sensor_channel_data WHERE tree_id = 70 AND sens_chan_data_timestamp >= '2008-06-11T00:00:00' AND sens_chan_data_timestamp <= '2008-06-18T00:00:00' ) AS "70"
ORDER BY timestamp;
"Sort (cost=201895.00..202158.64 rows=105456 width=12) (actual time=35334.017..35372.977 rows=150678 loops=1)"
" Sort Key: tbl_sensor_channel_data.sens_chan_data_timestamp"
" Sort Method: external sort Disk: 3240kB"
" -> Bitmap Heap Scan on tbl_sensor_channel_data (cost=3005.29..193096.66 rows=105456 width=12) (actual time=60.012..35037.129 rows=150678 loops=1)"
" Recheck Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on tbl_sensor_channel_data_pkey (cost=0.00..2978.92 rows=105456 width=0) (actual time=21.884..21.884 rows=150678 loops=1)"
" Index Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
"Total runtime: 35397.841 ms"
-----------------------------------------------------------
EXPLAIN ANALYSE SELECT timestamp,
data_from_tree_id_70*fnc_unit_conversion_factor(70, 7)+ fnc_unit_conversion_offset(70, 7) AS "flow_11"
FROM
(SELECT sens_chan_data_timestamp AS timestamp, sens_chan_data_data AS data_from_tree_id_70 FROM tbl_sensor_channel_data WHERE tree_id = 70 AND sens_chan_data_timestamp >= '2008-06-11T00:00:00' AND sens_chan_data_timestamp <= '2008-06-18T00:00:00' ) AS "70"
ORDER BY timestamp;
EXPLAIN ANALYSE SELECT timestamp,
"Sort (cost=176058.28..176321.92 rows=105456 width=12) (actual time=630.350..669.843 rows=150678 loops=1)"
" Sort Key: tbl_sensor_channel_data.sens_chan_data_timestamp"
" Sort Method: external sort Disk: 3240kB"
" -> Bitmap Heap Scan on tbl_sensor_channel_data (cost=3005.29..167259.94 rows=105456 width=12) (actual time=35.498..399.726 rows=150678 loops=1)"
" Recheck Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on tbl_sensor_channel_data_pkey (cost=0.00..2978.92 rows=105456 width=0) (actual time=27.433..27.433 rows=150678 loops=1)"
" Index Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= '2008-06-11 00:00:00'::timestamp without time zone) AND (sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time zone))"
"Total runtime: 694.968 ms"
"Szymon Guz" <mabewlun@gmail.com> wrote in message news:AANLkTimB8-0KZrRbddqgxnZ5TjdgF2t3fFbu2lvx-2V0@mail.gmail.com...
2010/6/19 Davor J. <DavorJ@live.com>
I think I have read what is to be read about queries being prepared in
plpgsql functions, but I still can not explain the following, so I thought
to post it here:
Suppose 2 functions: factor(int,int) and offset(int, int).
Suppose a third function: convert(float,int,int) which simply returns
$1*factor($2,$3)+offset($2,$3)
All three functions are IMMUTABLE.
Very simple, right? Now I have very fast AND very slow executing queries on
some 150k records:
VERY FAST (half a second):
----------------
SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
VERY SLOW (a minute):
----------------
SELECT convert(data, 1, 2) FROM tbl_data;
The slowness cannot be due to calling a function 150k times. If I define
convert2(float,int,int) to return a constant value, then it executes in
about a second. (still half as slow as the VERY FAST query).
I assume that factor and offset are cached in the VERY FAST query, and not
in the slow one? If so, why not and how can I "force" it? Currently I need
only one function for conversions.
Regards,
Davor
Hi,
show us the code of those two functions and explain analyze of those queries.
regards
Szymon Guz
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: DavorJ@live.com
Subject: Re: Slow function in queries SELECT clause.
In-Reply-To: <hvkvht$2dh1$1@news.hub.org>
* 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