On 7/18/07, Benjamin Arai <me@xxxxxxxxxxxxxxxx> 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.
