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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
