pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: 'Jeff Boes' <jboes@nexcerpt.com>
To: 'pgsql-performance' <pgsql-performance@postgresql.org>
Subject: Re: Optimizer difference using function index between 7.3 and 7.4
Date: Thu, 19 Feb 2004 20:58:03 -0000
Message-ID: <001901c3f72b$119b2320$0200000a@LaptopDellXP> (raw)
In-Reply-To: <403383A6.7090101@nexcerpt.com>

>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




view thread (3+ messages)

Message-ID: <001901c3f72b$119b2320$0200000a@LaptopDellXP>
Permalink:  ../001901c3f72b$119b2320$0200000a@LaptopDellXP/
Also on:    postgresql.org/message-id/001901c3f72b$119b2320$0200000a@LaptopDellXP

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: simon@2ndquadrant.com, jboes@nexcerpt.com
  Subject: Re: Optimizer difference using function index between 7.3 and 7.4
  In-Reply-To: <001901c3f72b$119b2320$0200000a@LaptopDellXP>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox