Re: [GENERAL] 50 MB Table

>I've been shuffling thru the 'LIKE' code in pg, but whilst perusing, it
>occurred to me that I could use a function for this thing. Since i'm
>only looking at the first part of the string I use...
>
>SELECT * FROM info WHERE substring(street_name from 1 to 4) = 'MAIN'
>
>...and the query completes in under 2 secs. Stupidly obvious now but I
>thought I would share this blinding insight with the list ;)

if that case, try:

SELECT * FROM info WHERE street_name ~ '^MAIN';

One trick with string indexes in PG is they must be 'front-anchored',
which I thought "LIKE 'MAIN%'" was. But maybe it's not.

This solution is a little more flexible than substring, and may give
the speed you desire.

(I'm not absolutely sure this query won't have the same performance
problems your first query did, but it typically works for us. If you
do try this, I'd appreciate knowing whether it works for you or not)

--
Karl DeBisschop <kdebisschop(at)alert(dot)infoplease(dot)com>
617.542.6500x2332 (Fax: 617.956.2696)

Information Please / Family Education Network
http://www.infoplease.com - your source for FREE online reference