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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedFeb 18, '04 at 3:25p
activeFeb 19, '04 at 8:58p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase