Re: How to optimize query that concatenates strings?

[Chander Ganesan <chander@xxxxxxxxxx>]

You could build a function-based index that contains the "simplified" version of each URL (in your case, the field with the '/' stripped).  Then use the same function on the URL going in.  In that case PostgreSQL will use the index that you created already.

Take a look at the PostgreSQL documentation for function-based indexes.

select from ... where simplify(url) <> url_col;

In the example above 'url_col' would have a function-based index that was based on 'simplify(url_col)'

Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999

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 = ''
or url || '/' = 'umist.ac.uk') as present;
                                          QUERY PLAN

-----------------------------------------------------------------------------------------------
 Result  (cost=47664.01..47664.02 rows=1 width=0)
   InitPlan
     ->  Seq Scan on url  (cost=0.00..47664.01 rows=6532 width=38)
           Filter: ((url = ''::text) OR ((url || '/'::text)
= 'umist.ac.uk'::text))
(4 rows)

url=# explain select exists(select * from url where url =
'') as present;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Result  (cost=5.97..5.98 rows=1 width=0)
   InitPlan
     ->  Index Scan using url_idx on url  (cost=0.00..5.97 rows=1
width=38)
           Index Cond: (url = ''::text)
(4 rows)


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

Thanks in advance,

BBB


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster