I'm doing massive tests on a Postgres database...
so basically I have 2 table where I inserted 40.000.000 records on, let's say table1 and 80.000.000 on table2
after this I deleted all those records.
Now if I do SELECT * FROM table1 it takes 199000ms ?
I can't understand what's happening?
can anyone help me on this?
If you delete all the rows from a table, they are marked as deleted but not actually removed from disk immediately. In order to remove them you need to do a "vacuum" operation- this should kick in automatically some time after such a big delete. Even so, that will just leave the pages empty but taking up quite a bit of disk space without a "vacuum full".
If you regularly need to do delete all the rows from a large table, consider using "truncate" instead, which simply zaps the table data file.
The tuples are logically deleted, not fisically. You should perform a VACUUM on the db. More info here
If you are deleting all records, use truncate not delete. Further the first time you run it the relation will not be cached (file cache or shared buffers), so it will be slower than subsequent times.
