X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0802DD1B456 for ; Thu, 19 Feb 2004 20:58:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 23464-03 for ; Thu, 19 Feb 2004 16:58:12 -0400 (AST) Received: from cmailg3.svr.pol.co.uk (cmailg3.svr.pol.co.uk [195.92.195.173]) by svr1.postgresql.org (Postfix) with ESMTP id CC22AD1D16D for ; Thu, 19 Feb 2004 16:58:09 -0400 (AST) Received: from modem-3186.llama.dialup.pol.co.uk ([217.135.188.114] helo=LaptopDellXP) by cmailg3.svr.pol.co.uk with esmtp (Exim 4.14) id 1AtvFJ-0000BI-RK; Thu, 19 Feb 2004 20:58:10 +0000 Reply-To: From: "Simon Riggs" To: "'Jeff Boes'" , "'pgsql-performance'" Subject: Re: Optimizer difference using function index between 7.3 and 7.4 Date: Thu, 19 Feb 2004 20:58:03 -0000 Organization: 2nd Quadrant Message-ID: <001901c3f72b$119b2320$0200000a@LaptopDellXP> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 In-Reply-To: <403383A6.7090101@nexcerpt.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/229 X-Sequence-Number: 5753 >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