From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | fiala_marek(at)centrum(dot)cz |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Search query is curious |
Date: | 2010-08-17 10:44:39 |
Message-ID: | AANLkTikj+FujewaS42n_EZu1=3oP=nga-K39nuqD_scF@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-08-17 10:49:05 | Re: Search query is curious |
Previous Message | Andres Freund | 2010-08-17 09:37:38 | Re: Advice configuring ServeRAID 8k for performance |