From: | Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr> |
---|---|
To: | "Tille, Andreas" <TilleA(at)rki(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Optimization (Was: [HACKERS] Serious performance problem) |
Date: | 2001-11-06 13:34:35 |
Message-ID: | 4.2.0.58.20011106141904.00a56b60@pop.freesurf.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>please refresh my memory. I´m really sure you posted an URL (in private or
>via list - but currently the archive is not searchable) where I can found
>some information about the database optimization. But I must have lost
>exactly this mail. Could you please help me out because I´m now convinced
>to do it this way.
No, I never published an URL but intend to.
Could you send me your database structure again, please?
Optimization could be something like:
1) Create the statistics table. Keep the date when the table of the last
update. Use a serial to count the number of rows inserted. If updates > n
and/or hours>x , refresh statistics table. Maybe it should simply be
refreshed any 30 minutes.
2) Store all calculated or linked values. Typically,
SELECT table1.blibli, table2.blabla
FROM table1
LEFT JOIN table2 on table1.x=table2.y;
Should be replaced by:
- add field blabla in Table1,
- Table1.blabla field is updated by a trigger on any insert/modification in
Table1,
- Table1.blabla field is updated by a trigger on any
insert/modification/drop in Table2.
You end with a 'flat' and intelligent database that is slower on INSERT but
much faster on SELECT.
>PS: Something about PgAdmin II running under wine I asked in my previous
>mail?
Well, I never had it run under Wine. Sorry, I wron't be of any help.
Best regards,
Jean-Michel POURE
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 2001-11-06 14:36:03 | Re: data integrity |
Previous Message | postgresql | 2001-11-06 13:19:19 | Re: Probably simple answer |