Re: Table Partitioning and Rules

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:josh(at)agliodbs(dot)com]
>Sent: Thursday, July 17, 2003 2:03 PM
>To: Richard Huxton; Girish Bajaj; pgsql-sql(at)postgresql(dot)org
>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.
>
>
>