Re: [PERFORM] sequential scan performance

2005-05-30 Thread 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.

Re: [PERFORM] sequential scan performance

2005-05-30 Thread Steve Atkins
On Sun, May 29, 2005 at 08:27:26AM -0500, 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,

[PERFORM] sequential scan performance

2005-05-29 Thread 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

Re: [PERFORM] sequential scan performance

2005-05-29 Thread Steinar H. Gunderson
On Sun, May 29, 2005 at 08:27:26AM -0500, Michael Engelhart wrote: 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

Re: [PERFORM] sequential scan performance

2005-05-29 Thread Christopher Kings-Lynne
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