
>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

Tue, 30 Oct 2001 13:06:44 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [HACKERS] Serious performance problem
On Tue, 30 Oct 2001, Jean-Michel POURE wrote:

> Is your database read-only?
Daily update from MS-SQL server.  Between updates it is 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 ...
The problem is for sure.  If one server is 10 to 30 times faster for the very
same tasks and chances are high that it skales better for the next orders of
magnitude where our data will fit in for the next years because of real
index usage (see postings on the hackers list) than the decission is easy.
My colleague made sure that MS SQL server is fit for the next years and
I can only convince him if an other Server has a comparable speed *for the
same task*.

> If you are adding 200.000 rows data everyday, consider using a combination
I do not add this much.

> 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.
I really believe that there are many problems in the world that fall under
this category and you are completely right.  My coleague is a database
expert (I consider me as a beginner) and he made sure that performance is
no issue for the next couple of years.  So what?  Spending hours in
optimisation into things who work perfectly?  Why not asking the
PostgreSQL authors to optimize tha server this way the very same task
performs comparable??????  If we afterwards need further database
optimization because of further constrains, Iï¿½m the first who will start
this.   But there must be server code in the world that is able to answer
the example query that fast.  This is proven!

Kind regards

         Andreas.


Tue, 30 Oct 2001 15:09:32 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: Serious performance problem