Queries against multi-million record tables.

Queries against multi-million record tables.

Post by Michael Miyabara-McCaske » Mon, 29 Jan 2001 06:53:28

Hello all,

I am in the midst of taking a development DB into production, but the
performance has not been very good so far.

The DB is a decision based system, that currently has queries against tables
with up to 20million records (3GB table sizes), and at this point about a
25GB DB in total. {Later down the road up to 60million records and a DB of
up to 150GB is planned).

As I understand it, Oracle has some product called "parallel query" which
splits the table queried into 10 pieces and then does each one across as
many CPUs as possible, then puts it all back together again.

So my question is... based upon the messages I have read here, it does not
appear that PostgreSQL makes use of multiple CPUs, but only hands the next
query off to the next processor based upon operating system rules.

Therefore, what are some good ways to handle such large amounts of
information using PostgreSQL?

Michael Miyabara-McCaskey

Web: http://www.miyabara.com/mykarz/
Mobile: +1 408 504 9014


1. Subselect query for a multi table insert single query

I am using Zope with Postgre with Psycopg. Now I have 4 related tables, and
would like to write a query which will input data from one html form.

So here are my tree sql statements, is there a way to make them into one:

1) INSERT INTO business_name (business_name, business_url)
  values ('<dtml-var business_name>', '<dtml-var business_url>');

2) select last_value from business_name_business_name_seq

3) INSERT INTO business_address (street_name, town, county, postcode,
county_id, business_name_id)
  values (<dtml-sqlvar street_name type="string">, <dtml-sqlvar town
type="string">, <dtml-sqlvar county type="string">, <dtml-sqlvar postcode
type="string">, <dtml-sqlvar county_id type="int">, <dtml-var last>);

This works for zope, where the last SQL Method calls a DTML Method "last"
which basically calls the 2nd SQL statement which returns the last value
from the business name sequence table.

Obvoiusly we come to the race condition realm here which may cause a foreign
key being allocated to the wrong business_address, should two users add a
record but the first user's line slows down for a fraction so that his
second sql method returns the value from the 2nd user -- you see my

What was suggested was to write a subselect query, which would first insert
the business_name, then selects the last_value from
business_name_business_name_seq  and then insert this value into the
business_address table ..... an so on. I would like to do this for all 4

Is this at all possible if so can you provide me with links to example code
or be good enough to reply to me.

many thanks


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

2. InvokeMember on dynamic User Control Possible?

3. How to upload 140 millions of record from SAS To SQL server table(single)

4. Inferno available for testing yet?

5. Slow self-join on a 100 million record table

6. A3000T mounting hardware...

7. Querying against a DataWindow - or - How to do this query?

8. Permanent Deletion of Startup Programs

9. Tables within tables vs. multi-dimensional tables

10. MySQL won't let me update on multi-table query

11. multi-table join, final table is outer join count ...

12. Unable to record scripts using PeopleSoft Web against PeopleSoft v7.57

13. How to upload 140 millions of record from SAS To SQL server t abl e(single)