Re: Search query is curious

On 17 August 2010 08:26, <fiala_marek(at)centrum(dot)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.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