Skip site navigation (1) Skip section navigation (2)

Re: sequential scan performance

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Michael Engelhart <mengelhart(at)mac(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: sequential scan performance
Date: 2005-05-29 19:44:32
Message-ID: Pine.GSO.4.62.0505292342170.1721@ra.sai.msu.su (view raw or flat)
Thread:
Lists: pgsql-performance
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 majordomo(at)postgresql(dot)org)
>

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

In response to

Responses

pgsql-performance by date

Next:From: Eric LauzonDate: 2005-05-29 20:17:11
Subject: OID vs overall system performances on high load
Previous:From: Jim C. NasbyDate: 2005-05-29 16:33:12
Subject: Re: Select performance vs. mssql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group