Re: Deleting millions of rows

Robert Haas [robertmhaas@gmail.com] 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