Re: perf problem with huge table

On 2/10/2010 5:13 PM, rama wrote:
> in that way, when i need to do a query for a long ranges (ie: 1 year) i just take the rows that are contained to contab_y
> if i need to got a query for a couple of days, i can go on ymd, if i need to get some data for the other timeframe, i can do some cool intersection between
> the different table using some huge (but fast) queries.
>
>
> Now, the matter is that this design is hard to mantain, and the tables are difficult to check
>
> what i have try is to go for a "normal" approach, using just a table that contains all the data, and some proper indexing.
> The issue is that this table can contains easilly 100M rows :)
> that's why the other guys do all this work to speed-up queryes splitting data on different table and precalculating the sums.
>
>
> I am here to ask for an advice to PGsql experts:
> what do you think i can do to better manage this situation?
> there are some other cases where i can take a look at? maybe some documentation, or some technique that i don't know?
> any advice is really appreciated!
>
Look into table partitioning
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

Its similar to what you are doing but it simplifies queries and logic to
access large data sets.

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.