Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgadmin-hackerspgsql-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

pgsql-hackers by date

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

pgadmin-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group