Postgres Pro
Downloads
Home   >   mailing lists

Re: PostgreSQL Parallel Processing ! - Mailing list pgsql-performance

From sridhar bamandlapally
Subject Re: PostgreSQL Parallel Processing !
Date
Msg-id CAGuFTBWibLVRrS-yQZCvuZ_62T=DOustwtK1VaQhcQMdn1axWw@mail.gmail.com
Whole thread Raw
In response to Re: PostgreSQL Parallel Processing !  (Claudio Freire <klaussfreire@gmail.com>)
Responses Re: PostgreSQL Parallel Processing !  (Marti Raudsepp <marti@juffo.org>)
Re: PostgreSQL Parallel Processing !  (Thomas Kellerer <spam_eater@gmx.net>)
List pgsql-performance
Tree view
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)| :TQ10000 |  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 <klaussfreire@gmail.com> wrote:
On Wed, Jan 25, 2012 at 5:16 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Jan 25, 2012 at 7:43 AM, Claudio Freire <klaussfreire@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

pgsql-performance by date:

Previous
From: "Carlo Stonebanks"
Date:
Subject: pl/pgsql functions outperforming sql ones?
Next
From: Marti Raudsepp
Date:
Subject: Re: PostgreSQL Parallel Processing !