Re: external query VS user function

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.