On Wednesday 06 August 2008 07:59, Rafael Domiciano wrote:
> I have read the article... tnks, very helpful.
> But, can I create a index using function like "substring"? I would like to
> create something like this:
Actually, Richard Broersma is the one who commented on that approach. I have
never done this but I have read about it. I'm sure it can be done.
> CREATE INDEX indtest_01 ON table_01
> ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2))
> 2008/8/4 Terry Lee Tucker <terry(at)chosen-ones(dot)org>
> > On Monday 04 August 2008 11:09, Frank Bax wrote:
> > > Terry Lee Tucker wrote:
> > > > On Monday 04 August 2008 10:05, Richard Broersma wrote:
> > > >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <mgould(at)allcoast(dot)net>
> > wrote:
> > > >>> In some db's if you
> > > >>> use a lower() or upr() it will always do a table scan instead of
> > using
> > > >>> a index
> > > >>
> > > >> True, this would also happen in PostgreSQL. However, you can
> > > >> overcome this by creating a "functional" index:
> > http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html
> > > >> This way all expression using where lower( column ) = 'a'. will
> > > >> always use an index scan.
> > > >
> > > > What about using the operator, ~* ?
> > > >
> > > > Does that cause a table scan as well?
> > >
> > > Whether or not any query uses an index scan or seq scan depends on many
> > > factors and is not always easily predictable.
> > >
> > > Richard's statement about "will always use an index scan" is not
> > > universally true. If the table is very small; a index scan is NOT
> > > used. Table statistics could also indicate a seq scan is more efficient
> > > (suppose 99% of rows had column='a').
> > >
> > > The ~* operator is very likely to scan the entire table because it will
> > > look for 'A' anywhere in the column (and will therefore match 'Joanne';
> > > and I doubt that there is special code to handle case where length of
> > > argument is exactly the same as column. However; ~* '^a' which anchors
> > > search to first character is perhaps more likely to use an index scan.
> > >
> > > Frank
> > Frank,
> > Thanks for the response. Actually, from within the applicaion, we use ~*
> > and
> > it is anchored with whatever they've typed in the widget as search
> > criteria.
> > Anyway, thanks for the helpful response...
> > --
> > Terry Lee Tucker
> > Turbo's IT Manager
> > Turbo, division of Ozburn-Hessey Logistics
> > 2251 Jesse Jewell Pkwy NE
> > Gainesville, GA 30501
> > Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
> > terry(at)turbocorp(dot)com
> > www.turbocorp.com
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
In response to
pgsql-sql by date
|Next:||From: Mark Roberts||Date: 2008-08-06 15:54:40|
|Subject: Re: more than 1000 connections|
|Previous:||From: Rafael Domiciano||Date: 2008-08-06 11:59:37|
|Subject: Re: Case Insensitive searches|