Joao Ferreira gmail wrote:
I need to remove from 100.000 to 1.000.000 records from my table once a day, and I'dd like to make that removal as fast as possible. This is the idea: DELETE FROM tt WHERE time < $1; Would it be considerably faster if I declare that query inside a user function, let's say function_delete(integer), and invoque it instead SELECT function_delete($max_time); Would this second approach be faster ? I imagine there could be some internal mechanism that would allow pg to have that query pre-optimized somehow ?
No, the internal mechanisms will be pretty much the same (assuming you write the function LANGUAGE SQL). The bottleneck in this command will most likely be the disk, so the conceivable "pre-optimization" mechanisms such as prepared statements or plan caching wouldn't really matter here in practice. So just use whatever you like best.
[[[ I've been looking at Partitioning, but it seems to be a quite intrusive setup if you already have your system up and running]]]
Partitioning is probably something you should look into if you can't get the statement to run quickly enough otherwise. Or combine partitioning and TRUNCATE to get superfast deletes.
