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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

I'd recommend our contrib/pg_trgm module, which provides
trigram based fuzzy search and return results ordered by similarity
to your query.  Read
for more details.

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)

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

In response to


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-2017 The PostgreSQL Global Development Group