Обсуждение: Search query is curious
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=77loops=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? Or any suggestion to optimize this query? In table songs are about 150.000 rows. Thank you for your reply. Best regards. Marek Fiala
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
Hello 2010/8/17 <fiala_marek@centrum.cz>: > > 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? > > Or any suggestion to optimize this query? without ORDER BY database returns first 15 rows where predicate is true. With ORDER BY the database has to find all rows where predicate is true and then has to sort it. So first case can be a much faster because there are not necessary full table scan. regards Pavel Stehule > In table songs are about 150.000 rows. > > Thank you for your reply. > > Best regards. > Marek Fiala > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
> without ORDER BY database returns first 15 rows where predicate is > true. With ORDER BY the database has to find all rows where predicate > is true and then has to sort it. So first case can be a much faster > because there are not necessary full table scan. Right. Essentialy, the ORDER BY happens before the LIMIT (so you have to sort everything before you take the first 15). If it were the other way around, you would take the first 15 rows Postgres happens to find (in an arbitrary order) and then sort these 15, which is probably not that useful. Consider Thom's suggestion. --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com