Nick Burrett <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
We don't normally hear of people needing that --- is there anything unusual about the schema of this database?
Not particularly. The database consists of around 3000 tables created using this:
CREATE TABLE vs_foo (date date NOT NULL, time time NOT NULL, bytesin int8 CHECK (bytesin >= 0), bytesout int8 CHECK (bytesout >= 0));
Each table has around 1500 rows.
3000 tables? That's why you need so many locks.
I'm surprised that I've never hit this problem before though.
Have you thought about collapsing these into *one* table with an extra key column? Also, it'd likely be better to combine the date and time into a timestamp column.
I tried it back in the days when we only had around 1000 tables. Problem was that inserts and deletes took a *very* long time. IIRC a one row insert was taking over 10 seconds. I think this was because the index files were growing to several gigabytes.
Having everything in one large table would have been great and would have made life much easier.
date and time were split to simplify queries. I think it had an impact on index sizes.
Regards,
Nick.
-- Nick Burrett Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
