function performance vs in-line sql

From: "Schwarz, Karl" <Karl(dot)Schwarz(at)ngc(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: function performance vs in-line sql
Date: 2007-03-08 16:24:16
Message-ID: 9E64800D28DF264DB32B0309B6F222D5188548@xcgny105.northgrum.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-03-08 17:49:41 Re: function performance vs in-line sql
Previous Message Jeff Cole 2007-03-08 15:30:03 Re: