An issue that comes up regularly on IRC is that text search queries,
especially on relatively modest size tables or for relatively
non-selective words, often misplan as a seqscan based on the fact that
to_tsvector has procost=1.
Clearly this cost number is ludicrous.
Getting the right cost estimate would obviously mean taking the cost of
detoasting into account, but even without doing that, there's a strong
argument that it should be increased to at least the order of 100.
(With the default cpu_operator_cost that would make each to_tsvector
call cost 0.25.)
(The guy I was just helping on IRC was seeing a slowdown of 100x from a
seqscan in a query that selected about 50 rows from about 500.)
--
Andrew (irc:RhodiumToad)