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).
