pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: me@benjaminarai.com
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [PERFORM] Parrallel query execution for UNION ALL Queries
Date: Wed, 18 Jul 2007 11:30:48 -0500
Message-ID: <dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com> (raw)
In-Reply-To: <39160.131.107.65.118.1184775275.squirrel@webmail.benjaminarai.com>
References: <39160.131.107.65.118.1184775275.squirrel@webmail.benjaminarai.com>

On 7/18/07, Benjamin Arai <me@benjaminarai.com> wrote:
> Hi,
>
> If I have a query such as:
>
> SELECT * FROM (SELECT * FROM A) UNION ALL (SELECT * FROM B) WHERE
> blah='food';
>
> Assuming the table A and B both have the same attributes and the data
> between the table is not partitioned in any special way, does Postgresql
> execute WHERE blah="food" on both table simultaiously or what?  If not, is
> there a way to execute the query on both in parrallel then aggregate the
> results?
>
> To give some context, I have a very large amount of new data being loaded
> each week.  Currently I am partitioning the data into a new table every
> month which is working great from a indexing standpoint.  But I want to
> parrallelize searches if possible to reduce the perofrmance loss of having
> multiple tables.

Most of the time, the real issue would be the I/O throughput for such
queries, not the CPU capability.

If you have only one disk for your data storage, you're likely to get
WORSE performance if you have two queries running at once, since the
heads would not be going back and forth from one data set to the
other.

EnterpriseDB, a commercially enhanced version of PostgreSQL can do
query parallelization, but it comes at a cost, and that cost is making
sure you have enough spindles / I/O bandwidth that you won't be
actually slowing your system down.



view thread (7+ messages)  latest in thread

Message-ID: <dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com>
Permalink:  ../dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, me@benjaminarai.com, pgsql-general@postgresql.org
  Subject: Re: [PERFORM] Parrallel query execution for UNION ALL Queries
  In-Reply-To: <dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com>
  References: <39160.131.107.65.118.1184775275.squirrel@webmail.benjaminarai.com> <dcc563d10707180930g6ed397e7m6454134810f91927@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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