Re: Optimization (Was: [HACKERS] Serious performance problem)

>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