badlydrawnbhoy wrote:
Hi all,

I've got a database of URLs, and when inserting new data into it I want
to make sure that there are no functionally equivalent URLs already
present. For example, 'umist.ac.uk' is functionally the same as
'umist.ac.uk/'.

I find that searching for the latter form, using string concatentation
to append the trailing slash, is much slower than searching for a
simple string - the index on URL name isn't used to speed up the
search.

Here's an illustration

url=# explain select exists(select * from url where url = 'umist.ac.uk'
or url || '/' = 'umist.ac.uk') as present;

Well, in that example, you should just remove the OR conditional - it just evaluates to false anyways.


Is there any way I can force postgres to use the index when using the
string concatenation in the query?

If you are always going to strcat with a '/', you could probably create a functional index or add a new column for a normalized url (which is what I'd lean towards).



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to