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

Re: sequential scan performance

From: Michael Engelhart <mengelhart(at)mac(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: sequential scan performance
Date: 2005-05-30 16:33:28
Message-ID: A55980F8-B846-48D6-8C58-84E5C23011A2@mac.com (view raw or flat)
Thread:
Lists: pgsql-performance
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  
>> 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
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>      message can get through to the mailing list cleanly
>


In response to

pgsql-performance by date

Next:From: Sebastian BöckDate: 2005-05-30 16:36:39
Subject: Re: Index not used on join with inherited tables
Previous:From: Josh BerkusDate: 2005-05-30 16:21:46
Subject: Re: Index not used on join with inherited tables

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