Re: trgm regex index peculiarity

"Erik Rijkers" <er(at)xs4all(dot)nl> writes:
> On Fri, March 28, 2014 09:31, Heikki Linnakangas wrote:
>> So thanks to the fast scan patch, I don't think this patch is worth
>> pursuing anymore. Unless there are some other test case where this patch
>> helps, but the fast scan patch doesn't.

> FWIW, for me the difference (from HEAD) remains quite a bit larger:
> ...
> That's a lot better than the original 140ms vs 5ms but your laptop's 2.5 ms vs. 1 ms is perhaps not representative.

I also feel that Alexander's patch is still worth pursuing. What I see
(in an assert-disabled build of HEAD) is that Erik's "slow" query takes
about 2 ms to plan and 5.5 ms to execute, while the "fast" query is about
0.7 ms to plan and 1.3 ms to execute. With the patch, the "slow" query is
still about 2 ms to plan but only 0.3 ms to execute, while the "fast"
query doesn't change much. There's a lot of noise in these numbers
(successive executions seem to be bouncing around more than usual today),
but still it looks like the runtime gain is impressive percentagewise.

One thing that's interesting is that the planning time is so much more for
the "slow" query, even though the "fast" one has an additional WHERE
clause that the planner has to think about. I haven't tried to do perf
measurements to be sure, but my guess is that this has nothing to do with
GIN or pg_trgm directly, but represents the planner's efforts to get a
selectivity estimate for the ~ operator --- that code works much
differently for patterns that define fixed prefixes vs those that don't.

Anyway, the important point here is that I think the planning time
is helping to mask the fact that there's a pretty useful runtime
improvement.

regards, tom lane