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
