Re: [PATCH] PostgreSQL DLR speedup

looking at your patch  I'm puzzled why this select oid stuff is in  
there at all.

The original MySQL version is same as your patch. Straightforward  
delete with a where statement.
No two step select/delete. Doesn't make any sense to me to search,  
return the oid and then delete based on that oid which basically  
makes another search.

So I totally agree on your patch.


On 09.11.2006, at 17:20, Ben Suffolk wrote:

> Hi,
>
> I have been doing some benchmarking on the pgsql DLRs. I actually  
> wrote a quick application as opposed to using kannel. The  
> application simulated message sending and DLRs by  looping a  
> thousand times over the following :-
>
>  1. Adding a random number of messages into the DLR table (and  
> storing them in an in memory hash)
>
>  2. Deleting a random number of messages from the DLR table (based  
> on a random selection from the hash)
>
>  3. Updating a smaller random number of messages in the DLR table  
> (again select from the hash).
>
> These test were carried out on a pretty old box running FreeBSd 6.1  
> and PostgreSQL 8.1.
>
> I ran the benchmark a number of times, and with the existing SQL I  
> was anywhere between 100 and 140 messages a second.
>
> I updated the SQL (the delete and update) to remove the OID  
> selection bit. I did not add any LIMIT so that it will still work  
> with the earlier versions of pgsql. Again after running this a  
> number of times I would average between 199 and 201 messages a  
> second (It was a lot more consistent this time).
>
> Attached is the patch.
>
> Regards
>
> Ben
>
> <dlr_pgsql.patch>