pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction performance vs in-line sql
4+ messages / 3 participants
[nested] [flat]
* function performance vs in-line sql
@ 2007-03-08 16:24 Schwarz, Karl <Karl.Schwarz@ngc.com>
0 siblings, 2 replies; 4+ messages in thread
From: Schwarz, Karl @ 2007-03-08 16:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function performance vs in-line sql
@ 2007-03-08 17:49 Richard Huxton <dev@archonet.com>
parent: Schwarz, Karl <Karl.Schwarz@ngc.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2007-03-08 17:49 UTC (permalink / raw)
To: Schwarz, Karl <Karl.Schwarz@ngc.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function performance vs in-line sql
@ 2007-03-08 17:52 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Schwarz, Karl <Karl.Schwarz@ngc.com>
1 sibling, 0 replies; 4+ messages in thread
From: Heikki Linnakangas @ 2007-03-08 17:52 UTC (permalink / raw)
To: Schwarz, Karl <Karl.Schwarz@ngc.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function performance vs in-line sql
@ 2007-03-08 18:45 Schwarz, Karl <Karl.Schwarz@ngc.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Schwarz, Karl @ 2007-03-08 18:45 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-03-08 18:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-08 16:24 function performance vs in-line sql Schwarz, Karl <Karl.Schwarz@ngc.com>
2007-03-08 17:49 ` Richard Huxton <dev@archonet.com>
2007-03-08 18:45 ` Schwarz, Karl <Karl.Schwarz@ngc.com>
2007-03-08 17:52 ` Heikki Linnakangas <heikki@enterprisedb.com>
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