"Erik Rijkers" <e...@xs4all.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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers