Re: [HACKERS] Serious performance problem

>For example I can assure in my application that the index, once created
>will be valid, because I just want to read in a new set of data once
>a day (from the MS-SQL Server which collects data over the day).  So
>I could recreate all indices after the import and the database is
>readonly until the next cron job.  Is there any chance to speed up
>those applications?

Hello Andreas,

Is your database read-only? Good point, sorry to insist your problem is 
software optimization. In your case, the database may climb up to 200 
million rows (1000 days x 200.000 rows). What are you going to do then? Buy 
a 16 Itanium computer with 10 Gb RAM and MS SQL Server licence. Have a 
close look at your problem. How much time does it get MS SQL Server to 
query 200 million rows ? The problem is not in choosing MS SQL or 
PostgreSQL ...

If you are adding 200.000 rows data everyday, consider using a combination 
of CREATE TABLE AS to create a result table with PL/pgSQL triggers to 
maintain data consistency. You will then get instant results, even on 2 
billion rows because you will always query the result table; not the 
original one. Large databases are always optimized this way because, even 
in case of smart indexes, there are things (like your problem) that need 
*smart* optimization.

Do you need PL/pgSQL source code to perform a test on 2 billion rows? If 
so, please email me on pgsql-general and I will send you the code.

Best regards,
Jean-Michel POURE