Robert Haas [robertmhaas@xxxxxxxxx] wrote:
Thanks for your response.
Does the table have triggers on it? Does it have indexes? What is the
result of pg_relation_size() on that table?
No triggers; 3 indexes
cemdb=> select pg_relation_size('ts_defects');
pg_relation_size
------------------
9464971264
(1 row)
cemdb=>
cemdb=> select pg_relation_size('ts_defects_DateIndex');
pg_relation_size
------------------
1299931136
(1 row)
cemdb=> select pg_relation_size('ts_defects_DefectIndex');
pg_relation_size
------------------
1217224704
(1 row)
cemdb=> select pg_relation_size('ts_defects_EventIndex');
pg_relation_size
------------------
1216528384
How much memory do you have in your machine? What is work_mem set to?
32G; work_mem=64M
Did you try VACUUM FULL instead of just plain VACUUM to recover
performance? You might also need to REINDEX.
Or you could TRUNCATE the table.
I didn't try FULL or REINDEX. In this case, TRUNCATE is the best option
as I was just trying to reset the state of the table for another test.
But this brings up another question: will autovacuum do the right thing
to preserve performance on this table when many rows are deleted?
Thanks,
Brian
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
