pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedParrallel query execution for UNION ALL Queries
7+ messages / 7 participants
[nested] [flat]
* Parrallel query execution for UNION ALL Queries
@ 2007-07-18 16:14 Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:21 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Jonah H. Harris <jonah.harris@gmail.com>
2007-07-18 16:30 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Benjamin Arai @ 2007-07-18 16:14 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.org
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.
Benjamin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
@ 2007-07-18 16:21 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-07-19 10:14 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Dimitri Fontaine <dfontaine@hi-media.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jonah H. Harris @ 2007-07-18 16:21 UTC (permalink / raw)
To: me@benjaminarai.com; +Cc: pgsql-performance; pgsql-general@postgresql.org
On 7/18/07, Benjamin Arai <me@benjaminarai.com> wrote:
> But I want to parrallelize searches if possible to reduce
> the perofrmance loss of having multiple tables.
PostgreSQL does not support parallel query. Parallel query on top of
PostgreSQL is provided by ExtenDB and PGPool-II.
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:21 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-07-19 10:14 ` Dimitri Fontaine <dfontaine@hi-media.com>
2007-07-19 14:30 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dimitri Fontaine @ 2007-07-19 10:14 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; me@benjaminarai.com; pgsql-performance
Hi,
Le mercredi 18 juillet 2007, Jonah H. Harris a écrit :
> On 7/18/07, Benjamin Arai <me@benjaminarai.com> wrote:
> > But I want to parrallelize searches if possible to reduce
> > the perofrmance loss of having multiple tables.
>
> PostgreSQL does not support parallel query. Parallel query on top of
> PostgreSQL is provided by ExtenDB and PGPool-II.
Seems to me that :
- GreenPlum provides some commercial parallel query engine on top of
PostgreSQL,
- plproxy could be a solution to the given problem.
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy
Hope this helps,
--
dim
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:21 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Jonah H. Harris <jonah.harris@gmail.com>
2007-07-19 10:14 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Dimitri Fontaine <dfontaine@hi-media.com>
@ 2007-07-19 14:30 ` Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 7+ messages in thread
From: Luke Lonergan @ 2007-07-19 14:30 UTC (permalink / raw)
To: Dimitri Fontaine <dfontaine@hi-media.com>; pgsql-general@postgresql.org; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; me@benjaminarai.com; pgsql-performance
Dimitri,
> Seems to me that :
> - GreenPlum provides some commercial parallel query engine on top of
> PostgreSQL,
I certainly think so and so do our customers in production with 100s of
terabytes :-)
> - plproxy could be a solution to the given problem.
> https://developer.skype.com/SkypeGarage/DbProjects/PlProxy
This is solving real world problems at Skype of a different kind than
Greenplum, well worth checking out.
- Luke
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
@ 2007-07-18 16:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-07-18 18:50 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Jim C. Nasby <decibel@decibel.org>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2007-07-18 16:30 UTC (permalink / raw)
To: me@benjaminarai.com; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:30 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-07-18 18:50 ` Jim C. Nasby <decibel@decibel.org>
2007-08-10 03:41 ` Re: Parrallel query execution for UNION ALL Queries llonergan@greenplum.com
0 siblings, 1 reply; 7+ messages in thread
From: Jim C. Nasby @ 2007-07-18 18:50 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: me@benjaminarai.com; pgsql-performance; pgsql-general@postgresql.org
On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote:
> 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.
I think you're thinking ExtendDB. :)
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Parrallel query execution for UNION ALL Queries
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:30 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Scott Marlowe <scott.marlowe@gmail.com>
2007-07-18 18:50 ` Re: [PERFORM] Parrallel query execution for UNION ALL Queries Jim C. Nasby <decibel@decibel.org>
@ 2007-08-10 03:41 ` llonergan@greenplum.com
0 siblings, 0 replies; 7+ messages in thread
From: llonergan@greenplum.com @ 2007-08-10 03:41 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Jul 18, 11:50 am, deci...@decibel.org ("Jim C. Nasby") wrote:
> On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote:
> > 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.
>
> I think you're thinking ExtendDB. :)
Well, now they are one and the same - seems that EnterpriseDB bought
ExtenDB and are calling it GridSQL.
Now that it's a commercial endeavor competing with Greenplum, Netezza
and Teradata I'd be very interested in some real world examples of
ExtenDB/GridSQL.
- Luke
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-08-10 03:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-18 16:14 Parrallel query execution for UNION ALL Queries Benjamin Arai <me@benjaminarai.com>
2007-07-18 16:21 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-07-19 10:14 ` Dimitri Fontaine <dfontaine@hi-media.com>
2007-07-19 14:30 ` Luke Lonergan <LLonergan@greenplum.com>
2007-07-18 16:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-07-18 18:50 ` Jim C. Nasby <decibel@decibel.org>
2007-08-10 03:41 ` llonergan@greenplum.com
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