Skip site navigation (1) Skip section navigation (2)

Re: Query advice request

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Mark Kelly <pgsql(at)wastedtimes(dot)net>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Query advice request
Date: 2010-10-09 18:23:51
Message-ID: 4CB0B337.7030208@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Mark, you should be using text indexes. You are looking for the whole 
words and the regular expression will not use an index. If you want 
speed, at least one of the two columns in your query should be indexed 
with a text index.

Mark Kelly wrote:
> Hi.
>
> I'm wondering if there is a better way to do this:
>
> SELECT news.id FROM news 
> WHERE news.headline ~* '(japan|office)' 
> OR news.body ~* '(japan|office)' 
> ORDER BY news.posted DESC;
>
> It is for small articles; a few hundred or so rows in the table and not 
> expecting millions.
>
> Cheers,
>
> Mark
>
>   


-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 


In response to

Responses

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-10-09 23:07:40
Subject: Re: Query advice request
Previous:From: Mark KellyDate: 2010-10-09 16:09:56
Subject: Query advice request

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group