Re: bytea encode performance issues

Tom Lane wrote:
> Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
> to work from, any suggestions would be mere guesswork.

This was taken immediately after a vacuum analyze on the database.

"HashAggregate (cost=41596.68..41596.84 rows=16 width=764) (actual
time=488263.802..488263.837 rows=40 loops=1)"
" -> Nested Loop (cost=0.00..41596.60 rows=16 width=764) (actual
time=23375.445..488260.311 rows=40 loops=1)"
" -> Nested Loop (cost=0.00..41463.32 rows=16 width=780)
(actual time=23375.344..488231.994 rows=40 loops=1)"
" -> Seq Scan on dbmail_messageblks k
(cost=0.00..39193.21 rows=259 width=764) (actual time=30.662..486585.126
rows=2107 loops=1)"
" Filter: ((is_header = 0::smallint) AND
(encode(messageblk, 'escape'::text) ~~ '%Yossi%'::text))"
" -> Index Scan using dbmail_messages_2 on dbmail_messages
m (cost=0.00..8.75 rows=1 width=16) (actual time=0.777..0.777 rows=0
loops=2107)"
" Index Cond: (m.physmessage_id = k.physmessage_id)"
" Filter: ((mailbox_idnr = 8) AND (status = ANY
('{0,1}'::integer[])))"
" -> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.32 rows=1 width=8) (actual
time=0.701..0.703 rows=1 loops=40)"
" Index Cond: (k.physmessage_id = p.id)"
"Total runtime: 488264.192 ms"

> Also, what can you tell us about the sizes of the messageblk
> strings (max and avg would be interesting)?
>
select max(length(messageblk)),avg(length(messageblk)) from
dbmail_messageblks
MAX AVG
532259; 48115.630147120314