<html style="direction: ltr;">
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<style>body
p { margin-bottom: 10pt; margin-top: 0pt; } </style>
<style>body p { margin-bottom: 10pt; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="UTF-8" bgcolor="#FFFFFF"
text="#000000">
<p>Hi, everyone. Daniel Verite <a class="moz-txt-link-rfc2396E"
href="mailto:daniel(at)manitou-mail(dot)org"><daniel(at)manitou-mail(dot)org></a>
wrote:<br>
</p>
<p> </p>
<blockquote type="cite">
<pre wrap="">How much bytea are you dumping for it to take only 0.066s?
The fact that it takes about the same time than dumping the "empty content"
looks very suspicious.
On my desktop machine, if I create a table with 1000 blobs containing strings
of 5 million 'x', which is what I understood you basically did (perhaps I
misunderstood?), then it takes about 200s to dump it with pg_dump -Fc</pre>
</blockquote>
OK, this is an egg-on-my-face moment with my benchmarks: I added the
pg_dump timing after the "delete" timing, and so I was actually
dumping the database when it was empty! Not very effective, to say
the least.<br>
<br>
I've updated my benchmark, and updated the results, as well:<br>
<br>
<tt>| | Delete | Dump | Database
size | Dump size |<br>
|---------------------------+-----------+-----------+---------------+-----------|<br>
| Empty content | 0m0.151s | 0m38.875s | 88
kB | 11K |<br>
| bytea | 0m0.505s | 1m59.565s | 57
MB | 4.7M |<br>
| large object with rule | 0m31.438s | 2m42.079s | 88
kB | 4.7M |<br>
| large object with trigger | 0m28.612s | 2m17.156s | 88
kB | 4.7M |<br>
<br>
<br>
<br>
** 10,000 records<br>
<br>
| | Delete | Dump | Database
size | Dump size |<br>
|---------------------------+-----------+------------+---------------+-----------|<br>
| Empty content | 0m7.436s | 0m0.089s | 680
kB | 66K |<br>
| bytea | 1m5.771s | 20m40.842s | 573
MB | 47M |<br>
| large object with rule | 5m26.254s | 21m7.695s | 680
kB | 47M |<br>
| large object with trigger | 5m13.718s | 20m56.195s | 680
kB | 47M |<br>
</tt>
<p><br>
</p>
<p>It would thus appear that there's a slight edge for dumping
bytea, but nothing super-amazing. Deleting, however, is still
much faster with bytea than large objects.<br>
</p>
<p><br>
I've put my benchmark code up on GitHub for people to run and play
with, to see if they can reproduce my results:</p>
<p><a class="moz-txt-link-freetext" href="https://github.com/reuven/pg-delete-benchmarks">https://github.com/reuven/pg-delete-benchmarks</a><br>
</p>
<p><br>
</p>
<p>Reuven<br>
</p>
<pre class="moz-signature" cols="72">--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner
</pre>
</body>
</html>