Re: Optimizer difference using function index between 7.3 and 7.4

>Jeff Boes writes
> # explain select link_id from links l join clm_tmp_links t on
> (fn_urlrev(l.path_base) = t.rev_path_base);

> executes in 59.8 seconds!

> Now the odd part: if I change the query to this:
>
> # explain analyze select link_id from links l join clm_tmp_links t on
> (fn_urlrev(l.path_base) = fn_urlrev(t.rev_path_base));

> Total runtime: 18.125 ms
>
> (i.e., apply the function to the data in the temp table), it runs a
> whole lot faster! Is this a bug in the optimizer? Or did something
> change about the way functional indexes are used?

Erm..I may have misunderstood your example, but surely the second
formulation of your query returns the wrong answer? It looks to me as if
you are comparing a reversed URL with a twice-reversed URL; if that's
true that would explain why it runs faster: They don't ever match. Is
that right?

Thanks for the idea of reversing the URLs, nice touch. I'd been thinking
about reverse key indexes as a way of relieving the hotspot down the
rightmost edge of an index during heavy insert traffic. I hadn't thought
this would also speed up the access also.

Best Regards, Simon Riggs