Re: [GENERAL] How to optimize query that concatenates strings?

[Chander Ganesan]

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