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: (view raw, whole thread or download thread mbox)
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:
> 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 

In response to


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-2017 The PostgreSQL Global Development Group