pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedprocedure takes much more time than its query statement
4+ messages / 4 participants
[nested] [flat]
* procedure takes much more time than its query statement
@ 2011-11-01 14:01 Sabin Coanda <s.coanda@deuromedia.com>
0 siblings, 2 replies; 4+ messages in thread
From: Sabin Coanda @ 2011-11-01 14:01 UTC (permalink / raw)
To: pgsql-performance
Hi there,
I have the function:
CREATE OR REPLACE FUNCTION "Test"( ... )
RETURNS SETOF record AS
$BODY$
BEGIN
RETURN QUERY
SELECT ...;
END;
$BODY$
LANGUAGE 'plpgsql' STABLE
The function call takes about 5 minute to proceed, but using directly its
query statement, after replacing the arguments with the same values, it
takes just 5 seconds !
I repeat the test several times and the duration is the same.
What is wrong ?
Please note Postgresql version is "PostgreSQL 8.3.5, compiled by Visual C++
build 1400". I used ANALYZE, and my query / function returns about 150 rows.
I made the tests in pgAdmin query windows.
TIA,
Sabin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure takes much more time than its query statement
@ 2011-11-02 01:21 Dave Crooke <dcrooke@gmail.com>
parent: Sabin Coanda <s.coanda@deuromedia.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dave Crooke @ 2011-11-02 01:21 UTC (permalink / raw)
To: Sabin Coanda <s.coanda@deuromedia.com>; +Cc: pgsql-performance
The most common reason for this (not specific to PG) is that the function
is getting compiled without the substituted constants, and the query plan
is generic, whereas with specific values it is able to use column
statistics to pick a more efficient one.
On Nov 1, 2011 8:16 PM, "Sabin Coanda" <s.coanda@deuromedia.com> wrote:
> Hi there,
>
> I have the function:
> CREATE OR REPLACE FUNCTION "Test"( ... )
> RETURNS SETOF record AS
> $BODY$
> BEGIN
> RETURN QUERY
> SELECT ...;
> END;
> $BODY$
> LANGUAGE 'plpgsql' STABLE
>
> The function call takes about 5 minute to proceed, but using directly its
> query statement, after replacing the arguments with the same values, it
> takes just 5 seconds !
>
> I repeat the test several times and the duration is the same.
>
> What is wrong ?
>
> Please note Postgresql version is "PostgreSQL 8.3.5, compiled by Visual C++
> build 1400". I used ANALYZE, and my query / function returns about 150
> rows.
> I made the tests in pgAdmin query windows.
>
> TIA,
> Sabin
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure takes much more time than its query statement
@ 2011-11-02 02:21 Craig Ringer <ringerc@ringerc.id.au>
parent: Sabin Coanda <s.coanda@deuromedia.com>
1 sibling, 1 reply; 4+ messages in thread
From: Craig Ringer @ 2011-11-02 02:21 UTC (permalink / raw)
To: Sabin Coanda <s.coanda@deuromedia.com>; +Cc: pgsql-performance
On 11/01/2011 10:01 PM, Sabin Coanda wrote:
> Hi there,
>
> I have the function:
> CREATE OR REPLACE FUNCTION "Test"( ... )
> RETURNS SETOF record AS
> $BODY$
> BEGIN
> RETURN QUERY
> SELECT ...;
> END;
> $BODY$
> LANGUAGE 'plpgsql' STABLE
>
> The function call takes about 5 minute to proceed, but using directly its
> query statement, after replacing the arguments with the same values, it
> takes just 5 seconds !
>
> I repeat the test several times and the duration is the same.
>
> What is wrong ?
>
Is it also slow if, outside PL/PgSQL in a regular psql session, you
PREPARE the query, then EXECUTE it?
If so, you're being bitten by a generic query plan. The server does a
better job when it knows what parameter is used when it's planning the
statement. To work around it, you can use the PL/PgSQL 'EXECUTE ...
USING ...' statement to force a re-plan of the statement for every time
it's run.
--
Craig Ringer
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure takes much more time than its query statement
@ 2011-11-02 08:15 Sabin Coanda <s.coanda@deuromedia.ro>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 4+ messages in thread
From: Sabin Coanda @ 2011-11-02 08:15 UTC (permalink / raw)
To: pgsql-performance
Excelent !
You are right
Thanks a lot
Sabin
"Craig Ringer" <ringerc@ringerc.id.au> wrote in message
news:4EB0A920.1010209@ringerc.id.au...
> On 11/01/2011 10:01 PM, Sabin Coanda wrote:
>> Hi there,
>>
>> I have the function:
>> CREATE OR REPLACE FUNCTION "Test"( ... )
>> RETURNS SETOF record AS
>> $BODY$
>> BEGIN
>> RETURN QUERY
>> SELECT ...;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' STABLE
>>
>> The function call takes about 5 minute to proceed, but using directly its
>> query statement, after replacing the arguments with the same values, it
>> takes just 5 seconds !
>>
>> I repeat the test several times and the duration is the same.
>>
>> What is wrong ?
>>
> Is it also slow if, outside PL/PgSQL in a regular psql session, you
> PREPARE the query, then EXECUTE it?
>
> If so, you're being bitten by a generic query plan. The server does a
> better job when it knows what parameter is used when it's planning the
> statement. To work around it, you can use the PL/PgSQL 'EXECUTE ... USING
> ...' statement to force a re-plan of the statement for every time it's
> run.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-11-02 08:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-01 14:01 procedure takes much more time than its query statement Sabin Coanda <s.coanda@deuromedia.com>
2011-11-02 01:21 ` Dave Crooke <dcrooke@gmail.com>
2011-11-02 02:21 ` Craig Ringer <ringerc@ringerc.id.au>
2011-11-02 08:15 ` Sabin Coanda <s.coanda@deuromedia.ro>
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