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

Re: sequential scan performance

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Michael Engelhart <mengelhart(at)mac(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: sequential scan performance
Date: 2005-05-29 14:43:08
Message-ID: 4299D4FC.5050505@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
> 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 .

Of course not.  There really is now way to make your literal query above 
fast.  You could try making a functional index on the reverse() of the 
string and querying for the reverse() of 'francisco'.

Or, if you want a general full text index, you should absolutely be 
using contrib/tsearch2.

Chris

In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2005-05-29 16:33:12
Subject: Re: Select performance vs. mssql
Previous:From: Steinar H. GundersonDate: 2005-05-29 13:47:13
Subject: Re: sequential scan performance

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