On 17 August 2010 08:26, <fiala_marek@xxxxxxxxxx> wrote: > > Hi, > > I've database of lyrics and I'm using this query for suggest box. > SELECT views, title, id FROM songs WHERE title ILIKE 'bey%' ORDER BY views DESC LIMIT 15; > In query plan is this line: -> Seq Scan on songs (cost=0.00..11473.56 rows=5055 width=23) (actual time=1.088..89.863 rows=77 loops=1) > it takes about 90ms > > but when i modify query (remove sort) > SELECT views, title, id FROM songs WHERE title ILIKE 'bey%' LIMIT 15; > In query plan -> Seq Scan on songs (cost=0.00..11473.56 rows=5055 width=23) (actual time=1.020..20.601 rows=15 loops=1 > seq scan takes only 20ms now, why? Sorts have a cost, so will take longer. > Or any suggestion to optimize this query? > In table songs are about 150.000 rows. It might be an idea to add an index to your views column to prevent the need for a sequential scan to sort. Also, ILIKE won't be able to use an index, so if you wish to match against title, you may wish to change your query to use: WHERE lower(title) LIKE .... And then create an index on lower(title). Regards -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
