Re: Performance Issues

Le Mardi 23 Avril 2002 21:16, Shaun Grannis a écrit :
> Thanks for you suggestions.

Dear Shaun,

The only solution I see is to create a pseudo-count function.
For this, you need to :

- add fire_trigger (timestamp) field on data_table with index.

- create a datefreq_table table which will be used
  as a pseudo-counter with two fields :
  datefreq_date (int4) and datefreq_count (int8).
  Both fields should be indexed.

- create a trigger on after update or insert of data_table
  to increase datefreq_count by 1.

- create a trigger on after delete of data_table
  to decrease date_freq_count by 1.

- create the needed records in datefreq_table (100 as you said).

To inizialize the system, enter:
UPDATE * FROM data_table
SET fire_trigger ='now'
WHERE fire_trigger IS NULL;
LIMIT 1000

This will inizilize the system for 10000 records. I don't know how much time
it will take. Probably 10 seconds on your hardware. Mesure the time needed to
inizialize the whole system.

Once inizialized, the system will give immediate answers using the
pseudo-counter table.

SELECT datefreq_count
FROM datefreq_table
WHERE datefreq_date = foo;

The only drawback of such a system is that you will not be able to DROP or
INSERT or even UPDATE data as fast as if there was no trigger. it will only
be a problem if your data changes very often.

Even on Oracle or DB2, you would have to use data-mining features to be able
to manage such amounts of data. Here, on PostgreSQL, you can do it manually
using PLpgSQL.

Cheers,
Jean-Michel