On 17 August 2010 08:26, <fiala_marek@centrum.cz> 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.863rows=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
