Re: [SQL] getting ILIKE or ~* to use indexes....
Rajesh,
> I want my query to use indexes for company name searches but its not
> happening unless
> is use '=' which does not server the purpose.
>
> eg
>
> tradein_clients=# explain SELECT co_name FROM unified_data where
> co_name ilike '%rajesh%' ;
Well, for an *anchored* case-insensitive search, you can create an
index on lower(field_name) to use an index.
CREATE INDEX idx_uni_co_name ON unifed_data(lower(co_name));
SELECT co_name FROM unified_data where
lower(co_name) LIKE (lower('rajesh') || '%') ;
And that will use the index.
However, what you are doing is an *unanchored* text search, meaning
that you are searching for 'rajesh' anywhere in the field. No standard
index can help you with that.
Instead, you should look into Full Text Search tools. There's a simple
one in /contrib in the Postgresql source, and an more robust one
available from the OpenFTS project.
-Josh Berkus
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] update on a large table
I need to do an UPDATE on a large (100 million record) table. Is there any way to speed up the process (Like turning off the transaction log)? So far postgres has been handling the large database exceptionally well (large \copy imports and WHERE clauses w/ multiple params) but it is killing me on UPDATES. It takes about 4 hours to run an UPDATE (the WHERE clause is against an INDEX) but about 50 sec for a similar SELECT. Thank You, -Aaron Held ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] getting ILIKE or ~* to use indexes....
Thanks Josh
I normally use tsearch for full text search i will probably use that
or may be this what u suggested.
regds
mallah.
> Rajesh,
>
>> I want my query to use indexes for company name searches but its not happening
>unless
>> is use '=' which does not server the purpose.
>>
>> eg
>>
>> tradein_clients=# explain SELECT co_name FROM unified_data where co_name ilike
>'%rajesh%' ;
>
> Well, for an *anchored* case-insensitive search, you can create an index on
>lower(field_name)
> to use an index.
>
> CREATE INDEX idx_uni_co_name ON unifed_data(lower(co_name));
> SELECT co_name FROM unified_data where
> lower(co_name) LIKE (lower('rajesh') || '%') ;
>
> And that will use the index.
>
> However, what you are doing is an *unanchored* text search, meaning that you are
>searching for
> 'rajesh' anywhere in the field. No standard index can help you with that.
>
> Instead, you should look into Full Text Search tools. There's a simple one in
>/contrib in the
> Postgresql source, and an more robust one
> available from the OpenFTS project.
>
> -Josh Berkus
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] slowing down too fast - why ?
Hi, I must be doing something silly. I have a 900MHz, 384MB RAM, and this thing is slow. (Postgresql-7.1.2). And growing exponencially slower. SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl) when I do : createdb filerian psql -d filerian -f /tmp/schema.sql echo 'create table times (the_moment datetime, the_number int4);' | psql -d filerian cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql -d filerian ; done 2>&1 >/dev/null & for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done Why are the times so bad ? Why is it slowing so fast ? Am I missing any useful indeces ? This shows the slowage: select the_number,min(the_moment) from times group by the_number; PS: if you look in the perl code for "exec", immediatelly above will you find the query it is doing. Thanx, John -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] slowing down too fast - why ?
and I forgot to mention that my stats are available at: http://John.Vicherek.com/slow/times.query.txt John On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > Hi, > > I must be doing something silly. I have a 900MHz, 384MB RAM, and > this thing is slow. (Postgresql-7.1.2). > > And growing exponencially slower. > > SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) > am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) > perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl) > > when I do : > > createdb filerian > psql -d filerian -f /tmp/schema.sql > echo 'create table times (the_moment datetime, the_number int4);' | psql -d filerian > cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here > while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql >-d filerian ; done 2>&1 >/dev/null & > for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done > > > Why are the times so bad ? Why is it slowing so fast ? > > Am I missing any useful indeces ? > > This shows the slowage: > select the_number,min(the_moment) from times group by the_number; > > PS: if you look in the perl code for "exec", immediatelly above will you > find the query it is doing. > >Thanx, > > John > > > > > -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
