[PERFORM] pl/pgsql functions outperforming sql ones?

[Carlo Stonebanks]

Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
stored functions s in straight SQL. Each stored proc was calling the next,
so to get the full effect I had to track down all the pl/pgsql stored
functions and convert them to sql. However, I was surprised to find after
all of the rewrites, the LANGUAGE sql procs caused the queries to run slower
than the LANGUAGE plpgsql.

 

None of the stored functions selected from tables, the operated on and
returned scalar values - it was all assign variables, if/then/else - not
even any looping.

 

For those who need the dirty details, here they are. If you happen to think
this behavior is expected, I needn't bore you - just let me know!

 

Thanks,

 

Carlo

 

This was all triggered during the optimization of a query like this:

 

SELECT myVar

FROM myTable

WHERE myFunc(myVar);

 

Looking at EXPLAIN ANALYSE I saw something like this:

 

Filter: myFunc(myVar)

 

I rewrote the body of myFunc(myVar) something like this:

 

SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar) END

 

When I reran EXPLAIN ANALYZE I got this:

 

Filter: SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar)
END

 

Nice. So, I did the same treatment to myOtherFunc() (converted to straight
sql) but the EXPLAIN ANALYZE didn't change (reasonable, I guess - how deep
would I expect it to go?)

 

All of the procs were IMMUTABLE.

 

I was very surprised to find that the query now ran much slower by a factor
of 4.

 

 

Re: [PERFORM] PostgreSQL Parallel Processing !

[sridhar bamandlapally]

*Hi ALL*
**
*Please have a look into this,*
*this may help us to think on PARALLEL option*
**
*WITHOUT PARALLEL Option*
SQL explain plan for select * from hr.emp ;
Explained.
PLAN
--
| Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
--
|   0 | SELECT STATEMENT  |  |  7444K|   944M| 16077   (4)| 00:03:13 |
|   1 |  TABLE ACCESS FULL| EMP  |  7444K|   944M| 16077   (4)| 00:03:13 |
--


*WITH PARALLEL Option*
SQL explain plan for select /*+parallel(emp,4)*/ * from hr.emp ;
Explained.
PLAN
-
| Id  | Operation| Name | Rows  | Bytes | Cost (%CPU)|
Time |
-
|   0 | SELECT STATEMENT |  |  7444K|   944M|  4442   (3)|
00:00:54 |
|   1 |  PX COORDINATOR  |  |   |   |
|  |
|   2 |   PX SEND QC (RANDOM)| :TQ1 |  7444K|   944M|  4442   (3)|
00:00:54 |
|   3 |PX BLOCK ITERATOR |  |  7444K|   944M|  4442   (3)|
00:00:54 |
|   4 | TABLE ACCESS FULL| EMP  |  7444K|   944M|  4442   (3)|
00:00:54 |
-

In the above plan ( WITH PARALLEL Option )
1. Cost has been nearly reduced to 1/4th
2. CPU has been reduced
3. Time has been nearly reduced to 1/3rd




On Thu, Jan 26, 2012 at 2:24 AM, Claudio Freire klaussfre...@gmail.comwrote:

 On Wed, Jan 25, 2012 at 5:16 PM, Merlin Moncure mmonc...@gmail.com
 wrote:
  On Wed, Jan 25, 2012 at 7:43 AM, Claudio Freire klaussfre...@gmail.com
 wrote:
  I know squat about how to implement this, but I've been considering
  picking the low hanging fruit on that tree and patching up PG to try
  the concept. Many of the items above would require a thread-safe
  execution engine, which may be quite hard to get and have a
  significant performance hit. Some don't, like parallel sort.
 
  This was just discussed on -hackers yesterday -- see thread
  'multithreaded query planner'.  In short, judging by the comments of
  some of the smartest people working on this project, it sounds like
  using threads to attack this is not going to happen, ever.  Note you
  can probably still get parallel execution in other ways, using
  processes, shared memory, etc, so I'd consider researching in that
  direction.

 If you mean this[0] thread, it doesn't show anything conclusive
 against, say, parallel sort or pipelining.

 But I agree, checking the code, it would be really tough to get any
 more than parallel sorting by primitive types with threads.

 Processes, however, show promise.

 [0] http://archives.postgresql.org/pgsql-hackers/2012-01/msg00734.php