Results below:
> ... but given that, I wonder whether the cost isn't from fetching
> the toasted messageblk data, and nothing directly to do with either
> the encode() call or the ~~ test. It would be interesting to compare
> the results of
>
> explain analyze select encode(messageblk, 'escape') ~~ '%Yossi%'
> from dbmail_messageblks where is_header = 0;
>
"Seq Scan on dbmail_messageblks (cost=0.00..38449.06 rows=162096
width=756) (actual time=0.071..492776.008 rows=166748 loops=1)"
" Filter: (is_header = 0)"
"Total runtime: 492988.410 ms"
> explain analyze select encode(messageblk, 'escape')
> from dbmail_messageblks where is_header = 0;
>
"Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096
width=756) (actual time=16.008..306408.633 rows=166750 loops=1)"
" Filter: (is_header = 0)"
"Total runtime: 306585.369 ms"
> explain analyze select messageblk = 'X'
> from dbmail_messageblks where is_header = 0;
>
"Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096
width=756) (actual time=18.169..251212.223 rows=166754 loops=1)"
" Filter: (is_header = 0)"
"Total runtime: 251384.900 ms"
> explain analyze select length(messageblk)
> from dbmail_messageblks where is_header = 0;
>
"Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096
width=756) (actual time=20.436..2585.098 rows=166757 loops=1)"
" Filter: (is_header = 0)"
"Total runtime: 2673.840 ms"
> (length is chosen with malice aforethought: unlike the other cases,
> it doesn't require detoasting a toasted input)
>
> regards, tom lane