Date: Fri, 24 Mar 2006 13:24:04 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Scaling up PostgreSQL in Multiple CPU / Dual Core
Message-ID: <20060324192404.GA9246@pervasive.com>
References: <65f9ddfb0603222219r1742c2aevda37acd55ca845be@mail.gmail.com>
	<1143131901.17999.67.camel@state.g2switchworks.com>
	<4422D033.2030407@commandprompt.com>
	<1143133364.17999.77.camel@state.g2switchworks.com>
	<87hd5olgyd.fsf@wolfe.cbbrowne.com>
	<20060324101440.GP90527@pervasive.com>
	<60zmjfsnz0.fsf@dba2.int.libertyrms.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <60zmjfsnz0.fsf@dba2.int.libertyrms.com>
User-Agent: Mutt/1.5.11

On Fri, Mar 24, 2006 at 01:21:23PM -0500, Chris Browne wrote:
> > Correct me if I'm wrong, but there's no way to (reasonably) accomplish
> > that without having some dedicated extra processes laying around that
> > you can use to execute the queries, no? In other words, the cost of a
> > fork() during query execution would be too prohibitive...
> 
> Counterexample...
> 
> The sort of scenario we keep musing about is where you split off a
> (thread|process) for each partition of a big table.  There is in fact
> a natural such partitioning, in that tables get split at the 1GB mark,
> by default.
> 
> Consider doing a join against 2 tables that are each 8GB in size
> (e.g. - they consist of 8 data files).  Let's assume that the query
> plan indicates doing seq scans on both.
> 
> You *know* you'll be reading through 16 files, each 1GB in size.
> Spawning a process for each of those files doesn't strike me as
> "prohibitively expensive."

Have you ever tried reading from 2 large files on a disk at the same
time, let alone 16? The results ain't pretty.

What you're suggesting maybe makes sense if the two tables are in
different tablespaces, provided you have some additional means to know
if those two tablespaces are on the same set of spindles. Though even
here the usefulness is somewhat suspect, because CPU is a hell of a lot
faster than disks are, unless you have a whole lot of disks. Of course,
this is exactly the target market for MPP.

Where parallel execution really makes sense is when you're doing things
like sorts or hash operations, because those are relatively
CPU-intensive.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461