Re: [HACKERS] Serious performance problem

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: pgadmin-hackers(at)postgresql(dot)org
Cc: "Tille, Andreas" <TilleA(at)rki(dot)de>
Subject: Re: [HACKERS] Serious performance problem
Date: 2001-10-30 12:06:44
Message-ID: 4.2.0.58.20011030125000.00a53100@pop.freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers pgsql-hackers


>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

Responses

Browse pgadmin-hackers by date

  From Date Subject
Next Message Tille, Andreas 2001-10-30 14:09:32 Re: Serious performance problem
Previous Message Dave Page 2001-10-30 11:17:04 Re: DROP/CREATE

Browse pgsql-hackers by date

  From Date Subject
Next Message Tille, Andreas 2001-10-30 12:17:18 Re: [HACKERS] Serious performance problem
Previous Message Brent Verner 2001-10-30 11:48:40 Re: Serious performance problem