Skip site navigation (1) Skip section navigation (2)

Re: [SQL] function returning setof performance question

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Mark Bronnimann <meb(at)speakeasy(dot)net>, pgsql-sql(at)postgresql(dot)org
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] function returning setof performance question
Date: 2003-07-30 17:28:50
Message-ID: 200307301028.50759.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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


In response to

pgsql-performance by date

Next:From: Ron JohnsonDate: 2003-07-30 18:32:43
Subject: Re: postgresql.conf
Previous:From: Josh BerkusDate: 2003-07-30 17:19:21
Subject: Re: postgresql.conf

pgsql-sql by date

Next:From: Josh BerkusDate: 2003-07-30 18:08:56
Subject: Re: Problem using Subselect results
Previous:From: George WeaverDate: 2003-07-30 16:45:34
Subject: Re: [SQL] ALTER TABLE ... DROP CONSTRAINT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group