Re: [PERFORM] sequential scan performance

[Michael Engelhart]

Thanks everyone for all the  suggestions.  I'll check into those  
contrib modules.


Michael
On May 29, 2005, at 2:44 PM, Oleg Bartunov wrote:


Michael,

I'd recommend our contrib/pg_trgm module, which provides
trigram based fuzzy search and return results ordered by similarity
to your query.  Read http://www.sai.msu.su/~megera/postgres/gist/ 
pg_trgm/README.pg_trgm

for more details.

Oleg
On Sun, 29 May 2005, Michael Engelhart wrote:



Hi -

I have a table of about 3 million rows of city aliases that I  
need to query using LIKE - for example:


select * from city_alias where city_name like '%FRANCISCO'


When I do an EXPLAIN ANALYZE on the above query, the result is:

Seq Scan on city_alias  (cost=0.00..59282.31 rows=2 width=42)  
(actual time=73.369..3330.281 rows=407 loops=1)

  Filter: ((name)::text ~~ '%FRANCISCO'::text)
Total runtime: 3330.524 ms
(3 rows)


this is a query that our system needs to do a LOT.   Is there any  
way to improve the performance on this either with changes to our  
query or by configuring the database deployment?   We have an  
index on city_name but when using the % operator on the front of  
the query string postgresql can't use the index .


Thanks for any help.

Mike

---(end of  
broadcast)---

TIP 2: you can get off all lists at once with the unregister command
  (send unregister YourEmailAddressHere to  
[EMAIL PROTECTED])





Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---(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




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[PERFORM] sequential scan performance

[Michael Engelhart]

Hi -

I have a table of about 3 million rows of city aliases that I need  
to query using LIKE - for example:


select * from city_alias where city_name like '%FRANCISCO'


When I do an EXPLAIN ANALYZE on the above query, the result is:

 Seq Scan on city_alias  (cost=0.00..59282.31 rows=2 width=42)  
(actual time=73.369..3330.281 rows=407 loops=1)

   Filter: ((name)::text ~~ '%FRANCISCO'::text)
Total runtime: 3330.524 ms
(3 rows)


this is a query that our system needs to do a LOT.   Is there any way  
to improve the performance on this either with changes to our query  
or by configuring the database deployment?   We have an index on  
city_name but when using the % operator on the front of the query  
string postgresql can't use the index .


Thanks for any help.

Mike

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])