Обсуждение: function performance vs in-line sql

I am having a performance problem with a query implemented within a
server side function. If I use an SQL client(EMS Postgres) and manually
generate the sql query I get about 100 times performance improvement
over using the function.

I've also tried using a prepared statement from my application  and
observed a similar performance improvement over the the function.

The table I am quering against has several hundred thousand records. I
have indexes defined and I've run vacuum several times.

Is there something basic I am missing here with the use of a function. I
am no database expert, but my assumption was that a function would give
me better results than in-line sql.

I've seen a mailing list entry in another list that implied that the
query planner for a function behaves differently than in-line sql.


Thanks
Karl

Schwarz, Karl wrote:
> Is there something basic I am missing here with the use of a function. I
> am no database expert, but my assumption was that a function would give
> me better results than in-line sql.

Not necessarily. Usually it means the planner has less information to go on.

We'll need more information though - table definitions, queries, how is
the function called etc.

--
   Richard Huxton
   Archonet Ltd

Schwarz, Karl wrote:
> I am having a performance problem with a query implemented within a
> server side function. If I use an SQL client(EMS Postgres) and manually
> generate the sql query I get about 100 times performance improvement
> over using the function.
>
> I've also tried using a prepared statement from my application  and
> observed a similar performance improvement over the the function.
>
> The table I am quering against has several hundred thousand records. I
> have indexes defined and I've run vacuum several times.
>
> Is there something basic I am missing here with the use of a function. I
> am no database expert, but my assumption was that a function would give
> me better results than in-line sql.
>
> I've seen a mailing list entry in another list that implied that the
> query planner for a function behaves differently than in-line sql.

For starters, can you show us the function, the manual sql query and the
schema, please?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Thanks. I was not looking for help with the query just wanted to know
that I didn't overlook some basic
configuration setting.

Karl

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 08, 2007 12:50 PM
To: Schwarz, Karl
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] function performance vs in-line sql

Schwarz, Karl wrote:
> Is there something basic I am missing here with the use of a function.

> I am no database expert, but my assumption was that a function would
> give me better results than in-line sql.

Not necessarily. Usually it means the planner has less information to go
on.

We'll need more information though - table definitions, queries, how is
the function called etc.

--
   Richard Huxton
   Archonet Ltd