2007-11-21 #1

WHOO

flowdb=# create index localip_timeslog on localip(timeslot);
create CREATE INDEX
flowdb=# create index localip_ip on localip(ip);
CREATE INDEX

This speeds the postgres2rrd up by several orders of magnitude! Timeslot processing goes from 15-30 seconds per, to 3-7 timeslots per second!

Notes:

• the index will get updated as more data is inserted. Future inserts may be slower than they otherwise might have been (but now that the bulk of the data has been inserted, this isn't a huge penalty
• after a lot of modifications, it will be worth occasionally worth doing vacuum analyze; to compact the database and update the planner's stats that tell it how to construct queries
• indexes are part of the table structure and have to be recreated if the table is dropped and then recreated.