pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction returning setof performance question
5+ messages / 4 participants
[nested] [flat]
* function returning setof performance question
@ 2003-07-30 02:08 Mark Bronnimann <meb@speakeasy.net>
2003-07-30 02:28 ` Re: function returning setof performance question Rod Taylor <rbt@rbt.ca>
2003-07-30 17:28 ` Re: [SQL] function returning setof performance question Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mark Bronnimann @ 2003-07-30 02:08 UTC (permalink / raw)
To: pgsql-sql
I have a question regarding the performance of a function returning a
set of a view as opposed to just selecting the view with the same
where clause. Please, if this should go to the performance list instead,
let me know. I'm just wondering about this from the sql end of things.
Here's the environment:
I'm working from PHP, calling on the query.
I have a view that joins 12 tables and orders the results.
From PHP, I do a select on that view with a where clause.
I created a function that queries the view with the where clause
included in the function. The function is returning a setof that
view taking one variable for the where clause (there are several
other static wheres in there).
I have found that querying the view with the where clause is
giving me quicker results than if I call the function.
The performance hit is tiny, we're talking less than 1/2 a second,
but when I've done this sort of thing in Oracle I've seen a performance
increase, not a decrease.
Any ideas?
Thanks folks... I'm new to the list.
--
Mark Bronnimann
meb@speakeasy.net
-- Let's organize this thing and take all the fun out of it. --
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function returning setof performance question
2003-07-30 02:08 function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
@ 2003-07-30 02:28 ` Rod Taylor <rbt@rbt.ca>
2003-07-30 02:57 ` Re: function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
1 sibling, 1 reply; 5+ messages in thread
From: Rod Taylor @ 2003-07-30 02:28 UTC (permalink / raw)
To: Mark Bronnimann <meb@speakeasy.net>; +Cc: pgsql-sql
> The performance hit is tiny, we're talking less than 1/2 a second,
> but when I've done this sort of thing in Oracle I've seen a performance
> increase, not a decrease.
Thats just plain strange (never tried on Oracle). Why in the world
would adding the overhead of a function call (with no other changes)
increase performance?
The function has additional overhead in the form of the plpgsql
interpreter. You may find a c function will give close to identical
performance as with the standard view so long as the query is the same.
One thing to keep in mind is that the view can be rearranged to give a
better query overall. The exact work completed for the view may be
different when called from within a different SQL statement. Most
functions -- some SQL language based functions are strange this way --
cannot do this
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function returning setof performance question
2003-07-30 02:08 function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
2003-07-30 02:28 ` Re: function returning setof performance question Rod Taylor <rbt@rbt.ca>
@ 2003-07-30 02:57 ` Mark Bronnimann <meb@speakeasy.net>
2003-07-30 03:51 ` Re: function returning setof performance question Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Bronnimann @ 2003-07-30 02:57 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
Thanks for the reply.
I was hoping to eliminate the parse call on the view because I was doing
the where clause on the view instead of putting the where in the view.
In all, I was hoping to keep a single view called from multiple functions
with different where clauses. Yep... I shoulda known better...
Thanks again!
And Rod Taylor (rbt@rbt.ca) said...:
> > The performance hit is tiny, we're talking less than 1/2 a second,
> > but when I've done this sort of thing in Oracle I've seen a performance
> > increase, not a decrease.
>
> Thats just plain strange (never tried on Oracle). Why in the world
> would adding the overhead of a function call (with no other changes)
> increase performance?
>
> The function has additional overhead in the form of the plpgsql
> interpreter. You may find a c function will give close to identical
> performance as with the standard view so long as the query is the same.
>
>
> One thing to keep in mind is that the view can be rearranged to give a
> better query overall. The exact work completed for the view may be
> different when called from within a different SQL statement. Most
> functions -- some SQL language based functions are strange this way --
> cannot do this
>
--
Mark Bronnimann
meb@speakeasy.net
-- Let's organize this thing and take all the fun out of it. --
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function returning setof performance question
2003-07-30 02:08 function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
2003-07-30 02:28 ` Re: function returning setof performance question Rod Taylor <rbt@rbt.ca>
2003-07-30 02:57 ` Re: function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
@ 2003-07-30 03:51 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2003-07-30 03:51 UTC (permalink / raw)
To: Mark Bronnimann <meb@speakeasy.net>; +Cc: Rod Taylor <rbt@rbt.ca>; pgsql-sql
Mark Bronnimann wrote:
> I was hoping to eliminate the parse call on the view because I was doing
> the where clause on the view instead of putting the where in the view.
> In all, I was hoping to keep a single view called from multiple functions
> with different where clauses. Yep... I shoulda known better...
>
It sounds like you're using a sql function, not a plpgsql function
(although I don't think you said either way). If you write the function
in plpgsql it will get parsed and cached on the first call in a
particular backend session, which *might* give you improved performance
on subsequent calls, if there are any; are you using persistent connections?
Alternatively, it might work to use a prepared query.
Joe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] function returning setof performance question
2003-07-30 02:08 function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
@ 2003-07-30 17:28 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-07-30 17:28 UTC (permalink / raw)
To: Mark Bronnimann <meb@speakeasy.net>; pgsql-sql; +Cc: pgsql-performance@postgresql.org
Mark,
I'm crossing this over to the performance list; it's really appropriate on
both lists. So I'm quoting you in full as well.
> I have a question regarding the performance of a function returning a
> set of a view as opposed to just selecting the view with the same
> where clause. Please, if this should go to the performance list instead,
> let me know. I'm just wondering about this from the sql end of things.
>
> Here's the environment:
>
> I'm working from PHP, calling on the query.
>
> I have a view that joins 12 tables and orders the results.
>
> From PHP, I do a select on that view with a where clause.
>
> I created a function that queries the view with the where clause
> included in the function. The function is returning a setof that
> view taking one variable for the where clause (there are several
> other static wheres in there).
>
> I have found that querying the view with the where clause is
> giving me quicker results than if I call the function.
>
> The performance hit is tiny, we're talking less than 1/2 a second,
> but when I've done this sort of thing in Oracle I've seen a performance
> increase, not a decrease.
>
> Any ideas?
Actually, this is exactly what I'd expect in your situation. The SRF returns
the records in a very inefficient fashion: by materializing the result set
and looping through it to return it to the calling cursor, whereas the View
does set-based operations to grab blocks of data. Also PL/pgSQL as a
language is not nearly as optimized as Oracle's PL/SQL.
It's also possible that PostgreSQL handles criteria-filtered views better than
Oracle does. I wouldn't be surprised.
The only times I can imagine an SRF being faster than a view with a where
clause are:
1) When you're only returning a small part of a complex result set, e.g. 10
rows out of 32,718.
2) When the view is too complex (e.g. UNION with subselects) for the Postgres
planner to "push down" the WHERE criteria into the view execution.
I've been planning on testing the performance of SRFs vs. views myself for
paginated result sets in a web application, but haven't gotten around to it
since I can't get my www clients to upgrade to 7.3 ...
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-30 17:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-30 02:08 function returning setof performance question Mark Bronnimann <meb@speakeasy.net>
2003-07-30 02:28 ` Rod Taylor <rbt@rbt.ca>
2003-07-30 02:57 ` Mark Bronnimann <meb@speakeasy.net>
2003-07-30 03:51 ` Joe Conway <mail@joeconway.com>
2003-07-30 17:28 ` Josh Berkus <josh@agliodbs.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