Re: text_position worst case runtime

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> And how much code would those take? The bottom line here is that we
>> don't have a pile of complaints about the performance of text_position,
>> so it's difficult to justify making it much more complicated than it
>> is now.

> It seems somewhat contrary to the Postgres design philosophy to assume that
> all strings are small.

That is a straw-man argument. If we try to optimize every single
function in the system to the Nth degree, we'll end up with a system
that is unmaintainable (and likely unusably buggy as well). We've got
to set limits on the amount of complexity we're willing to accept in
the core code.

Note that I have not said "you can't put Boyer-Moore into core".
What I've said is that the case to justify doing that hasn't been made.
And handwaving about "design philosophy" isn't the kind of case I'm
looking for --- common applications in which it makes a real performance
difference are what I'm looking for.

At this point we haven't even been shown any evidence that text_position
itself is what to optimize if you need to do searches in large text
strings. It seems entirely likely to me that the TOAST mechanisms would
be the bottleneck, instead. And one should also consider other approaches
entirely, like indexes (tsearch2 anyone?).

regards, tom lane