Re: How to optimize query that concatenates strings?

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