Re: [SQL] Table Partitioning and Rules

[Dmitry Tkach]

But what makes you think, that it is quicker to scan 10 tables with 25 
million rows each than it would be to scan one table with 250 million rows?
It won't... In fact, it will be *longer*.

If you have a way to narrow the number of tables to scan down based on 
the condition, you can have that logic implemented with partial indices, 
as it was suggested earlier in this thread...

Dima

Girish Bajaj wrote:

The problem is that Im worried about sequential scans. This particular table
can have upto 150 cols and 250 million records. Now we have a reporting
requirement that someone could select on ANY col and filter on any col as
well. Meaning someone could so a SELECT on col number 1,2,310,1000 from
contact where col num 75='X' and col num 139 = 'Y'.
I cant possibly index all the cols in the table. So I thought Id best manage
the data by splitting up the table into multiple partitions and eventually
depending on application logic, only scan those tables that are necessary to
scan sequentially instead of the whole big table.
Im getting a little confused here cause eventually I would want to join in
this 250 million gigantic table as well.. and that would be a real big
problem causing loads of sequential scans wouldn't it?
Thanks,
Girish
-----Original Message-----
From: Josh Berkus [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 17, 2003 2:03 PM
To: Richard Huxton; Girish Bajaj; [EMAIL PROTECTED]
Subject: Re: [SQL] Table Partitioning and Rules

Girish,

 

Essentially Im trying to store a persons information in a table in the
database. Since we could have millions of people, with duplicates! Ive
decided we need to partition the table into segments where all people
     

with
 

the LastName starting from A to G will be in one table. H-N will be in
another table and O-Z in the third. Ive created a VIEW that does a UNION
     

on
 

all the tables.
     

This sounds hideously inefficient and a management headache besides.  I
think 
PostgreSQL will accept up to 2 billion rows in any one table, and splitting 
stuff into 3 tables will not improve your performance ... quite the
opposite.

Change your database design.

 



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html